Lesson 4 — Dimensional Modeling in Depth
Dimensional vocabulary is compact but extremely testable because each term answers a different question.
Fact vs dimension
Fact
A fact represents measurements associated with a business process. Examples could include quantity, amount, count, duration, or another measurable result.
Dimension
A dimension provides descriptive context for analyzing those measurements — the “by what?” perspectives.
Example:
Enrollment Count by Semester, Campus, Program, Student Type
Enrollment Count is a measure associated with the fact process; Semester/Campus/Program/Student Type are descriptive dimensional perspectives.
Grain — define it before interpreting facts
Grain states exactly what one fact-table row means.
Example:
One row = one Student registration in one Course on one Registration Date.
Grain is not cardinality. Cardinality describes relationship participation; grain describes the semantic level of a fact row.
If grain is unclear, two measures with the same name can mean different things and aggregation can become invalid.
Conformed dimensions and conformed facts
Conformed dimension
A shared, consistently defined descriptive dimension used across dimensional models/data marts.
Example: multiple marts use the same definition and values for Campus.
Conformed fact
A measure whose definition is standardized consistently across models.
Memory hook: shared context vs shared measure meaning.
Slowly Changing Dimensions — ORC
Chapter 5’s three core history-handling types can be remembered as Overwrite / Row / Column.
Type 1 — Overwrite
Replace the old descriptive value. No history is retained.
Use when the prior value is not analytically important — for example, correcting a misspelling.
Type 2 — New Row
Create a new dimension row/version. Preserve historical versions and distinguish current from prior rows.
Use when complete history matters.
Type 3 — New Column
Add a separate column for limited previous-state information, such as Previous Department.
Use when only a small amount of prior-state history is required.
Star vs snowflake
Star
Dimensions are comparatively collapsed into single descriptive structures around the fact.
Snowflake
Dimensions are more normalized, separating hierarchy/component structures into related tables.
The distinction is not “good vs bad.” It is a dimensional physical-design trade-off involving navigation, simplicity, redundancy, and normalization.
Common traps
- Fact vs dimension: measurement vs descriptive context.
- Grain vs fact: row meaning vs measured content.
- Grain vs cardinality: fact-row semantic level vs relationship participation.
- Conformed dimension vs conformed fact: shared context vs shared measure.
- SCD 1/2/3: overwrite vs new row vs new column.
- Star vs snowflake: collapsed vs normalized dimensions.
Changed-fact examples
- “Preserve every address version” → Type 2. Change the fact to “history is explicitly unnecessary” → Type 1.
- “Campus, City, State, Region stay in one dimension” → star-like. Split hierarchy into related normalized dimension tables → snowflake.
- “Sales and Enrollment use identical Campus definitions” → conformed dimension. If what is standardized is
Net Revenuecalculation instead → conformed fact.
Source anchors: current Mastery Lab dimensional sections, pp. 137–139.