Dimensional Modeling is a data modeling technique created by Ralph Kimball specifically for data warehouses and business intelligence. It organizes data into fact tables (quantitative measurements) and dimension tables (descriptive context), making data intuitive for business users and optimized for analytical queries.
Core Components
Fact Tables
Store the quantitative measurements of a business process:
- Rows: One per event/transaction (an order, a click, a payment)
- Columns: Foreign keys to dimensions + numeric measures
- Grain: The level of detail (e.g., one row per order line item)
``sql`
-- Example: sales_fact
order_id | date_key | product_key | customer_key | quantity | revenue | discount
Dimension Tables
Provide descriptive context for facts:
- Rows: One per business entity (a product, a customer, a date)
- Columns: Descriptive attributes (name, category, address)
- Denormalized: Flatten hierarchies for query performance
`sql``
-- Example: dim_product
product_key | product_name | category | subcategory | brand | color | size | price
Schema Designs
Star Schema
- Fact table in the center, dimension tables around it
- Dimensions are denormalized (no further joins needed)
- Best for: Most data warehouse use cases
Snowflake Schema
- Dimensions are normalized (broken into sub-dimensions)
- Saves storage but requires more joins
- Best for: When storage is expensive or data changes frequently
Galaxy Schema (Fact Constellation)
- Multiple fact tables sharing dimensions
- Best for: Complex businesses with multiple business processes
Kimball's Four-Step Process
1. Select the Business Process: What are you measuring? (Sales, clicks)
2. Declare the Grain: What does one row represent? (One order line)
3. Identify the Dimensions: Who, what, when, where, why (5 Ws)
4. Identify the Facts: What numeric measurements to capture
Types of Fact Tables
| Type | Description | Example |
|------|-------------|--------|
| Transaction | One row per event | Individual sales |
| Periodic Snapshot | One row per time period | Daily account balance |
| Accumulating Snapshot | One row per lifecycle | Order fulfillment stages |
| Factless | No measures, just events | Student attendance |
Slowly Changing Dimensions (SCDs)
- Type 1: Overwrite old values (lose history)
- Type 2: Add new rows with effective dates (keep history)
- Type 3: Add columns for old/new values (limited history)
Common Use Cases
1. Enterprise BI: Build the analytical layer for dashboards
2. dbt Models: Implement dimensional models as dbt transforms
3. Self-Service Analytics: Business users query intuitive structures
4. Data Marts: Department-specific analytical datasets
5. Metrics Layer: Define consistent business metrics