Dimensional modelling¶

A way to shape data for analysis as facts, the measurable events of the business, surrounded by dimensions, the context used to filter and group them.

Before reading

Medallion layers: bronze, silver and gold

Why it matters¶

Clean tables describe entities as the source systems see them. Analysts ask different questions: revenue by product category and month, orders by customer region. A dimensional model answers them with simple, predictable joins, gives every metric one table to come from, and lets business users and tools understand the data without knowing the sources.

How it works¶

A fact table records events or measurements. Each row is one occurrence, such as one line of one order, with numeric measures such as quantity and amount, and keys pointing to dimensions. Fact tables are long and narrow and grow constantly.

A dimension table describes the things involved in the events: products, customers, dates, stores. Each row is one product or one customer, with descriptive attributes such as name, category, region and segment. Dimensions are wide and comparatively short.

The grain is the most important decision: exactly what one row of the fact table represents. "One row per order line" is a grain; "orders" is not. Every measure and every dimension key must be true at that grain. Mixing grains, such as putting the order's shipping fee on each order line, makes sums wrong: the fee is counted once per line.

A fact table with its dimensions around it is a star schema. Splitting dimensions into further normalised tables, such as product to category, is a snowflake schema. Queries join the fact to the dimensions they need, filter on dimension attributes and aggregate measures.

flowchart TB
  F[(fct_order_line<br/>one row per order line:<br/>quantity, amount)]
  DP[(dim_product:<br/>name, category, brand)] --- F
  DC[(dim_customer:<br/>segment, region)] --- F
  DD[(dim_date:<br/>date_day, month, weekday)] --- F

Dimensions are joined by a key. Many models give dimensions a surrogate key, a number or hash created in the warehouse, rather than reusing the source ID. That keeps the model stable when sources change and makes it possible to keep several versions of one product, which History and slowly changing dimensions builds on.

Conformed dimensions are shared. dim_product means the same thing whether it is joined to order lines, returns or stock levels, so results from different fact tables can be compared and combined. Building them once and reusing them is what keeps a set of fact tables consistent.

Measures differ in how they can be added up. Additive measures, such as amount, can be summed over any dimension. Semi-additive measures, such as stock level, can be summed across products but not across days. Non-additive measures, such as a percentage, must be recomputed from their parts.

Wide tables are a common alternative: one denormalised table with the fact and the dimension attributes already joined in, such as order lines with product category and customer region as columns. Columnar storage makes them cheap to scan, and BI tools find them easy. They work well as a final layer for one use, built from a star schema, but as the only model they duplicate attributes and must all be rebuilt when one attribute changes.

Key terms¶

Fact table: rows of events or measurements with numeric measures and dimension keys.
Dimension table: descriptive context for facts, one row per member.
Grain: what exactly one fact row represents.
Star schema: a fact table joined directly to its dimensions.
Surrogate key: a key created in the warehouse to identify a dimension row.
Conformed dimension: a dimension shared, with the same meaning, by several facts.
Additive, semi-additive, non-additive: how a measure can be summed.

Example¶

CREATE TABLE shop.fct_order_line (
  order_line_id  varchar,     -- grain: one row per order line
  order_id       varchar,
  product_key    bigint,      -- to dim_product
  customer_key   bigint,      -- to dim_customer
  order_date     date,        -- to dim_date
  order_status   varchar,     -- status of the whole order, kept on each line
  quantity       integer,
  amount         decimal(12, 2)
);

CREATE TABLE shop.dim_product (
  product_key    bigint,
  product_id     varchar,     -- source ID, kept for tracing
  name           varchar,
  category       varchar,
  brand          varchar
);

-- revenue by category and month
SELECT p.category, date_trunc('month', f.order_date) AS month, sum(f.amount)
FROM shop.fct_order_line AS f
JOIN shop.dim_product AS p ON f.product_key = p.product_key
GROUP BY 1, 2;

The order-level shipping fee does not belong in fct_order_line. It goes in a separate fact at order grain, or is split across lines by an agreed rule.

Pitfalls¶

An undeclared grain. Measures at different grains end up in one table and sums are wrong.
Dimensions per report. Two versions of dim_customer give two answers to the same question.
Snowflaking everything. Normalising dimensions into many small tables adds joins with little benefit in columnar storage.
Summing semi-additive measures over time. Stock levels summed across days are meaningless.
Only wide tables. Every attribute change means rebuilding every wide table that copies it.

Learn more¶

Kimball Group, dimensional modeling techniques: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
Ralph Kimball and Margy Ross, The Data Warehouse Toolkit, 3rd edition (Wiley, 2013).

History and slowly changing dimensions · Multi-source conformance · Semantic layer · Naming conventions