Dimensional modeling is the dominant design technique for BI and data warehouse systems, organizing data into fact tables (measurements) and dimension tables (context), arranged in star or snowflake schemas optimized for analytic queries. Ralph Kimball's approach — codified in The Data Warehouse Toolkit — remains the industry standard because it maps directly to how business users think about data: events measured across descriptive dimensions. The critical insight practitioners must internalize is that grain definition must happen before anything else: once the grain is wrong, every query built on top of it produces misleading results, and no amount of BI tooling can fix a fundamentally mis-modeled fact table.
What This Cheat Sheet Covers
This topic spans 14 focused tables and 88 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: Core Schema Types
Star and snowflake schemas are the two dominant physical arrangements of dimensional models. Understanding their structural differences — and when each is appropriate — is the first decision a modeler faces before writing a single DDL statement.
| Type | Example | Description |
|---|---|---|
FactSales → DimProduct, DimDate, DimCustomer | • Central fact table directly joined to fully denormalized dimension tables • fewest joins, fastest query performance, simplest for BI tools | |
DimProduct → DimCategory → DimDepartment | • Dimension tables are normalized into sub-dimension tables • reduces storage redundancy but increases join complexity and query time | |
FactSales + FactInventory → DimDate (shared) | • Multiple fact tables sharing conformed dimension tables • models complex enterprise data with multiple business processes | |
FactSales(DateKey, ProductKey, SalesAmt) | • Central table storing measurements or events • contains foreign keys to dimensions plus numeric measures • narrow but extremely large in row count |