Dimensional Modeling

Foundation of business intelligence and analytics

What is Dimensional Modeling?

Dimensional Modeling is a data modeling technique optimized for data retrieval in data warehouses and business intelligence systems. Pioneered by Ralph Kimball, it structures data into fact tables (containing measurable events) and dimension tables (containing descriptive context), making complex business data intuitive and performant for analysis.

Unlike normalized operational databases designed for transactional efficiency, dimensional models prioritize query performance and user understanding. They organize data around business processes, making it natural for business users to explore and analyze data without deep technical knowledge.

Core Principle:

Structure data the way business people think about it - around measurable events (facts) with rich descriptive context (dimensions) that answer who, what, when, where, why, and how questions.

Facts vs Dimensions

Fact Tables

Measurable business events

Fact tables store quantitative data about business transactions or events. They contain foreign keys to dimension tables and numeric measures that can be aggregated.

Fact_Sales Example:
Keys:
• date_key (FK)
• product_key (FK)
• customer_key (FK)
• store_key (FK)
Measures:
• quantity_sold
• sales_amount
• discount_amount
• profit_amount

Characteristics:

  • •Contain numeric measures
  • •Grain defines level of detail
  • •Foreign keys to dimensions
  • •Often very large tables
Dimension Tables

Descriptive context

Dimension tables provide descriptive attributes that give context to facts. They answer the "who, what, where, when, why" questions about business events.

Dim_Product Example:
Key:
• product_key (PK)
Attributes:
• product_name
• product_description
• category
• subcategory
• brand
• size
• color
• list_price

Characteristics:

  • •Mostly text attributes
  • •Denormalized structure
  • •Relatively small tables
  • •Source of query constraints

Types of Fact Tables

Transaction Facts

One row per business transaction or event. Most detailed level of granularity.

Example: Sales Transactions

Each individual sale creates one row with date, product, customer, store, and measures.

  • •Highest detail level
  • •Large volume of rows
  • •Most flexible for analysis
Periodic Snapshot Facts

Captures state at regular intervals. One row per period per entity.

Example: Account Balances

Daily snapshot of every customer account balance, even if no transactions occurred.

  • •Regular time intervals
  • •Predictable row count
  • •Great for trend analysis
Accumulating Snapshot Facts

Tracks process with definite beginning and end. Row updated as process progresses.

Example: Order Fulfillment

One row per order with dates for ordered, shipped, delivered stages.

  • •Multiple date columns
  • •Row gets updated
  • •Tracks process duration

Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions (SCD) are techniques for tracking changes to dimension attributes over time. Different types balance historical accuracy against complexity and storage.

Type 1: Overwrite

Simply overwrite old value with new value. No history preserved.

Before:

customer_id: 123
city: "Boston"

After:

customer_id: 123
city: "New York"

Use when: History doesn't matter (corrections, no business value in tracking changes)

Type 2: Add New Row

Create new row with new surrogate key. Track effective dates and current flag.

Key
Cust_ID
City
Start_Date
End_Date
Current
1
123
Boston
2020-01-01
2023-05-15
N
2
123
New York
2023-05-16
9999-12-31
Y

Use when: Need complete history (most common approach for important attributes)

Type 3: Add New Column

Keep current and previous value in separate columns. Limited history.

customer_id: 123
current_city: "New York"
previous_city: "Boston"
city_change_date: 2023-05-16

Use when: Only need to track one previous value (rare in practice)

Best Practices

Define Clear Grain

The grain is the most atomic level of detail captured in a fact table. Clearly define what each row represents before designing tables.

Good Grain Statements:

  • • "One row per product per day in each store"
  • • "One row per individual sales transaction"
  • • "One row per customer per month for each account"

Mixing grains in a single fact table leads to confusion and incorrect aggregations.

Use Surrogate Keys

Use system-generated integers as primary keys in dimension tables rather than natural business keys. Provides flexibility for slowly changing dimensions and insulates from source system changes.

product_key: 12345 (surrogate)
product_code: "SKU-ABC-123" (natural)

Surrogate keys are smaller, faster to join, and support Type 2 SCDs naturally.

Denormalize Dimensions

Include all hierarchical levels in a single dimension table even if it creates redundancy. Trading storage for query simplicity and performance.

Product Dimension includes:

product_name, subcategory, category,
department, division

Users can easily filter and group by any level without joins.

Conformed Dimensions

Share identical dimensions across multiple fact tables. Enables drill-across queries and consistent reporting across business processes.

Example:

Same Dim_Date used by Fact_Sales, Fact_Inventory, and Fact_Shipments ensures consistent time analysis across all areas.

Critical for enterprise-wide consistency and integrated reporting.

Why Dimensional Modeling?

Business Benefits

  • ✓Intuitive Structure: Mirrors how business users think about data
  • ✓Self-Service Analytics: Business users can explore data independently
  • ✓Consistent Reporting: Conformed dimensions ensure enterprise-wide consistency
  • ✓Historical Tracking: SCD techniques preserve history for trend analysis

Technical Benefits

  • ✓Query Performance: Denormalized structure optimized for reads
  • ✓BI Tool Friendly: All major BI tools designed for dimensional models
  • ✓Predictable Queries: Star joins are straightforward to optimize
  • ✓Extensible: Easy to add new facts and dimensions as needs evolve