Hierarchies, Levels, Ragged/Unbalanced Hierarchies, and Drill Paths

Learning outcomes

Analysts rarely stop at a leaf label. They move from product to subcategory to category to department, or from customer city to region to country. Those drill paths are only safe when each level has explicit semantics and ragged branches are handled as real data conditions rather than patched with misleading labels.

1. A hierarchy is a semantic many-to-one path

A fixed positional hierarchy gives each level an agreed meaning and requires each lower member to roll to exactly one parent at the next level for the relevant historical context. AtlasMart’s product hierarchy is product → subcategory → category → department. Keyboard and Mouse roll to Input Devices, then Accessories, then Hardware. The geography hierarchy is city → region → country, but one known East-region customer has no city-level detail, so that branch is slightly ragged.

2. Fixed product hierarchy: drill without changing fact grain

The product attributes are repeated on one wide dimension row because they are descriptive context attached to product key 201–204. A drill-down adds a lower-level attribute to the grouping; it does not create a new fact grain or new measure.

SELECT  p.department_name,  p.category_name,  COALESCE(p.subcategory_name,'[no subcategory]') AS subcategory_name,  SUM(f.extended_amount) AS gmvFROM fact_sales AS fJOIN dim_product AS p ON p.product_key=f.product_keyGROUP BY p.department_name,p.category_name,p.subcategory_nameORDER BY p.department_name,p.category_name,subcategory_name;

Expected leaf rollups are Input Devices 225, Docking 200, and Monitors 200. Category totals are Accessories 425 and Displays 200; department Hardware totals 625. All levels reconcile because each fact line points to one product and each product has one parent at every modeled product level.

3. Ragged geography: absence of a level is not unknown identity

Cyra Labs belongs to a known geography row: country Freedonia, region East, but no city is supplied. That is different from geography key 0, where the entire relationship is unresolved. A report may display [no city level] for the ragged leaf while still grouping the row correctly under East and Freedonia.

SELECT  g.country_name,  g.region_name,  CASE    WHEN g.geography_status='unknown' THEN '[unknown geography]'    WHEN g.city_name IS NULL THEN '[no city level]'    ELSE g.city_name  END AS city_label,  COALESCE(SUM(f.extended_amount),0) AS gmvFROM dim_customer AS cJOIN dim_geography AS g ON g.geography_key=c.geography_keyLEFT JOIN fact_sales AS f ON f.customer_key=c.customer_keyWHERE c.customer_key > 0GROUP BY g.country_name,g.region_name,g.geography_status,g.city_nameORDER BY g.region_name,city_label;

The current sales fixture contributes North 275, West 200, South 150, and East 0. The East row remains visible as known descriptive context even though it has no paid-sale fact in the Chapter 03 population.

4. Deliberately wrong approach: force every branch to the same depth

Replacing every missing city with the literal value “Unknown” collapses two distinct meanings: a known East region whose city level is not collected and a completely unresolved geography. The reverse mistake is inventing a fake city such as “East City” just to satisfy a fixed-level UI. Both damage semantics.

The repair is to preserve the reason for missingness. A slightly ragged hierarchy can use a fixed set of positional attributes with documented placeholder rules, while fully unpredictable parent-child hierarchies need a different modeling technique. Do not use a positional hierarchy when a child can legitimately have multiple parents at the same time; that becomes a many-to-many relationship, which Chapter 09 handles with bridges.

5. Reconcile every level to the same atomic facts

import sqlite3conn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS dim_store;\nDROP TABLE IF EXISTS dim_product;\nDROP TABLE IF EXISTS dim_customer;\nDROP TABLE IF EXISTS dim_geography;\nDROP TABLE IF EXISTS dim_date;\n\nCREATE TABLE dim_date (\n  date_key INTEGER PRIMARY KEY,\n  full_date TEXT NOT NULL UNIQUE,\n  calendar_year INTEGER NOT NULL,\n  calendar_month INTEGER NOT NULL,\n  day_of_month INTEGER NOT NULL,\n  day_name TEXT NOT NULL\n);\n\nCREATE TABLE dim_geography (\n  geography_key INTEGER PRIMARY KEY,\n  geography_id TEXT NOT NULL UNIQUE,\n  country_name TEXT NOT NULL,\n  region_name TEXT NOT NULL,\n  city_name TEXT,\n  geography_status TEXT NOT NULL\n);\n\nCREATE TABLE dim_customer (\n  customer_key INTEGER PRIMARY KEY,\n  durable_customer_id TEXT NOT NULL UNIQUE,\n  source_system TEXT NOT NULL,\n  source_customer_id TEXT NOT NULL,\n  customer_name TEXT NOT NULL,\n  segment TEXT NOT NULL,\n  geography_key INTEGER NOT NULL REFERENCES dim_geography(geography_key),\n  UNIQUE (source_system, source_customer_id)\n);\n\nCREATE TABLE dim_product (\n  product_key INTEGER PRIMARY KEY,\n  product_id TEXT NOT NULL UNIQUE,\n  product_name TEXT NOT NULL,\n  brand_name TEXT NOT NULL,\n  department_name TEXT NOT NULL,\n  category_name TEXT NOT NULL,\n  subcategory_name TEXT\n);\n\nCREATE TABLE dim_store (\n  store_key INTEGER PRIMARY KEY,\n  store_id TEXT NOT NULL UNIQUE,\n  store_name TEXT NOT NULL,\n  store_type TEXT NOT NULL\n);\n\nCREATE TABLE fact_sales (\n  sales_key INTEGER PRIMARY KEY,\n  date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n  store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n  order_id TEXT NOT NULL,\n  line_no INTEGER NOT NULL,\n  quantity INTEGER NOT NULL CHECK (quantity > 0),\n  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n  extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0),\n  extended_cost NUMERIC NOT NULL CHECK (extended_cost >= 0),\n  source_batch_id TEXT NOT NULL,\n  UNIQUE(order_id, line_no)\n);\n\nINSERT INTO dim_date VALUES\n(20260918,'2026-09-18',2026,9,18,'Friday'),\n(20260919,'2026-09-19',2026,9,19,'Saturday'),\n(20260920,'2026-09-20',2026,9,20,'Sunday');\n\nINSERT INTO dim_geography VALUES\n(0,'__UNKNOWN__','Unknown','Unknown',NULL,'unknown'),\n(-1,'__NOT_APPLICABLE__','Not applicable','Not applicable',NULL,'not_applicable'),\n(501,'G-NORTH','Freedonia','North','Northport','known'),\n(502,'G-WEST','Freedonia','West','Westhaven','known'),\n(503,'G-EAST','Freedonia','East',NULL,'known_ragged'),\n(504,'G-SOUTH','Freedonia','South','Southbank','known');\n\nINSERT INTO dim_customer VALUES\n(0,'D-UNKNOWN','system','__UNKNOWN__','Unknown customer','Unknown',0),\n(-1,'D-NA','system','__NOT_APPLICABLE__','Not applicable customer','Not applicable',-1),\n(101,'D-CUST-001','erp','C001','Ada Retail','SMB',501),\n(102,'D-CUST-002','erp','C002','Ben Home','Consumer',502),\n(103,'D-CUST-003','crm','C003','Cyra Labs','Enterprise',503),\n(104,'D-CUST-004','erp','C004','Dara Studio','Consumer',504);\n\nINSERT INTO dim_product VALUES\n(0,'__UNKNOWN__','Unknown product','Unknown','Unknown','Unknown',NULL),\n(-1,'__NOT_APPLICABLE__','Not applicable product','Not applicable','Not applicable','Not applicable',NULL),\n(201,'P100','Keyboard','KeyWorks','Hardware','Accessories','Input Devices'),\n(202,'P200','Mouse','KeyWorks','Hardware','Accessories','Input Devices'),\n(203,'P300','Monitor','ViewCo','Hardware','Displays','Monitors'),\n(204,'P400','Dock','ConnectCo','Hardware','Accessories','Docking');\n\nINSERT INTO dim_store VALUES\n(0,'__UNKNOWN__','Unknown selling location','Unknown'),\n(301,'S-WEB','Web Store','Digital'),\n(302,'S-MOBILE','Mobile Store','Digital'),\n(303,'S-SALES','Assisted Sales','Assisted');\n\nINSERT INTO fact_sales VALUES\n(1,20260918,101,201,301,'O1001',1,2,50,100,60,'BATCH-CH03-001'),\n(2,20260918,101,202,301,'O1001',2,1,25,25,10,'BATCH-CH03-001'),\n(3,20260918,102,203,302,'O1002',1,1,200,200,140,'BATCH-CH03-001'),\n(4,20260919,101,204,301,'O1003',1,1,100,100,60,'BATCH-CH03-001'),\n(5,20260919,101,202,301,'O1003',2,2,25,50,20,'BATCH-CH03-001'),\n(6,20260920,104,201,302,'O1005',1,1,50,50,30,'BATCH-CH03-001'),\n(7,20260920,104,204,302,'O1005',2,1,100,100,60,'BATCH-CH03-001');")product=dict(conn.execute("""SELECT p.category_name,SUM(f.extended_amount)FROM fact_sales f JOIN dim_product p ON p.product_key=f.product_keyGROUP BY p.category_name"""))region=dict(conn.execute("""SELECT g.region_name,COALESCE(SUM(f.extended_amount),0)FROM dim_customer c JOIN dim_geography g ON g.geography_key=c.geography_keyLEFT JOIN fact_sales f ON f.customer_key=c.customer_keyWHERE c.customer_key>0 GROUP BY g.region_name"""))department=conn.execute("""SELECT SUM(f.extended_amount)FROM fact_sales f JOIN dim_product p ON p.product_key=f.product_keyWHERE p.department_name='Hardware'""").fetchone()[0]assert product=={'Accessories':425,'Displays':200},productassert region=={'East':0,'North':275,'South':150,'West':200},regionassert department==625assert sum(product.values())==625assert sum(region.values())==625print('product categories =',product)print('geography regions =',region)print('hierarchy reconciliation: PASS')

6. Multiple hierarchies can coexist

A product can have a merchandising hierarchy and a separate brand-oriented grouping without forcing brand into the category path. Similarly, a date dimension can expose calendar and fiscal hierarchies simultaneously. The key requirement is to name each path and its level semantics explicitly so BI users know what they are drilling through.

Do not infer a parent-child relationship merely because two attributes are correlated in today’s sample data. A hierarchy is a governed business relationship whose cardinality and change policy must remain valid beyond the tiny fixture.

7. Production judgment

Favor fixed positional attributes when levels have stable names and true many-to-one relationships. Use documented ragged handling when only some branches skip a level. Escalate to a more flexible parent-child or bridge design when depth is unpredictable or membership is many-to-many. Whatever the representation, test that rollups reconcile to atomic facts and that missing-level semantics remain distinguishable from unknown dimension identity.

The next lesson asks whether these hierarchy attributes should stay flattened in the dimension row, be normalized into snowflake tables, or occasionally live in an outrigger.

Knowledge check

Summary and next step

Hierarchies are governed semantic paths, not UI indentation. Name the levels, verify many-to-one behavior, distinguish ragged-known from unknown, and reconcile every rollup to atomic facts. Next, compare flattened dimensions with snowflakes and outriggers.

Authoritative references

• Kimball Group — Dimensions for Descriptive Context — Role of dimensions as descriptive filtering/grouping context.
• Kimball Group — Dimension Table Structure — Wide, descriptive dimension-table structure and analytical labels.
• Kimball Group — Dimension Surrogate Keys — Warehouse-controlled dimension primary keys and source-key independence.
• Kimball Group — Multiple Hierarchies in Dimensions — Coexisting natural hierarchies and drill paths.
• Kimball Group — Snowflaked Dimensions — Tradeoffs of normalizing dimension hierarchies.
• Kimball Group — Outrigger Dimensions — Secondary dimension references and sparing use.
• SQLite — Foreign Key Support — Local lab referential-integrity behavior.