Chapter 05 · Dimension Design: Descriptive Context, Hierarchies, Attributes, and Analytical Usability

Dimension Attributes as Human-Readable Analytical Context, Not Operational Normalization Exercises

Design AtlasMart dimensions as human-readable analytical context, enrich customer/product/geography attributes without changing fact grain, and verify that BI outputs remain understandable and reconciled.

Intermediate → Advanced105–125 minutesDimension usability labPython stdlib sqlite3 · local/syntheticLast reviewed: September 2026

Learning outcomes

AtlasMart already has a correct paid-sales fact, but facts become usable to analysts only when the surrounding dimensions provide clear, governed words for the people, products, places, and classifications behind each measurement. This lesson redesigns the descriptive layer without changing the fact grain or earlier control totals.

Explain why dimension attributes are the human-readable context for facts rather than a copy of an OLTP entity model.

Keep descriptive text out of the fact table while still producing readable BI results with simple joins.

Enrich the existing customer/product keys with governed geography and product hierarchy attributes without rewriting facts.

Use explicit unknown and not-applicable members instead of nullable fact foreign keys.

Verify that dimension enrichment preserves 625 GMV, 380 cost, and 245 gross profit.

Chapter 05 continuity contract

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.

Execution scope

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. A dimension exists to answer “who, what, where, when, why, and how?”

A fact row records a measurement event at a declared grain. A dimension row supplies descriptive context for that event. AtlasMart’s line O1001/1 says that two keyboards contributed 100 of revenue and 60 of cost; the customer, product, selling location, and calendar dimensions turn surrogate keys such as 101 and 201 into business vocabulary such as “Ada Retail”, “SMB”, “Keyboard”, “Accessories”, and “North”.

That distinction matters because an OLTP schema is optimized for recording operational state and enforcing transactional rules. A presentation-layer dimension is optimized for filtering, grouping, labeling, and stable analytical interpretation. Copying the source normalization pattern into the warehouse can preserve technical purity while making every report require five or six joins just to show a readable label.

Object	Primary responsibility	Typical columns	Growth shape
`fact_sales`	Repeated measurements at paid-order-line grain	keys, quantity, revenue, cost, batch ID	many rows; grows with events
`dim_customer`	Who bought and how the customer is described	surrogate key, durable ID, name, segment, geography key	few rows relative to facts; descriptive width
`dim_product`	What was sold	surrogate key, product ID, name, brand, department/category/subcategory	descriptive and hierarchy-rich
`dim_geography`	Where a customer is classified	country, region, city, status	reference-like but still governed analytical context

2. Preserve fact grain while enriching context

Chapter 05 does not add customer name, region, brand, or category columns to fact_sales. The fact remains one paid order line. Instead, the existing customer surrogate keys 101–104 continue to join to richer customer rows, and those customer rows reference a geography member. Product surrogate keys 201–204 continue to identify product dimension rows whose new hierarchy attributes remain single-valued for the current Chapter 05 snapshot.

chapter05_fixture.sql · descriptive dimension baseline

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_sales;DROP TABLE IF EXISTS dim_store;DROP TABLE IF EXISTS dim_product;DROP TABLE IF EXISTS dim_customer;DROP TABLE IF EXISTS dim_geography;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date (  date_key INTEGER PRIMARY KEY,  full_date TEXT NOT NULL UNIQUE,  calendar_year INTEGER NOT NULL,  calendar_month INTEGER NOT NULL,  day_of_month INTEGER NOT NULL,  day_name TEXT NOT NULL);CREATE TABLE dim_geography (  geography_key INTEGER PRIMARY KEY,  geography_id TEXT NOT NULL UNIQUE,  country_name TEXT NOT NULL,  region_name TEXT NOT NULL,  city_name TEXT,  geography_status TEXT NOT NULL);CREATE TABLE dim_customer (  customer_key INTEGER PRIMARY KEY,  durable_customer_id TEXT NOT NULL UNIQUE,  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  customer_name TEXT NOT NULL,  segment TEXT NOT NULL,  geography_key INTEGER NOT NULL REFERENCES dim_geography(geography_key),  UNIQUE (source_system, source_customer_id));CREATE TABLE dim_product (  product_key INTEGER PRIMARY KEY,  product_id TEXT NOT NULL UNIQUE,  product_name TEXT NOT NULL,  brand_name TEXT NOT NULL,  department_name TEXT NOT NULL,  category_name TEXT NOT NULL,  subcategory_name TEXT);CREATE TABLE dim_store (  store_key INTEGER PRIMARY KEY,  store_id TEXT NOT NULL UNIQUE,  store_name TEXT NOT NULL,  store_type TEXT NOT NULL);CREATE TABLE fact_sales (  sales_key INTEGER PRIMARY KEY,  date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  store_key INTEGER NOT NULL REFERENCES dim_store(store_key),  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  quantity INTEGER NOT NULL CHECK (quantity > 0),  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),  extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0),  extended_cost NUMERIC NOT NULL CHECK (extended_cost >= 0),  source_batch_id TEXT NOT NULL,  UNIQUE(order_id, line_no));INSERT INTO dim_date VALUES(20260918,'2026-09-18',2026,9,18,'Friday'),(20260919,'2026-09-19',2026,9,19,'Saturday'),(20260920,'2026-09-20',2026,9,20,'Sunday');INSERT INTO dim_geography VALUES(0,'__UNKNOWN__','Unknown','Unknown',NULL,'unknown'),(-1,'__NOT_APPLICABLE__','Not applicable','Not applicable',NULL,'not_applicable'),(501,'G-NORTH','Freedonia','North','Northport','known'),(502,'G-WEST','Freedonia','West','Westhaven','known'),(503,'G-EAST','Freedonia','East',NULL,'known_ragged'),(504,'G-SOUTH','Freedonia','South','Southbank','known');INSERT INTO dim_customer VALUES(0,'D-UNKNOWN','system','__UNKNOWN__','Unknown customer','Unknown',0),(-1,'D-NA','system','__NOT_APPLICABLE__','Not applicable customer','Not applicable',-1),(101,'D-CUST-001','erp','C001','Ada Retail','SMB',501),(102,'D-CUST-002','erp','C002','Ben Home','Consumer',502),(103,'D-CUST-003','crm','C003','Cyra Labs','Enterprise',503),(104,'D-CUST-004','erp','C004','Dara Studio','Consumer',504);INSERT INTO dim_product VALUES(0,'__UNKNOWN__','Unknown product','Unknown','Unknown','Unknown',NULL),(-1,'__NOT_APPLICABLE__','Not applicable product','Not applicable','Not applicable','Not applicable',NULL),(201,'P100','Keyboard','KeyWorks','Hardware','Accessories','Input Devices'),(202,'P200','Mouse','KeyWorks','Hardware','Accessories','Input Devices'),(203,'P300','Monitor','ViewCo','Hardware','Displays','Monitors'),(204,'P400','Dock','ConnectCo','Hardware','Accessories','Docking');INSERT INTO dim_store VALUES(0,'__UNKNOWN__','Unknown selling location','Unknown'),(301,'S-WEB','Web Store','Digital'),(302,'S-MOBILE','Mobile Store','Digital'),(303,'S-SALES','Assisted Sales','Assisted');INSERT INTO fact_sales VALUES(1,20260918,101,201,301,'O1001',1,2,50,100,60,'BATCH-CH03-001'),(2,20260918,101,202,301,'O1001',2,1,25,25,10,'BATCH-CH03-001'),(3,20260918,102,203,302,'O1002',1,1,200,200,140,'BATCH-CH03-001'),(4,20260919,101,204,301,'O1003',1,1,100,100,60,'BATCH-CH03-001'),(5,20260919,101,202,301,'O1003',2,2,25,50,20,'BATCH-CH03-001'),(6,20260920,104,201,302,'O1005',1,1,50,50,30,'BATCH-CH03-001'),(7,20260920,104,204,302,'O1005',2,1,100,100,60,'BATCH-CH03-001');

3. Make the human-readable output observable

A BI-facing query should not expose a user to anonymous warehouse integers. The integers make joins durable; dimension attributes make the result understandable.

Readable paid-sales query

SELECT  d.full_date,  c.customer_name,  c.segment,  COALESCE(g.city_name, '[no city level]') AS city_name,  g.region_name,  p.product_name,  p.category_name,  s.store_name,  f.quantity,  f.extended_amount AS revenue,  f.extended_amount - f.extended_cost AS gross_profitFROM fact_sales AS fJOIN dim_date AS d ON d.date_key=f.date_keyJOIN dim_customer AS c ON c.customer_key=f.customer_keyJOIN dim_geography AS g ON g.geography_key=c.geography_keyJOIN dim_product AS p ON p.product_key=f.product_keyJOIN dim_store AS s ON s.store_key=f.store_keyORDER BY f.sales_key;

The query should return seven rows. The fact rows and measurements are unchanged; only the labels and analytical attributes are richer. A grouped category query must still reconcile to Accessories 425 + Displays 200 = 625.

4. Deliberately wrong approach: put descriptions directly in the fact

A tempting shortcut is to add customer_name, segment, region_name, product_name, brand_name, and category_name to every sales row. That may look “flat and fast,” but it turns descriptive corrections into fact rewrites, creates inconsistent spellings across historical rows, duplicates governance rules, and makes a source-key rename or classification correction a measurement-table maintenance event.

The repair is not “normalize everything.” The repair is to keep measurement columns and dimension foreign keys in the fact, while keeping the human-readable context in dimensions whose attributes can be governed deliberately. Physical denormalization for a specific serving table is a later optimization and must remain traceable to the governed dimension semantics.

5. Unknown and not-applicable are real analytical states

A missing relationship is not always the same situation. Unknown means the dimension should exist but cannot yet be resolved; not applicable means the relationship has no business meaning for that fact. Both are different from a known ragged hierarchy member whose city level is legitimately absent. Chapter 05 reserves surrogate key 0 for unknown and -1 for not applicable in the customer/product/geography dimensions.

This policy keeps fact foreign keys non-null, preserves joins, and makes “unknown context” countable. It also avoids using a blank string or SQL NULL as an undocumented catch-all state.

6. Hands-on verification

verify_dimension_usability.py

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');")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), controlsrows=conn.execute("""SELECT c.customer_name,p.product_name,g.region_name,f.extended_amountFROM fact_sales fJOIN dim_customer c ON c.customer_key=f.customer_keyJOIN dim_product p ON p.product_key=f.product_keyJOIN dim_geography g ON g.geography_key=c.geography_key""").fetchall()assert len(rows)==7assert all(r[0] and r[1] and r[2] for r in rows)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"""))assert category == {'Accessories':425,'Displays':200}, categoryprint('controls =',controls)print('category GMV =',category)print('dimension usability: PASS')

Cleanup is automatic because the database is in memory. With a file-backed lab database, delete only the disposable Chapter 05 file you created.

7. Production judgment

A useful dimension favors business labels, explicit identity, stable semantics, and predictable drill/filter behavior. It should be wide enough that analysts do not reconstruct obvious hierarchies with repetitive joins, but not so generic that unrelated entity types are forced into one abstract table. Chapter 05’s design is logical/semantic; engine-specific clustering, compression, and physical serving choices come later.

The next lesson separates the three key identities that are often conflated: the source natural/business key, the warehouse’s durable entity identity, and the surrogate row key used by facts.

Knowledge check

Check your understanding

Why should customer_name normally live in a customer dimension rather than every sales fact row?
What does surrogate key 0 mean in this chapter?
Why is a missing city on a known East-region customer not automatically the same as an unknown geography?
What proves that Chapter 05 did not change sales semantics?

Review the answers

1. Because it is descriptive context, not an atomic measurement. Centralizing the attribute reduces duplication and lets governance/history policy change without rewriting every fact row.

2. An unknown dimension member: the relationship should exist but cannot currently be resolved.

3. The country/region can be known while the hierarchy is legitimately ragged at the city level. Unknown geography means the dimension relationship itself is unresolved.

4. The same seven fact rows reconcile to 4 orders, 9 units, 625 GMV, 380 cost, and 245 gross profit after joining the enriched dimensions.

Summary and next step

Dimensions are the governed vocabulary around facts. Keep facts atomic, keep dimension labels readable, preserve non-null foreign-key policy with explicit default members, and verify enrichment against existing controls. Next, separate natural/business keys, durable identities, and surrogate keys.

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.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.