Dimension Hierarchies

Dimension tables frequently store hierarchical relationships (such as Geography, Product Categories or Management Chains) to allow BI tools to drill down or roll up data.

Fixed-Depth Hierarchies

A fixed-depth hierarchy has a predictable, standard number of levels. These are easily flattened into standard, named attributes directly on the dimension row.

Example (Product Hierarchy):

product_key	product_name	sub_category	category	department
101	Laptop Pro	Laptops	Electronics	Technology
102	Desk Chair	Chairs	Office Furniture	Furniture

BI tools can easily group by department, drill down to category, and then to sub_category.

Slightly Ragged Hierarchies

A slightly ragged hierarchy has a fixed, small number of levels, but some members don't have a value at every level. Geography is the classic case: most cities belong to a state or province, but city-states like Singapore and Monaco don't.

Because the maximum depth is known, each level can still be stored as its own column. The problem is the gaps. If they're left NULL, BI tools group them under a "(blank)" member, so a user drilling from country to state to city can lose track of those locations or see them lumped together.

The standard fix is to pad the empty levels, usually by copying the nearest child value up into them.

Example (Geography Hierarchy):

location_key	city	state_province	country
1	Seattle	Washington	USA
2	Washington D.C.	Washington D.C.	USA
3	Singapore	Singapore	Singapore
4	Monaco	Monaco	Monaco

Singapore and Monaco have no state or province, so the city name fills state_province. Because Washington D.C. is a federal district and not a state, the city name is padded into the state_province column. Every row now has a value at every level, and drill-down works from country to city without gaps.

Ragged (Variable-Depth) Hierarchies

A ragged hierarchy has no fixed depth. Org charts and bills of materials are typical examples: one branch might be 2 levels deep while another is 15. Unlike slightly ragged hierarchies, you can't give each level its own column, because you don't know how many columns you'd need.

Source systems usually store these as parent-child relationships, where each row points to its parent.

Example (Employee Hierarchy):

employee_key	employee_name	title	manager_key
1	Alice	CEO	NULL
2	Bob	VP Engineering	1
3	Carol	VP Sales	1
4	Dave	Engineering Manager	2
5	Erin	Engineer	4

The Sales branch ends at Carol (2 levels), while the Engineering branch runs down to Erin (4 levels).

Answering a question like "total expenses for everyone under Bob" from this table requires recursive SQL to walk the tree. Most BI tools can't generate recursive queries, and the tree gets re-walked on every query. Dimensional models avoid this by flattening the tree ahead of time, using one of two techniques.

1. Hierarchy Bridge Table

A bridge table stores one row for every ancestor-descendant pair, along with the number of levels between them. Each employee is also paired with themselves at depth 0.

ancestor_key	descendant_key	depth	is_leaf
2 (Bob)	2 (Bob)	0	False
2 (Bob)	4 (Dave)	1	False
2 (Bob)	5 (Erin)	2	True

Only Bob's rows are shown. The full bridge for this org chart has 12 rows.

To total expenses for Bob's organization, join the fact table to the bridge on descendant_key and filter to Bob as the ancestor:

SELECT SUM(f.expense_amount)
FROM fact_expenses f
JOIN bridge_org b ON f.employee_key = b.descendant_key
WHERE b.ancestor_key = 2;

The depth-0 row includes Bob's own expenses; add AND b.depth > 0 to exclude them. Always filter to a single ancestor or group by ancestor_key. Otherwise, each fact row is repeated once per ancestor and totals are overstated.

2. Pathstring Attribute

A pathstring stores each member's full lineage as text directly on the dimension row.

employee_key	employee_name	org_path
1	Alice	/1/
2	Bob	/1/2/
3	Carol	/1/3/
4	Dave	/1/2/4/
5	Erin	/1/2/4/5/

Filtering for org_path LIKE '/1/2/%' returns Bob, Dave, and Erin. The trailing slash matters: '/1/2%' would also match an employee whose path is /1/23/.

Pathstrings need no extra table, but extracting a specific level requires string parsing, and rolling up to every level at once is awkward. The bridge table is usually the more flexible choice for reporting. With either technique, a reorg means regenerating rows or paths for everyone below the person who moved.