Chapter 06 · Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics
Bus Matrix: Rows as Processes, Columns as Dimensions, and Incremental Warehouse Delivery
Build AtlasMart’s enterprise bus matrix across sales, fulfillment, inventory, returns, and marketing so teams can deliver one process at a time while preserving shared dimensional contracts.
Learning outcomes
AtlasMart cannot implement five process marts as isolated projects and hope they integrate later. The enterprise bus matrix makes the intended process/dimension intersections visible before teams build them, while still allowing one process row to be delivered at a time.
Read a bus matrix as business-process rows and dimension columns rather than as a physical schema diagram.
Use matrix cells to identify where dimensions must be conformed and where a dimension does not apply.
Attach a grain statement and key measures to each process row.
Sequence incremental delivery without creating incompatible local dimensions.
Detect the anti-pattern of forcing every process into one fact or every dimension into every row.
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.
1. The bus matrix is an integration blueprint
The matrix does not say that Sales and Inventory should share one fact table. It says they both need a compatible Product and Date vocabulary. Likewise, Marketing and Sales share Customer/Product/Date, while Campaign belongs only to Marketing. This is the difference between integration and uniformity.
| Business process / fact grain | Date | Customer | Product | Store | Campaign | Return reason |
|---|---|---|---|---|---|---|
| Sales — one paid order line | ✓ order date | ✓ | ✓ | ✓ | — | — |
| Fulfillment — one shipped order line | ✓ ship date | ✓ | ✓ | — | — | — |
| Inventory — one product per snapshot date | ✓ snapshot date | — | ✓ | — | — | — |
| Returns — one returned order line event | ✓ return date | ✓ | ✓ | — | — | ✓ |
| Marketing — one customer/product/campaign exposure event | ✓ exposure date | ✓ | ✓ | — | ✓ | — |
2. Scan rows for process correctness and columns for conformance
Reading across a row asks: does this dimension make sense at the process grain? Reading down a column asks: where must this dimension retain compatible meaning? For Inventory, Customer is blank because “one product snapshot per date” is not customer-specific. For Returns, Return Reason applies because it describes a return event. For Sales, Return Reason would be nonsensical.
| Process | Declared grain | Core measures/evidence | Shared conformance obligations |
|---|---|---|---|
| Sales | one paid order line | quantity, paid GMV, cost-at-sale | date, customer, product |
| Fulfillment | one shipped order line | shipped units | date, customer, product |
| Inventory | one product per snapshot date | on-hand units | date, product |
| Returns | one returned order-line event | returned units, return amount | date, customer, product |
| Marketing | one customer/product/campaign exposure event | row count / exposure occurrence | date, customer, product |
3. Incremental delivery works only if the connection points are governed first
A practical sequence could deliver Sales first because its requirements and source evidence are mature, then Inventory, Fulfillment, Returns, and Marketing. But when Sales creates Product, it cannot treat that dimension as a private implementation detail if the bus matrix already identifies Product as an enterprise connection point. The Product contract needs identity, attribute-domain, ownership, unknown-member, and change policies that later processes can reuse.
process,grain,date,customer,product,store,campaign,return_reasonsales,paid order line,Y,Y,Y,Y,N,Nfulfillment,shipped order line,Y,Y,Y,N,N,Ninventory,product snapshot date,Y,N,Y,N,N,Nreturns,returned order-line event,Y,Y,Y,N,N,Ymarketing,customer-product-campaign exposure,Y,Y,Y,N,Y,N4. Deliberately wrong approach: centralize every row before delivering anything
A “big-bang” interpretation of enterprise architecture says no process can ship until every dimension and source is globally standardized. That can freeze delivery. The opposite failure lets every team ship private dimensions and postpones integration forever. The bus architecture exists between those extremes: identify enterprise connection points up front, then implement manageable process-centric increments against those contracts.
The lab sequence therefore treats shared Date/Product/Customer semantics as architectural dependencies, but it does not require Marketing, Returns, and Fulfillment to be production-ready before Sales can exist.
5. Add acceptance gates to each row
| Gate | Sales example | Why it matters |
|---|---|---|
| Grain frozen | one paid order line | prevents mixed-grain facts |
| Shared keys resolve | date/customer/product mappings complete or explicit unknown | prevents orphaned cross-process headers |
| Controls reconcile | 625 paid GMV / 9 units | protects correctness before integration |
| Conformance tests pass | P100 category matches governed domain | prevents same-name semantic drift |
| Local-only dimensions labeled | Store belongs to Sales in this matrix | avoids false enterprise promises |
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")# Independent process controls before integration.assert conn.execute("SELECT SUM(quantity),SUM(extended_amount) FROM fact_sales").fetchone()==(9,625)assert conn.execute("SELECT SUM(shipped_units) FROM fact_fulfillment").fetchone()[0]==9assert conn.execute("SELECT SUM(on_hand_units) FROM fact_inventory_snapshot WHERE snapshot_date_key=20260920").fetchone()[0]==137assert conn.execute("SELECT SUM(returned_units),SUM(return_amount) FROM fact_returns").fetchone()==(1,25)assert conn.execute("SELECT COUNT(*) FROM fact_marketing_exposure").fetchone()[0]==6print('process controls: PASS')These checks are intentionally process-local. A bus matrix does not excuse a team from reconciling its own fact before drill-across begins.
Knowledge check
Check your understanding
- What does a blank Customer cell for Inventory mean?
- Why should Product governance start before every process is delivered?
- Does a bus matrix prescribe one physical database?
- Why validate each process control before cross-process queries?
Review the answers
1. Customer is not a valid dimension for the inventory snapshot process at its declared grain; it is not “missing data.”
2. Because Product is a planned enterprise connection point. Later process teams need a stable identity and shared attribute contract to integrate incrementally.
3. No. It is an architectural/semantic blueprint; conformed dimensions can connect models across different physical systems when their shared domains are compatible.
4. Integration cannot repair incorrect local facts. Each process must first prove its own grain and totals.
Summary and next step
The bus matrix makes integration obligations visible without erasing process autonomy. Next, assign owners and definitions so shared dimensions and measures do not drift after teams begin delivering independently.
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.