Hierarchy
A hierarchy is an ordered, multi-level classification of dimension values that enables drill-down navigation and meaningful aggregation across levels, such as day-month-quarter-year or product-category-brand.
A hierarchy groups dimension values into levels where each level is a logical aggregation of the level below. The most common example is time: day rolls up to week, which rolls up to month, which rolls up to quarter, which rolls up to year. Product hierarchies might be SKU to product variant to product line to category. Geographic hierarchies are city to region to country to continent. Hierarchies enable intuitive navigation: users start at a summary (monthly revenue) and drill down to detail (daily revenue for a specific month).
Hierarchies matter for analytics because they determine how dimensions naturally aggregate and what comparisons make sense. Comparing monthly and quarterly revenue requires understanding the time hierarchy. Hierarchies also drive user experience: BI tools can provide drill-down buttons automatically when hierarchies are defined. Without explicit hierarchies, aggregation is ambiguous: is "product" a leaf level or does it roll up to category?
Hierarchies are defined in semantic models or metadata systems and can be ragged (not all branches have the same depth) or unbalanced (some paths longer than others). Multiple hierarchies can exist for the same dimension: calendar hierarchy (day-week-month-quarter-year) vs. fiscal hierarchy (day-fiscal week-fiscal month-fiscal quarter-fiscal year). The semantic layer manages these hierarchies and enforces valid drill-downs.
Key Characteristics
- ▶Multi-level classification with parent-child relationships
- ▶Enables logical drill-down and roll-up navigation
- ▶Can be ragged or balanced depending on domain
- ▶May have multiple hierarchies per dimension
- ▶Supports meaningful aggregation across levels
- ▶Defined explicitly in semantic models
Why It Matters
- ▶Navigation: Enable intuitive drill-down in dashboards
- ▶Analysis: Support valid comparisons across hierarchy levels
- ▶Performance: Optimize aggregations using hierarchy knowledge
- ▶Accuracy: Prevent comparing incompatible hierarchy levels
- ▶Usability: Automatic drill-paths improve analyst productivity
Example
A time hierarchy defines: day < week < month < fiscal quarter < fiscal year. A product hierarchy defines: SKU < product variant < product line < category < segment. Revenue queries can drill from annual total to monthly details to specific day; users can't accidentally mix calendar weeks and fiscal quarters.
Coginiti Perspective
SMDL models hierarchies through dimension definitions within entities. Date hierarchies emerge from date and datetime typed dimensions, while organizational hierarchies are captured through relationships between entities at different levels of granularity. Semantic SQL navigates these hierarchies by resolving relationship chains, so a query can drill from a category entity down to a product entity through defined one_to_many relationships. CoginitiScript pipelines can materialize pre-aggregated tables at each hierarchy level for performance-sensitive reporting.
Related Concepts
More in Semantic Layer & Metrics
Business Logic Layer
A business logic layer is the component of a semantic layer or data system that encodes business rules, calculations, and transformations, making them reusable and enforced across analytics.
Data Abstraction Layer
A data abstraction layer is a software or architectural component that sits between raw data sources and analytics consumers, providing unified access and hiding implementation complexity.
Data Semantics
Data semantics refers to the documented meaning, business context, and valid usage of data elements, including definitions, relationships, constraints, and governance rules.
Derived Metrics
Derived metrics are metrics calculated from other base metrics or dimensions rather than directly from raw fact tables, enabling metric composition and reducing calculation redundancy.
Dimension
A dimension is a categorical or descriptive attribute used to slice, filter, and organize metrics, such as product, region, customer segment, or date.
Governed Metrics
Governed metrics are business metrics with centrally defined calculations, owners, approval workflows, and enforced standards that ensure consistency and trustworthiness across all analytics consumers.
See Semantic Intelligence in Action
Coginiti operationalizes business meaning across your entire data estate.