The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling
One book, placed in its field — what Ralph Kimball, Margy Ross's book gets right, where it goes further than the rest, and what the rest of the field adds.
Mike West, Editor · Leading With Data · dataengineerprofessional.com/guides/profile-the_data_warehouse_toolkit
Arrow keys or swipe to move · N for notes · F for full screen
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling
One book, placed in its field — what Ralph Kimball, Margy Ross's book gets right, where it goes further than the rest, and what the rest of the field adds.
Mike West, Editor · Leading With Data · dataengineerprofessional.com/guides/profile-the_data_warehouse_toolkit
The problem
Business Acceptance and Adoption erodes when it is left to instinct instead of method.
You were taught the moves piecemeal, never the whole model.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
The model
- 01Dimensional Modeling Discipline
- 02Atomic Grain Data Availability
- 03Conformed Dimensions and Bus Architecture
- 04Verbose Descriptive Dimension Attributes
- 05Slowly Changing Dimension Change Handling
- 06ETL Subsystem Quality
- 07Business Requirements Alignment
- 08Strong Executive Business Sponsorship
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Step 1 of 5 · Dimensional Modeling Discipline
Grain is declared second and everything else derives from it — never identify facts or dimensions before the grain sentence exists.
Misconception
Practitioners believe the four steps are a loose checklist they can reorder or shortcut, often jumping straight to picking facts before the grain is nailed down.
Reality
The steps are strictly sequential because each one constrains the next: an undeclared grain makes it impossible to know which dimensions and facts legitimately belong in the table, so out-of-order work guarantees rework.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Step 2 of 5 · Atomic Grain Data Availability
The atomic grain carries the richest dimensionality; every level of summarization strips away dimensions and therefore future questions.
Misconception
Practitioners believe pre-aggregating facts at load time is the responsible choice because it saves storage and speeds up the common reports.
Reality
Aggregates are a performance optimization layered on top of atomic data, never a replacement for it; the atomic grain is what lets you attach maximum dimensionality and slice along axes nobody requested up front.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Step 3 of 5 · Conformed Dimensions and Bus Architecture
The bus matrix is the single most valuable early deliverable: it exposes shared dimensions and sequences delivery before a line of ETL is written.
Misconception
Conformance means every fact table must physically share one identical dimension table with all the same rows and columns.
Reality
Dimensions conform when they share consistent keys and a common subset of attributes; a shrunken rollup dimension (month vs. day, brand vs. product) conforms to its base as long as the shared attributes match exactly, so different processes can use different granularities and still drill across.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Step 4 of 5 · Verbose Descriptive Dimension Attributes
Decode operational codes into descriptive text inside the ETL process so end users never see a raw code.
Misconception
Practitioners believe keeping the original operational codes alongside a description column is enough, or that verbose attributes waste storage that should be optimized away.
Reality
Cryptic codes force users to memorize or look up mappings, and every additional dimension attribute costs almost nothing relative to the wide, low-cardinality fact tables; the ETL layer is precisely where decoding belongs so the code never surfaces to the query interface.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Step 5 of 5 · Slowly Changing Dimension Change Handling
Decide the SCD type per attribute based on the business's history requirement, not per dimension or by default.
Misconception
Teams assume Type 2 (add a new row) is the safe default for every changing attribute, or that the choice is a technical decision the ETL developer should make.
Reality
The correct type depends entirely on how the business wants to see history for that specific attribute, and the answer is a stewardship decision; over-applying Type 2 explodes dimension row counts and can distort trend analysis by fragmenting a single entity across many versioned rows.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
A framework to use
Enterprise Data Warehouse Bus Architecture
This framework is part of the full guide. See the guide →
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
The cheat sheet
- Dimensional Modeling DisciplineGrain is declared second and everything else derives from it — never identify facts or dimensions before the grain sentence exists.
- Atomic Grain Data AvailabilityThe atomic grain carries the richest dimensionality; every level of summarization strips away dimensions and therefore future questions.
- Conformed Dimensions and Bus ArchitectureThe bus matrix is the single most valuable early deliverable: it exposes shared dimensions and sequences delivery before a line of ETL is written.
- Verbose Descriptive Dimension AttributesDecode operational codes into descriptive text inside the ETL process so end users never see a raw code.
- Slowly Changing Dimension Change HandlingDecide the SCD type per attribute based on the business's history requirement, not per dimension or by default.
- ETL Subsystem QualityTreat ETL as 34 subsystems and audit for completeness; the missing ones drive most downstream defects.
- Business Requirements AlignmentModel the measurement process, not the report; reports change, business processes persist.
- Strong Executive Business SponsorshipThe right sponsor has a compelling motivation and the political weight to enforce conformance across silos.
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data
Sources
- The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling — Ralph Kimball, Margy Ross
The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling · Mike West, Editor · Leading With Data