Kimball-Style Dimensional Modeling Guide

Overview

Kimball-style dimensional modeling is a practical approach to data warehouse design that prioritizes query performance, user accessibility, and business understanding. This guide outlines the core rules and principles an agent should follow when designing or evaluating dimensional models.

---

Core Rules

Rule 1: Organize Data into Fact and Dimension Tables

• Fact Tables: Store measurable business events and metrics (transactions, orders, clicks, impressions, page views)
  • Contain foreign keys to dimension tables
  • Contain numerical measures (amounts, quantities, counts)
  • Represent events that happened at a specific point in time
• Dimension Tables: Store descriptive attributes and context (products, customers, dates, locations)
  • Describe the "who, what, when, where, why, and how" of facts
  • Contain natural language attributes for reporting and filtering
  • Change infrequently compared to fact tables

Rule 2: Denormalize Dimension Tables

• Store related attributes together in a single dimension table
• Avoid splitting dimensions across multiple tables to reduce join complexity
• Accept some data redundancy to achieve better query performance
• Favor read performance over storage optimization

Rule 3: Use Conformed Dimensions

• Create shared dimension tables used across multiple fact tables
• Ensure consistency and common business definitions across the warehouse
• Enable "drilling across" - analyzing multiple business processes through the same dimension
• Example: A single Customer dimension shared by Sales, Returns, and Support fact tables

Rule 4: Implement Star Schema Architecture

• Central fact table surrounded by dimension tables in a "star" pattern
• Simple, intuitive structure that mirrors natural business relationships
• Dimensions radiate directly from the fact table
• Minimizes join complexity and improves query optimization

Rule 5: Use Surrogate Keys in Dimensions

• Generate system-level integer primary keys (auto-increment) for each dimension
• Do NOT use natural keys (business keys) as primary keys
• Benefits:
  • Protects against changes in source systems
  • Improves join performance
  • Enables tracking dimension changes over time
  • Simplifies slowly changing dimension management

Rule 6: Implement Surrogate Keys in Facts

• Use the surrogate keys from dimension tables as foreign keys in fact tables
• Never use business keys directly in fact tables
• Store foreign keys to all relevant dimensions

Rule 7: Implement Slowly Changing Dimension (SCD) Logic

Handle dimension attribute changes systematically using one of these approaches:

Type 1 - Overwrite:

• Overwrite old values with new values
• Use when history is not important
• Example: Customer address correction

Type 2 - Add New Row:

• Create a new row for each change with effective date tracking
• Use when full historical analysis is needed
• Include fields: EffectiveDate, EndDate, IsCurrent
• Use surrogate keys to link to fact tables

Type 3 - Hybrid:

• Store both current and previous values in separate columns
• Use when tracking one level of historical change
• Include fields: CurrentValue, PreviousValue, DateOfChange

Rule 8: Define Granularity Upfront

• Establish the lowest level of detail (grain) for each fact table before design
• All measures in a fact table must be additive at the defined grain
• Examples:
  • Sales: One row per transaction
  • Inventory: One row per product per day per warehouse
  • Student Enrollment: One row per student per course per term
• Enforce consistency - don't mix different grains in one fact table

Rule 9: Always Include a Date Dimension

• Create an explicit Date dimension, never rely on date fields alone
• Pre-calculate common time attributes:
  • Calendar year, fiscal year, quarter, month, week, day
  • Day of week, day of month, day of year
  • Week number, is_holiday, is_weekend, business_day
  • Season, fiscal period
• Store one row per date (usually 50-100 years of data = 18K-36K rows)
• Enable flexible time-based analysis without complex SQL logic

Rule 10: Maintain Referential Integrity

• Ensure every foreign key in fact tables has a corresponding primary key in dimension tables
• Use NULL for missing or unknown dimensions (or create a "Missing" row)
• Validate referential integrity during ETL/ELT processes

---

Design Patterns

Pattern 1: Degenerate Dimensions

• Store transaction-level descriptors directly in the fact table
• Don't create separate dimension tables for low-value descriptors
• Examples: Order numbers, invoice numbers, transaction IDs, receipt numbers
• Saves dimension table overhead while preserving detail

Pattern 2: Junk Dimensions

• Combine multiple low-cardinality flags and indicators into a single dimension
• Reduces fact table width and improves performance
• Example: A single OrderStatus dimension with combinations of:
  • is_paid, is_shipped, is_returned, is_expedited
• Pre-generate all valid combinations in the junk dimension

Pattern 3: Factless Fact Tables

• Create fact tables with no measures - only foreign keys
• Use for tracking events or relationships
• Examples:
  • Student enrollments in courses
  • Course prerequisites
  • Doctor-patient visits (when only the event matters, not metrics)
• One row = one event occurrence

Pattern 4: Conformed Facts

• Use identical measure definitions across fact tables
• Example: All fact tables use the same Revenue definition
• Enables reliable cross-fact analysis
• Document measure definitions in a business glossary

Pattern 5: Role-Playing Dimensions

• Use the same physical dimension table in multiple roles with different foreign keys
• Saves dimension maintenance while enabling different perspectives
• Example: Date dimension used as:
  • OrderDateKey
  • ShipDateKey
  • DeliveryDateKey
  • ReturnDateKey

---

Implementation Guidelines

Guideline 1: Incremental Loading Strategy

• Design for efficient batch updates, not full refreshes
• Use surrogate keys to identify changed records
• Implement SCD logic to track updates
• Archive historical versions for auditability
• Support late-arriving facts and dimensions

Guideline 2: Additive Measures

• Ensure all measures can be summed across any dimension
• Semi-additive measures: Can sum across some dimensions but not others
  • Example: Account balance (can sum by account, not by date)
• Non-additive measures: Cannot be summed at all
  • Example: Ratios, percentages, counts of distinct values
  • Store in dimensions or calculate on-the-fly

Guideline 3: Namespace Foreign Keys Clearly

• Use naming convention: [DimensionName]Key
• Examples: CustomerKey, ProductKey, DateKey, LocationKey
• Differentiate from business keys with suffixes like _SK (surrogate key) or _ID (business ID)

Guideline 4: Document the Data Warehouse

• Maintain a data dictionary for all tables and columns
• Document grain, additivity, and calculation logic for each fact table
• Document SCD type and change tracking logic for each dimension
• Create a dimensional model diagram (star schema visualization)
• Version control documentation alongside schema changes

Guideline 5: Version History Tracking

• For Type 2 SCD dimensions, include:
  • RowEffectiveDate / RowStartDate
  • RowEndDate / RowExpirationDate
  • RowIsCurrent (boolean flag)
  • RowChangeReason (what triggered the change)

Guideline 6: Handle Unknowns and Nulls

• Create a special "Unknown" or "Not Applicable" row in each dimension
• Use a reserved surrogate key (e.g., 0 or -1) for unknowns
• Avoid NULL foreign keys in fact tables for better query performance

Guideline 7: Promote Dimension-Only Attributes to Dimensions

• If a useful filtering/grouping attribute exists, create a dimension for it
• Don't store high-cardinality descriptive data in fact tables
• Example: Create a Product dimension instead of storing product description in every sales fact row

---

Common Pitfalls to Avoid

1. Mixing Grains: Don't combine different event types or detail levels in one fact table
2. Over-Normalizing Dimensions: Avoid creating normalized dimension hierarchies (defeats the purpose)
3. Ignoring Change Tracking: Don't ignore how dimensions change; plan SCD strategy upfront
4. Using Natural Keys: Never use business keys as primary keys in dimension or fact tables
5. Neglecting Conformed Dimensions: Each new fact table should reuse existing dimensions, not create new ones
6. Storing Pre-Calculated Aggregates in Facts: Use aggregate tables separately, not in transaction-level fact tables
7. Creating Overly Wide Dimensions: Keep dimensions focused; don't include everything tangentially related
8. Forgetting About Late-Arriving Data: Design processes to handle facts arriving after dimension changes

---

Checklist for Dimensional Model Validation

• [ ] Fact tables and dimension tables clearly identified
• [ ] All dimensions denormalized (no 3NF style normalization)
• [ ] Surrogate keys generated for all dimensions
• [ ] Surrogate keys used in fact tables (not natural keys)
• [ ] Conformed dimensions shared across fact tables where applicable
• [ ] Star schema clearly visible (fact in center, dimensions radiating out)
• [ ] Grain defined and consistent for each fact table
• [ ] Date dimension exists and pre-calculated attributes included
• [ ] SCD strategy defined for each dimension
• [ ] Referential integrity enforced
• [ ] All measures additive (or semi/non-additive properly documented)
• [ ] Foreign key naming convention applied consistently
• [ ] Unknown/NULL handling documented
• [ ] Documentation complete and accessible

---

Quick Reference: Design Decisions

Question | Kimball Answer
-- | —
Should I normalize dimensions? | No - denormalize for query performance
What keys should I use? | Surrogate keys (generated integers) as PK/FK
How do I handle dimension changes? | Use Slowly Changing Dimension (SCD) Type 1, 2, or 3
One big fact table or many small ones? | Multiple fact tables at different grains, all conformed
Do I need a date dimension? | Yes, always - with pre-calculated attributes
Can I store raw source data here? | No - cleanse, validate, and restructure during ETL/ELT
How do I link to other fact tables? | Through conformed dimensions, not directly
Should I store aggregates in fact tables? | No - keep transaction-level facts separate from aggregates

---

References & Further Learning

• Key Principles: Prioritize ease of use, query performance, and business understanding
• Design for Reporting: Think like a business analyst - what questions need answering?
• Iterate: Start simple, add complexity only when needed
• Test: Validate that reports can be built easily from your model