Dimensional Modeling
Definition
Dimensional modeling structures analytical data to present the needed information as simply as possible, return queries as quickly as possible, and accurately track the underlying business processes. It organizes data into fact tables (measurements) surrounded by dimension tables (context), optimized for reads rather than transactional updates.
Core Ideas
Fact vs dimension tables
- Fact table — numeric, usually additive measures (sales amount, units) that can be summed. Facts may be derived (net from gross profit). Not all numbers are facts; some descriptive info is kept in the fact table when used in most reports. The primary key is usually a composite key.
- Dimension table — the “nouns”; used as filter constraints and labels. A conformed dimension is shared across all business processes. Avoid
nullin dimensions (Oracle excludes null from calculations; MS treats numeric null as 0). Dimensions hold only a surrogate key and descriptive attributes.
Grain, keys, and the bus matrix
- Grain — the level of detail one fact row represents; declaring it is a core modeling step.
- Surrogate key — a system-generated integer key required in dimension tables (fact tables can use composite keys). Benefits: avoids duplication, tracks changes, insulates from source administrative changes, and is fast to join.
- Bus matrix — a technique for deciding which dimension tables are needed across processes.
The dimensional modeling process
- Choose the business process
- Declare the grain
- Choose the dimensions
- Choose the facts
Schemas
| Schema | Characteristics |
|---|---|
| Star | Simple, few joins, fast queries; de-normalized (more storage); fast load but slow validation; data integrity not enforced; harder to map from OLTP; not very flexible for new analysis needs |
| Snowflake | Dimensions normalized into hierarchies; less storage, better data quality, easier to update/map from OLTP; more joins → more complex queries; slow load but fast checking |
| Fact Constellation | Multiple fact tables sharing dimensions (“galaxy”); flexible for analysis; complex structure, harder to maintain; usually more space |
Snowflaking suits future-proofing (new reporting) when sub-dimensions (time, location, product type) don’t cost much storage.
Slowly Changing Dimensions (SCD)
How dimension history is preserved when attributes change:
- SCD0 — never update (retain original)
- SCD1 — overwrite (no history)
- SCD2 — add a new row with a flag (full history; must filter by the active flag)
- SCD3 — add a column (limited history)
- SCD4 — separate historical table (commonly used)
- SCD6 — hybrid of 1 + 2 + 3
Relationships
- Data Warehousing and Business Intelligence — the parent discipline and Kimball lifecycle
- Data Quality — nulls, outliers, and conformity handled during load
- Databases — contrast with normalized OLTP relational design
- Query Optimization — star schemas trade storage for join-light reads