Chapter 05 · Dimension Design: Descriptive Context, Hierarchies, Attributes, and Analytical Usability
Design a Customer/Product/Geography Dimension Set for Usability, History, and Governance
Assemble the governed AtlasMart customer, product, and geography dimension set, preserve prior fact and metric controls, and validate keys, labels, drill paths, ownership, and future history policies end to end.
Learning outcomes
A dimension design is not complete when the DDL compiles. AtlasMart needs a repeatable contract for keys, labels, hierarchies, unknown members, ownership, privacy classification, and future history policy—and it needs tests proving that dimension evolution did not corrupt the established facts and metrics.
Assemble the final Chapter 05 customer, product, and geography dimensions around the unchanged paid-sales fact.
Publish key, hierarchy, ownership, privacy, and planned history policies for important attributes.
Validate readable BI outputs and unknown/not-applicable member behavior.
Run structural and metric reconciliation tests across dimensions and facts.
Define what Chapter 05 intentionally defers to the conformance and SCD chapters.
The Chapter 03 sales fact still contains exactly 7 paid order lines, 4 paid orders, 9 units, and 625 paid GMV; Chapter 04 added 380 cost-at-sale and therefore 245 gross profit / 39.2% recomputed gross margin. Chapter 05 keeps every fact row and fact foreign key stable. It enriches descriptive context around the existing customer/product keys and introduces a governed geography dimension; no prior control total is silently restated.
The mandatory lab uses Python's standard-library
sqlite3 module with synthetic AtlasMart rows and
PRAGMA foreign_keys = ON. It is a
semantic/modeling lab, not a warehouse-performance benchmark.
Record your actual Python/SQLite versions; query plans,
optimizer behavior, physical storage, collation, and
constraint enforcement differ across engines.
1. Final Chapter 05 design contract
| Dimension | Primary key | Business/durable identity | Key descriptive responsibilities |
|---|---|---|---|
| Customer | customer_key |
source key + durable_customer_id |
customer name, segment, geography relationship |
| Product | product_key |
product_id |
name, brand, department/category/subcategory |
| Geography | geography_key |
geography_id |
country, region, optional city, ragged-status semantics |
| Date | date_key |
stable calendar date | calendar labels; richer fiscal/holiday attributes arrive in Chapter 08 |
| Store | store_key |
store_id |
selling-location label/type; conformance across processes comes in Chapter 06 |
The fact keeps customer/product/store/date surrogate keys and Chapter 04 measurements. Chapter 05 does not implement Slowly Changing Dimension history; it records the policy decisions that Chapter 07 will later execute.
2. Attribute governance is part of dimension design
{ "contract_version": "ch05-v1", "dimensions": { "customer": { "owner": "Customer Analytics", "unknown_key": 0, "not_applicable_key": -1, "attributes": { "durable_customer_id": { "class": "identifier", "history_policy": "type_0/durable", "privacy": "internal" }, "customer_name": { "class": "descriptor", "history_policy": "type_1_correction_candidate", "privacy": "restricted_in_real_data" }, "segment": { "class": "descriptor", "history_policy": "type_2_candidate", "privacy": "internal" }, "geography_key": { "class": "relationship", "history_policy": "type_2_candidate", "privacy": "internal" } } }, "product": { "owner": "Merchandising Analytics", "unknown_key": 0, "not_applicable_key": -1, "hierarchy": "product > subcategory > category > department", "attributes": { "product_name": { "history_policy": "type_1_or_2_by_requirement" }, "category_name": { "history_policy": "type_2_candidate" } } }, "geography": { "owner": "Data Governance", "unknown_key": 0, "not_applicable_key": -1, "hierarchy": "city? > region > country", "ragged_rule": "known region may have no city; do not label it unknown" } }}3. Human-readable acceptance queries
The simplest usability test is whether a consumer can answer common questions with fact-to-dimension joins and recognizable labels while preserving controls.
-- Revenue and profit by customer segmentSELECT c.segment, SUM(f.extended_amount) AS gmv, SUM(f.extended_amount-f.extended_cost) AS gross_profitFROM fact_sales fJOIN dim_customer c ON c.customer_key=f.customer_keyGROUP BY c.segmentORDER BY c.segment;-- Revenue by product hierarchySELECT p.department_name,p.category_name,p.subcategory_name, SUM(f.extended_amount) AS gmvFROM fact_sales fJOIN dim_product 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,p.subcategory_name;-- Revenue by customer geographySELECT g.country_name,g.region_name, COALESCE(g.city_name,'[no city level]') AS city_label, SUM(f.extended_amount) AS gmvFROM fact_sales fJOIN dim_customer c ON c.customer_key=f.customer_keyJOIN dim_geography g ON g.geography_key=c.geography_keyGROUP BY g.country_name,g.region_name,g.city_nameORDER BY g.country_name,g.region_name,city_label;4. Controlled failure audit
| Failure | How it appears | Required repair / control |
|---|---|---|
| Mutable source key used as fact key | source renumber breaks historical joins | warehouse surrogate + source/durable mapping |
| NULL dimension foreign key | inner joins silently drop facts; unlabeled left-join bucket | 0 unknown or -1 not applicable per documented policy |
| Description stored in fact | label corrections require fact rewrites; repeated text drifts | dimension attribute with governed change policy |
| Ragged city forced to Unknown | known East geography becomes semantically indistinguishable from unresolved geography | preserve known region/country and explicit no-city-level state |
| Reflexive snowflake | simple BI requires unnecessary join chains | flatten stable many-to-one descriptive levels unless a measured/governance reason justifies normalization |
5. End-to-end Chapter 05 acceptance lab
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');")# 1) Prior fact/metric controls are unchanged.controls=conn.execute("""SELECT COUNT(*),COUNT(DISTINCT order_id),SUM(quantity),SUM(extended_amount),SUM(extended_cost),SUM(extended_amount-extended_cost)FROM fact_sales""").fetchone()assert controls==(7,4,9,625,380,245),controls# 2) Fact foreign keys are non-null and valid.assert conn.execute("SELECT COUNT(*) FROM fact_sales WHERE customer_key IS NULL OR product_key IS NULL OR store_key IS NULL OR date_key IS NULL").fetchone()[0]==0assert conn.execute("PRAGMA foreign_key_check").fetchall()==[]# 3) Default members exist where Chapter 05 requires them.for table in ('dim_customer','dim_product','dim_geography'): keycol=table.replace('dim_','')+'_key' keys={r[0] for r in conn.execute(f'SELECT {keycol} FROM {table} WHERE {keycol} IN (0,-1)')} assert keys=={0,-1},(table,keys)# 4) Durable customer identity is unique.assert conn.execute("SELECT COUNT(*) FROM dim_customer WHERE customer_key>0").fetchone()[0] == conn.execute("SELECT COUNT(DISTINCT durable_customer_id) FROM dim_customer WHERE customer_key>0").fetchone()[0]# 5) Hierarchy and geography rollups reconcile to 625.category=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,SUM(f.extended_amount)FROM fact_sales f JOIN dim_customer c ON c.customer_key=f.customer_keyJOIN dim_geography g ON g.geography_key=c.geography_keyGROUP BY g.region_name"""))assert category=={'Accessories':425,'Displays':200},categoryassert region=={'North':275,'South':150,'West':200},regionassert sum(category.values())==sum(region.values())==625# 6) Ragged-known geography is distinct from unknown geography.east=conn.execute("SELECT geography_status,city_name FROM dim_geography WHERE geography_key=503").fetchone()unknown=conn.execute("SELECT geography_status,city_name FROM dim_geography WHERE geography_key=0").fetchone()assert east==('known_ragged',None) and unknown==('unknown',None)print('controls =',controls)print('category GMV =',category)print('sales-bearing geography GMV =',region)print('Chapter 05 acceptance: PASS')
Expected final line: Chapter 05 acceptance: PASS.
Because the database is in memory, there is no persistent
cleanup. If you adapt this to a file-backed database, delete
only the disposable Chapter 05 lab file after verification.
6. What this chapter intentionally does not freeze
Chapter 05 defines dimension semantics, not a universal physical layout or a complete history implementation. Chapter 06 will decide how customer/product/date dimensions become conformed across multiple business processes. Chapter 07 will implement Type 0–7 history policies and effective dating. Chapter 08 adds date/role-playing/junk/degenerate/mini/inferred-member patterns. Physical partitioning, clustering, and columnar decisions arrive much later.
That sequencing is deliberate: first make the descriptive contract correct and understandable, then reuse it across processes, then add history and specialized patterns.
7. Production judgment and handoff
A production-ready dimension has explicit identity, meaningful labels, documented default members, named hierarchy levels, ownership, change policy, and tests that protect fact reconciliation. It does not simply mirror normalized source tables, and it does not hide missingness or history requirements behind blank strings and NULL joins.
With AtlasMart’s customer/product/geography context now governed, Chapter 06 can ask a harder enterprise question: are the same customer, product, and date definitions truly conformed when Sales, Inventory, Fulfillment, Finance, and Marketing use them at different grains?
Knowledge check
Check your understanding
- Which Chapter 05 control proves prior metrics were preserved?
- Why record history policy now if Type 2 is not implemented until Chapter 07?
- What is the difference between an unknown geography and a known ragged geography?
- Why must conformance wait for Chapter 06?
Review the answers
1. The unchanged fact population reconciles to 7 lines, 4 orders, 9 units, 625 GMV, 380 cost, and 245 gross profit.
2. Because model semantics and ownership should be agreed before the loader encodes change behavior; otherwise ETL mechanics silently decide business history.
3. Unknown means the relationship cannot be resolved; known ragged means country/region are known but a lower level such as city legitimately does not exist or is not collected.
4. A dimension is not conformed merely because its local table is well designed. Conformance requires shared meaning and compatible members/keys across different business processes.
Summary and next step
Chapter 05 turns dimensions into governed analytical contracts: descriptive context, stable identity, explicit default members, safe hierarchies, and deliberate flattening choices. The next chapter extends those contracts across business processes with conformed dimensions and a bus architecture.
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.