Dimensional Modeling
A data warehouse design methodology focused on query performance and business usability, developed by Ralph Kimball.
Last updated: 2026-04-12
Overview
Dimensional modeling is the standard approach for structuring data in a data warehouse for analytics. It organizes data into two types of tables: fact tables (measurable events) and dimension tables (descriptive context). The result is a star-schema that is intuitive for business users and fast for aggregation queries.
Contrast with the Inmon (3NF) approach: Inmon advocates normalizing the enterprise warehouse first, then building data marts; Kimball advocates building conformed dimensional data marts directly.
Core Principles
- Grain declaration: Every fact table must have a declared grain — the atomic level of detail one row represents. Never mix grains.
- Facts are numeric and additive: Measures should be as additive as possible (sum across any dimension). Semi-additive (sum across some dimensions) and non-additive facts require care.
- Dimensions provide context: The descriptive attributes that answer “who, what, where, when, why, how” around a fact.
- Conformed dimensions: Shared dimensions across multiple fact tables allow “drill-across” queries spanning business processes.
- Conformed facts: Metrics with the same definition across business processes (e.g., revenue defined identically everywhere).
Schemas
- star-schema: Fact table at center, denormalized dimensions radiating out. Best query performance.
- Snowflake schema: Dimensions are normalized into sub-dimensions. Saves storage, hurts query performance.
- Constellation (bus) schema: Multiple fact tables sharing conformed dimensions. Standard in enterprise DWH.
Fact Table Types
See fact-table-types for full detail. Summary:
- Transactional: One row per event (most common)
- Periodic snapshot: One row per entity per period
- Accumulating snapshot: One row per lifecycle instance, updated as it progresses
- Factless: Records events with no numeric measure (attendance, coverage)
- Aggregate: Pre-summarized for performance
Dimension Types
See slowly-changing-dimensions for SCD types 0–7.
Other dimension patterns:
- Role-playing dimension: Same physical table used in multiple roles (e.g., date as order date, ship date, due date)
- Junk dimension: Consolidates low-cardinality flags/indicators into one table
- Degenerate dimension: A dimension key with no dimension table (e.g., invoice number stored directly in fact)
- Bridge table: Handles many-to-many relationships between facts and dimensions
- Outrigger: A dimension that references another dimension (use sparingly)
Surrogate Keys
Dimensional modeling uses surrogate keys (meaningless integers) as primary keys on dimension tables. Never use natural/operational keys as the DWH primary key — they break when source systems change.
Kimball vs. Inmon
| Kimball | Inmon | |
|---|---|---|
| Approach | Bottom-up (data marts first) | Top-down (enterprise EDW first) |
| Model | Dimensional (star schema) | 3NF normalized |
| Speed to value | Faster | Slower |
| Consistency | Conformed dimensions | Central EDW enforces it |
| Key person | ralph-kimball | bill-inmon |
Connections
- star-schema — the physical schema produced by dimensional modeling
- fact-table-types — full taxonomy of fact table patterns
- slowly-changing-dimensions — how dimension history is handled
- medallion-architecture — the Silver layer is where Kimball dimensional modeling belongs
- semantic-layer — sits above the dimensional model; governs metric definitions for consumers
- etl-architecture — how data flows into a dimensional model
- data-warehouse — the broader system dimensional modeling lives in
- ralph-kimball — originator of dimensional modeling methodology
- bill-inmon — alternative methodology (top-down 3NF)
Sources
- Dimensional Modeling — Joakim Dalby — added 2026-04-12