Chapter 06 · Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics
Shared Date/Product/Customer Dimensions Across Marts with Different Grain
Use shared date, product, and customer dimensions across facts with different grains, then perform safe multipass drill-across without atomic fact-to-fact joins or invented dimensions.
Learning outcomes
AtlasMart executives want one product view showing sold units, shipped units, ending inventory, returns, marketing exposures, and paid GMV. Those values live in facts with different grains. The correct integration pattern is multipass: aggregate each fact independently to compatible conformed row headers, then align the result sets.
Explain why shared dimensions do not make atomic fact-to-fact joins safe.
Aggregate sales, fulfillment, inventory, returns, and marketing independently before alignment.
Use Product as the conformed row header while respecting each process date role and grain.
Explain why Customer is valid for several processes but absent from Inventory.
Prove safe drill-across totals against independent source/process controls.
Chapter 06 preserves every accepted AtlasMart control from Chapters 01–05. Paid sales remain 7 order lines, 4 paid orders, 9 sold units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit (39.2% recomputed gross margin). The September 20 inventory snapshot remains 137 units. Chapter 06 adds clearly labeled synthetic fulfillment, return, and marketing process fixtures only to teach cross-process conformance; those new processes never rewrite the established sales or inventory evidence.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. It
proves semantic and reconciliation properties, not
warehouse-scale performance. Record your actual Python/SQLite
versions before execution. Date roles, key mappings, metric
definitions, and conformance rules are explicit contracts; do
not infer production governance, latency, security, cost, or
SLO guarantees from this toy fixture.
The fixture uses a physically shared
dim_product to make the mechanism easy to see. In
separate marts, the same logic requires compatible conformed
product row-header domains before answer sets can be merged.
1. Shared keys do not remove multiplicity
Sales has multiple rows for P100 and Fulfillment also has
multiple rows for P100. Joining the two atomic facts on
product_key creates every sales-row/fulfillment-row
combination for that product. The key values match correctly,
yet the measures duplicate. Conformance enables alignment; it
does not magically make many-to-many fact joins additive.
SELECT SUM(s.quantity) AS wrongly_repeated_sold_units, SUM(f.shipped_units) AS wrongly_repeated_shipped_unitsFROM fact_sales sJOIN fact_fulfillment f ON f.product_key=s.product_key;Expected result in this fixture: 17 sold units and 17 shipped units—both wrong. Each true process control is 9.
2. Drill across with separate passes
The safe approach groups each fact to the chosen common row header first. Here the row header is Product. Inventory also fixes its time context to the September 20 ending snapshot before aggregation because balances are semi-additive across time. The grouped answer sets can then be aligned through the conformed product domain.
WITH sales AS ( SELECT product_key, SUM(quantity) AS sold_units, SUM(extended_amount) AS paid_gmv FROM fact_sales GROUP BY product_key), shipped AS ( SELECT product_key, SUM(shipped_units) AS shipped_units FROM fact_fulfillment GROUP BY product_key), inventory AS ( SELECT product_key, SUM(on_hand_units) AS ending_on_hand FROM fact_inventory_snapshot WHERE snapshot_date_key = 20260920 GROUP BY product_key), returns AS ( SELECT product_key, SUM(returned_units) AS returned_units, SUM(return_amount) AS return_amount FROM fact_returns GROUP BY product_key), marketing AS ( SELECT product_key, COUNT(*) AS exposures FROM fact_marketing_exposure GROUP BY product_key)SELECT p.product_id, p.product_name, COALESCE(s.sold_units,0) AS sold_units, COALESCE(sh.shipped_units,0) AS shipped_units, COALESCE(i.ending_on_hand,0) AS ending_on_hand, COALESCE(r.returned_units,0) AS returned_units, COALESCE(m.exposures,0) AS exposures, COALESCE(s.paid_gmv,0) AS paid_gmv, COALESCE(r.return_amount,0) AS return_amountFROM dim_product pLEFT JOIN sales s ON s.product_key=p.product_keyLEFT JOIN shipped sh ON sh.product_key=p.product_keyLEFT JOIN inventory i ON i.product_key=p.product_keyLEFT JOIN returns r ON r.product_key=p.product_keyLEFT JOIN marketing m ON m.product_key=p.product_keyWHERE p.product_key > 0ORDER BY p.product_key;3. Read the result without pretending the grains became equal
| Product | Sold units | Shipped units | Ending on-hand | Returned units | Exposures | Paid GMV | Return amount |
|---|---|---|---|---|---|---|---|
| P100 Keyboard | 3 | 3 | 40 | 0 | 2 | 150 | 0 |
| P200 Mouse | 3 | 3 | 60 | 1 | 1 | 75 | 25 |
| P300 Monitor | 1 | 1 | 12 | 0 | 1 | 200 | 0 |
| P400 Dock | 2 | 2 | 25 | 0 | 2 | 200 | 0 |
| Control total | 9 | 9 | 137 | 1 | 6 | 625 | 25 |
The report places process measures side-by-side by Product. It does not assert that an exposure caused a sale or that every sale was shipped on the same date. Those would require additional business logic and potentially event-level linkage.
4. Date is conformed while roles remain process-specific
All processes use the same calendar domain, but the fact foreign keys mean different roles: order date, ship date, snapshot date, return date, and exposure date. A report must choose which role it is grouping. “By date” is incomplete unless the row header identifies the business date role. Role-playing details are expanded in Chapter 08; for now, the names prevent semantic collapse.
5. Customer does not belong in every process
Sales, Fulfillment, Returns, and Marketing can group by the governed customer domain because their event grains identify a customer. Inventory is product-state, not customer-state. A cross-process customer report therefore cannot include inventory unless it changes the analytical question—for example, presenting enterprise inventory beside customer measures without pretending inventory belongs to each customer. Duplicating 137 units across customers would be a semantic error.
import sqlite3conn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_marketing_exposure;\nDROP TABLE IF EXISTS fact_returns;\nDROP TABLE IF EXISTS fact_fulfillment;\nDROP TABLE IF EXISTS fact_inventory_snapshot;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS source_key_map;\nDROP TABLE IF EXISTS marketing_product_local;\nDROP TABLE IF EXISTS dim_return_reason;\nDROP TABLE IF EXISTS dim_campaign;\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);\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);\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);\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);\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);\nCREATE TABLE dim_campaign (\n campaign_key INTEGER PRIMARY KEY,\n campaign_id TEXT NOT NULL UNIQUE,\n campaign_name TEXT NOT NULL\n);\nCREATE TABLE dim_return_reason (\n return_reason_key INTEGER PRIMARY KEY,\n return_reason_code TEXT NOT NULL UNIQUE,\n return_reason_name TEXT NOT NULL\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');\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');\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);\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');\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');\nINSERT INTO dim_campaign VALUES\n(401,'CMP-ACCESSORY','Accessory Awareness'),\n(402,'CMP-DISPLAY','Display Upgrade');\nINSERT INTO dim_return_reason VALUES\n(501,'CHANGED_MIND','Changed mind');\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);\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');\n\nCREATE TABLE fact_inventory_snapshot (\n snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n on_hand_units INTEGER NOT NULL CHECK(on_hand_units >= 0),\n source_batch_id TEXT NOT NULL,\n PRIMARY KEY(snapshot_date_key,product_key)\n);\nINSERT INTO fact_inventory_snapshot VALUES\n(20260919,201,42,'INV-20260919'),\n(20260919,202,63,'INV-20260919'),\n(20260919,203,13,'INV-20260919'),\n(20260919,204,28,'INV-20260919'),\n(20260920,201,40,'INV-20260920'),\n(20260920,202,60,'INV-20260920'),\n(20260920,203,12,'INV-20260920'),\n(20260920,204,25,'INV-20260920');\n\nCREATE TABLE fact_fulfillment (\n fulfillment_key INTEGER PRIMARY KEY,\n ship_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 order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n shipment_id TEXT NOT NULL,\n shipped_units INTEGER NOT NULL CHECK(shipped_units > 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(shipment_id,order_id,line_no)\n);\nINSERT INTO fact_fulfillment VALUES\n(1,20260919,101,201,'O1001',1,'SH1001',2,'FUL-001'),\n(2,20260919,101,202,'O1001',2,'SH1001',1,'FUL-001'),\n(3,20260920,102,203,'O1002',1,'SH1002',1,'FUL-001'),\n(4,20260920,101,204,'O1003',1,'SH1003',1,'FUL-001'),\n(5,20260920,101,202,'O1003',2,'SH1003',2,'FUL-001'),\n(6,20260920,104,201,'O1005',1,'SH1005',1,'FUL-001'),\n(7,20260920,104,204,'O1005',2,'SH1005',1,'FUL-001');\n\nCREATE TABLE fact_returns (\n return_key INTEGER PRIMARY KEY,\n return_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 return_reason_key INTEGER NOT NULL REFERENCES dim_return_reason(return_reason_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n return_id TEXT NOT NULL UNIQUE,\n returned_units INTEGER NOT NULL CHECK(returned_units > 0),\n return_amount NUMERIC NOT NULL CHECK(return_amount >= 0),\n source_batch_id TEXT NOT NULL\n);\nINSERT INTO fact_returns VALUES\n(1,20260920,101,202,501,'O1003',2,'R1001',1,25,'RET-001');\n\nCREATE TABLE fact_marketing_exposure (\n exposure_key INTEGER PRIMARY KEY,\n exposure_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 campaign_key INTEGER NOT NULL REFERENCES dim_campaign(campaign_key),\n source_event_id TEXT NOT NULL UNIQUE\n);\nINSERT INTO fact_marketing_exposure VALUES\n(1,20260918,101,201,401,'EXP-001'),\n(2,20260918,101,202,401,'EXP-002'),\n(3,20260918,102,203,402,'EXP-003'),\n(4,20260919,101,204,401,'EXP-004'),\n(5,20260920,104,201,401,'EXP-005'),\n(6,20260920,104,204,401,'EXP-006');\n\nCREATE TABLE source_key_map (\n source_system TEXT NOT NULL,\n entity_type TEXT NOT NULL,\n source_key TEXT NOT NULL,\n durable_id TEXT NOT NULL,\n warehouse_key INTEGER NOT NULL,\n PRIMARY KEY(source_system,entity_type,source_key)\n);\nINSERT INTO source_key_map VALUES\n('erp','customer','C001','D-CUST-001',101),\n('marketing','customer','MKT-C001','D-CUST-001',101),\n('erp','customer','C002','D-CUST-002',102),\n('marketing','customer','MKT-C002','D-CUST-002',102),\n('erp','customer','C004','D-CUST-004',104),\n('marketing','customer','MKT-C004','D-CUST-004',104),\n('erp','product','P100','P100',201),\n('marketing','product','M-P100','P100',201),\n('erp','product','P200','P200',202),\n('marketing','product','M-P200','P200',202),\n('erp','product','P300','P300',203),\n('marketing','product','M-P300','P300',203),\n('erp','product','P400','P400',204),\n('marketing','product','M-P400','P400',204);\n\nCREATE TABLE marketing_product_local (\n product_code TEXT PRIMARY KEY,\n product_name TEXT NOT NULL,\n category_name TEXT NOT NULL\n);\nINSERT INTO marketing_product_local VALUES\n('P100','Keyboard','Peripherals'),\n('P200','Mouse','Accessories'),\n('P300','Monitor','Displays'),\n('P400','Dock','Accessories');\n")wrong=conn.execute("""SELECT SUM(s.quantity),SUM(f.shipped_units)FROM fact_sales s JOIN fact_fulfillment f ON f.product_key=s.product_key""").fetchone()assert wrong==(17,17),wrongrows=conn.execute('WITH sales AS (\n SELECT product_key,\n SUM(quantity) AS sold_units,\n SUM(extended_amount) AS paid_gmv\n FROM fact_sales GROUP BY product_key\n), shipped AS (\n SELECT product_key, SUM(shipped_units) AS shipped_units\n FROM fact_fulfillment GROUP BY product_key\n), inventory AS (\n SELECT product_key, SUM(on_hand_units) AS ending_on_hand\n FROM fact_inventory_snapshot\n WHERE snapshot_date_key = 20260920\n GROUP BY product_key\n), returns AS (\n SELECT product_key,\n SUM(returned_units) AS returned_units,\n SUM(return_amount) AS return_amount\n FROM fact_returns GROUP BY product_key\n), marketing AS (\n SELECT product_key, COUNT(*) AS exposures\n FROM fact_marketing_exposure GROUP BY product_key\n)\nSELECT p.product_id, p.product_name,\n COALESCE(s.sold_units,0) AS sold_units,\n COALESCE(sh.shipped_units,0) AS shipped_units,\n COALESCE(i.ending_on_hand,0) AS ending_on_hand,\n COALESCE(r.returned_units,0) AS returned_units,\n COALESCE(m.exposures,0) AS exposures,\n COALESCE(s.paid_gmv,0) AS paid_gmv,\n COALESCE(r.return_amount,0) AS return_amount\nFROM dim_product p\nLEFT JOIN sales s ON s.product_key=p.product_key\nLEFT JOIN shipped sh ON sh.product_key=p.product_key\nLEFT JOIN inventory i ON i.product_key=p.product_key\nLEFT JOIN returns r ON r.product_key=p.product_key\nLEFT JOIN marketing m ON m.product_key=p.product_key\nWHERE p.product_key > 0\nORDER BY p.product_key;').fetchall()assert rows==[ ('P100','Keyboard',3,3,40,0,2,150,0), ('P200','Mouse',3,3,60,1,1,75,25), ('P300','Monitor',1,1,12,0,1,200,0), ('P400','Dock',2,2,25,0,2,200,0),],rowsassert sum(r[2] for r in rows)==9assert sum(r[3] for r in rows)==9assert sum(r[4] for r in rows)==137assert sum(r[5] for r in rows)==1assert sum(r[6] for r in rows)==6assert sum(r[7] for r in rows)==625assert sum(r[8] for r in rows)==25print('wrong atomic join=',wrong)print('safe drill-across rows=',rows)print('drill-across reconciliation: PASS')Knowledge check
Check your understanding
- Why does joining two facts on a valid conformed product key still duplicate measures?
- What is the safe drill-across sequence?
- Why is ending inventory filtered to one snapshot date?
- Why can the report show exposures next to sales but not claim causality?
Review the answers
1. Because each fact may have multiple atomic rows per product. The join forms combinations between those rows, so matching keys do not guarantee compatible cardinality.
2. Query each fact separately, aggregate it to the compatible conformed row headers and required time context, then align/merge the summarized answer sets.
3. Inventory balance is semi-additive across time; summing multiple daily states would duplicate stock state rather than measure flow.
4. Conformed dimensions align descriptive context, but they do not establish event-level causal linkage between marketing exposures and purchases.
Summary and next step
Different-grain facts can participate in one report only after each process preserves its own semantics and is separately aggregated to common conformed row headers. The final lesson turns this into a repeatable conformance audit.
Authoritative references
- Kimball Group — Conformed Dimensions — Shared attribute domains that enable consistent row headers across fact tables.
- Kimball Group — Conformed Facts — Consistent technical definitions for measurements reused across fact tables.
- Kimball Group — Enterprise Data Warehouse Bus Architecture — Incremental process-centric delivery connected by standardized conformed dimensions.
- Kimball Group — Enterprise Data Warehouse Bus Matrix — Business-process rows, dimension columns, and incremental delivery planning.
- Kimball Group — Drilling Across — Separate fact-table queries aligned on identical conformed row headers.
- SQLite — Foreign Key Support — Referential-integrity behavior for the local deterministic lab.