Hierarchies are modeled based on whether their depth is fixed or variable. Fixed-depth hierarchies should be completely flattened into separate columns within the dimension table, while variable-depth or ragged hierarchies require bridge tables, closure tables, recursive common table expressions, or materialized path strings.
Fixed hierarchies in dimension tables
Retail product hierarchies typically have a predictable, fixed structure such as Department, Category, Subcategory, and SKU. Flatten these levels directly as columns inside dim_product:
dim_product product_key | sku_code | subcategory_name | category_name | department_name 101 | A44 | Running Shoes | Footwear | Apparel 102 | B12 | Tennis Racquets | Equipment | Sporting Goods
A review of flattening benefits:
- Flattening requires zero joins for drill-downs.
- Columnar storage engines compress repeated text columns efficiently.
- Standard BI tools natively recognize the hierarchy for drill-up and drill-down navigation.
Ragged organizational trees
Employee-to-manager structures or variable bills of materials have unpredictable depth, where one branch has three levels and another has eight. Storing ragged hierarchies directly in fixed columns breaks easily. Instead, represent the relationship using one of three proven patterns:
- Bridge or closure table: Pre-calculates all ancestor-descendant pairs with hop distance, allowing fast joins to find all direct and indirect reports.
- Recursive CTEs: Dynamically traverse parent-child pointers (
manager_employee_id) at query time, suitable for moderate data volumes. - Materialized path strings: Store hierarchical lineage like
/emp_1/emp_12/emp_45/on each record for simple prefix matching withLIKE.
BI drill-down needs
Choose the pattern that matches your analytics consumption. If business users need self-service drill paths in standard dashboards, flatten predictable levels or expose a view joined to a closure table so end users avoid writing recursive SQL.