Dimensional Modeling - Part 3: Dimensions Hierarchy

Hierarchical structures in dimensional modeling can be classified into three main types: balanced, unbalanced, and ragged hierarchies. Each type presents unique characteristics and implementation considerations that directly impact query performance and system design.

Balanced Hierarchies

Balanced hierarchies represent the most common hierarchical structure, characterized by a consistent number of levels across all branches. Every path from the root to leaf nodes traverses the same number of hierarchical tiers.

Example: Calendar hierarchy (Year → Quarter → Month → Date) or standardized geographic hierarchy (Country → State → City)

The levels in balanced hierarchies are typically derived from columns within a single denormalized dimension table or from tables forming a snowflake dimension structure. When higher-level columns exist within a denormalized dimension, they contain redundant data that facilitates rapid query execution.

• Fixed number of levels across all branches
• Facts typically relate to the lowest hierarchical level
• Supports aggregation from detailed to summary levels
• Can be represented as distinct positional attributes within dimension tables

Unbalanced Hierarchies

Unbalanced hierarchies feature varying depths across different branches, with levels determined by parent-child relationships rather than predefined column structures. The hierarchical depth depends on dimension row relationships rather than static table schema design.

Example: Employee reporting structure where organizational depth varies by department

In unbalanced hierarchies, dimension tables utilize surrogate keys alongside foreign key references to the same table, creating self-referential relationships. Facts apply to the dimension grain, requiring careful consideration of individual records and their hierarchical descendants during aggregation.

• Variable number of levels determined by data relationships
• Parent-child relationships define structure
• Each member can potentially have subordinates
• Requires recursive queries or specialized handling

Ragged Hierarchies

Ragged hierarchies occur when members exist at levels that are not directly adjacent to their logical parents, creating gaps in the hierarchical structure. Missing intermediate levels are typically handled by repeating parent values or using null placeholders.

Example: Geographic hierarchy where some countries lack state/province subdivisions (e.g., New Zealand: Country → City, bypassing state level)

Ragged hierarchies present significant challenges for traditional relational database queries, often requiring specialized modeling techniques such as bridge tables or pathstring attributes to maintain query efficiency and structural integrity.

• Inconsistent intermediate levels
• Parent-child relationships may skip hierarchical tiers
• Requires special handling for missing levels
• Complex aggregation and navigation requirements

Fixed Depth Positional Hierarchies

When hierarchical depth remains constant and level names are standardized, these structures should be represented as distinct positional attributes within dimension tables. This approach provides optimal query performance and intuitive navigation patterns.

Slightly Ragged Hierarchies

For hierarchies with limited depth variation, slightly ragged structures can be adapted into fixed depth positional frameworks by creating dimension attributes for maximum level counts and populating them according to established business logic.

Variable Depth Hierarchies

Irregular hierarchies with undefined depth present significant modeling challenges requiring specialized approaches:

Bridge Table Approach

Bridge tables contain rows for every possible hierarchical path, enabling standard SQL traversal without specialized language extensions.

Pathstring Attribute Approach

Pathstring attributes store complete hierarchical paths as encoded text strings, enabling efficient navigation without bridge table complexity.

References

• Microsoft. (n.d.). Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables. Microsoft Learn. https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables
• Lecture notes from Business Analytics course, Prof. Paolo Menna, University of Verona 2024-2025