Kimball dimensional modeling is a data warehouse design methodology introduced by Ralph Kimball in 1996, focused on creating business-driven, user-friendly star schemas that optimize query performance and analytical reporting. At its core, the approach organizes data into fact tables (measurable business events) and dimension tables (descriptive context), with a bottom-up implementation strategy that delivers rapid, incremental value to specific business processes. The methodology's enduring influence lies in the conformed dimension concept — shared, standardized dimensions that enable enterprise-wide consistency and cross-process analysis through a technique called drilling across, which remains essential even in modern cloud data platforms like Snowflake, Databricks, and BigQuery for maintaining semantic coherence across distributed data marts.
What This Cheat Sheet Covers
This topic spans 12 focused tables and 104 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: Core Design Methodology
| Concept | Example | Description |
|---|---|---|
1. Select business process2. Declare grain3. Identify dimensions4. Identify facts | • Sequential design steps forming the foundation of every dimensional model • grain declaration is the pivotal step that determines fact table row uniqueness | |
FactSales → joins to → DimProduct, DimDate, DimCustomer, DimStore | • Denormalized structure with a central fact table surrounded by dimension tables • optimizes query performance and simplifies business user comprehension | |
DimProduct → DimCategory → DimBrand | • Normalized variant where dimension hierarchies are broken into secondary tables • Kimball recommends avoiding snowflakes because they are harder for users to navigate and can hurt query performance | |
One row per product sold per transaction per store per day | • Precise statement of what a single fact table row represents • must be the lowest atomic level to enable maximum flexibility for slicing and aggregation | |
Rows = business processes Columns = dimensions Shaded cells = shared dimensions | Enterprise planning tool showing which conformed dimensions are used by which business processes, enabling integrated incremental development | |
Same DimCustomer used in FactSales, FactReturns, FactServiceCalls | • Standardized master dimensions shared across multiple fact tables • essential for cross-process analysis and consistent business definitions enterprise-wide |