Dimensional Modeling
Intermediate 16 min read Dimensional Modeling

Dimensional Modeling Overview

Facts, dimensions and the star that connects them

Dimensional modeling structures analytical data into facts (measures about events) and dimensions (context you slice by). It is the classic, proven shape for BI because it is intuitive, fast and flexible.

dimensional modelingfactsdimensionsKimballstar schemaBI schema
Prerequisites: Warehouse Grain OLTP vs OLAP
Before we start

What you will learn

  • Distinguish facts from dimensions by their role in a report
  • Explain why dimensional shapes are fast for BI queries
  • Recognize when a raw operational model won't serve analysis
Introduction

What is this?

Dimensional modeling organizes a warehouse into facts — numeric measures about business events (one row per order, per sale) — and dimensions — the descriptive context you filter and group by (customer, date, product, region). Facts join dimensions through key-based relationships, typically in a star shape. It trades normalization for readability and speed, matching exactly how analysts think.

Motivation

Why do you need it?

  • Users reason in terms of 'sales by product by month' — dimensions ARE the slicing vocabulary.
  • Star-shaped joins are cheap and explainable; dashboards read one fact plus a few dimensions directly.
  • Modeling facts at atomic grain keeps every aggregation derivable without guessing.
  • It is the default way BI tools and semantic layers are designed, so it integrates everywhere.
Think about it

Mental model

Imagine

A photo's subject and the scene around it

Think of

Facts and dimensions

The subject (a sale — the fact) is the measurement; the scene (who, when, where — the dimensions) gives the context that makes the measure meaningful. 'Sales of 5' means nothing; 'sales of 5, in March, to first-time customers in Spain' is a decision.

Mechanics

How it works

  • A fact table holds the numeric measures and foreign keys to dimensions, at a declared grain.
  • Dimension tables hold the descriptive attributes for one entity (customer, product, date).
  • Surrogate keys (integer ids) join facts to dimensions; dimensions are denormalized for reading.
  • Aggregations group by dimension attributes and sum/avg fact measures.
  • The star schema is a fact hub with dimension spokes — the shape that makes SQL simple.

The star: one fact, many dimensions.

Fact table
dim_customer
dim_date
dim_product
Group queries
Hands-on

Examples

The shape of a dimensional read

The shape of a dimensional read
SELECT d.month, c.region, SUM(f.sales_amount)
FROM fact_sales f
JOIN dim_customer c ON f.cust_key  = c.cust_key
JOIN dim_date     d ON f.date_key  = d.date_key
WHERE d.month BETWEEN '2026-01' AND '2026-03'
GROUP BY d.month, c.region;

One fact, two dims, grouped by time and region. This is the workhorse query dimensional models are built to power.

Real world

In production

  • A retail fact_sales table (grain: one transaction line) joins dim_date, dim_store, dim_product; daily dashboards group by any slicer.
  • A fintech models fact_account_activity with dim_customer, dim_branch, dim_risk; regulatory reports read the same star with different filters.
  • A subscription facts set (new, churned, retained) joins shared dim_customer and dim_date to drive the exec scorecard.
Guidelines

Best practices

  • Start facts at atomic grain; aggregate to every coarser grain you need.
  • Keep dimension attributes few but useful: attributes = the slicing vocabulary.
  • Use surrogate keys everywhere; business keys (customer code) are volatile.
  • Document grain and add uniqueness tests on (fact key, dim keys).
Watch out

Common mistakes

  • Building the dashboard from normalized source tables and re-deriving joins each time.
  • Putting customer attributes in the fact table slice-of-time — that's a dimension's job.
  • Over-modeling: 200 columns of unused attributes that slow everyone down.
  • Skipping surrogate keys, so a renamed customer code breaks lineage.
Root cause analysis

Common issues

Error
Report pasted over two regions double-counts shared customers
Root cause

Customers appear in multiple region dims (row-level duplicates)

Fix

Make each customer one row with one region; use roles for multiple perspectives.

Error
Renamed product code broke a year of history
Root cause

Business key used as join key instead of surrogate

Fix

Add immutable surrogate keys and map business codes through a versioned dim.

When things break

Debugging tips

  • If a group-by returns wrong totals, check fact grain first, then dimension key uniqueness.
  • If dims use 'code' values with drift, migrate to surrogate keys and version the business key mapping.
  • Validate one slice by hand: sum a small region's amounts and compare to source.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build Your First Star Schema
40 min · intermediate
In progress
Objectives
  • Split operational data into a fact and two dimensions
  • Assign surrogate keys and join a star
  • Answer three business questions from one star

Build Your First Star Schema

40 min · intermediate

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which table holds 'month', 'region' and 'category'?

2

Dimensional modeling aims to fully normalize every table like a transactional schema.

Go deeper

Official documentation

Kimball Group — dimensional modeling techniques
docs