Guide

Dimensional modeling in practice

Facts, dimensions, grain, keys and history, shown with one example from a Norwegian online shop, and the mistakes that most often give wrong numbers.

By Steffen Sande10 min read

An operational database is built to record events: a customer places an order, a warehouse takes an item off the shelf. An analytical model has to answer questions about those same events: how much did we sell in Bergen in March, and which product categories are growing? Dimensional modeling is a way of building the analytical model so that questions like these are easy to ask and hard to answer wrongly.

The terms in this article come from Ralph Kimball, who described the method in the book The Data Warehouse Toolkit. One example runs through the whole article: a Norwegian online shop that wants to analyse its sales.

Facts and dimensions

A dimensional model is made of two kinds of tables.

  • Fact tables hold measurable events. Each row is something that happened, with the numbers that describe it: quantity, amount, discount.
  • Dimension tables hold the context the events are described with: who bought, what was bought, and when. The columns in the dimensions are what people filter and group by: county, product category, month.

With the fact table in the middle and the dimensions around it, the pattern is called a star schema. For the online shop, that is fact_order_line in the middle, with dim_customer, dim_product and dim_date around it. The star schema usually sits in the gold layer, built on the shared data model in the silver layer.

A useful rule of thumb: numbers you want to add up belong in the fact table. Text and categories you want to filter on belong in a dimension.

Decide the grain first

Before you choose a single column, you have to decide what one row in the fact table means. This is called the grain. For the online shop, the natural choice is:

One row per order line, that is, one product in one order.

That choice drives everything else. With one row per order line, each row can point to one product, and you can calculate revenue per product. Had you chosen one row per order, the product would have no place in the fact table, because an order can contain many products.

It usually pays to choose the finest grain the source data has. From order lines you can always add up to orders, days or months. From monthly totals you can never get back to the individual lines.

Write the grain down in the table’s description. It is the first thing a new analyst needs to know.

The example: a star for order lines

Here is the fact table. It holds keys to the dimensions, a couple of columns that identify the order, and the measures.

fact_order_line: one row per order line.

Column Type Description
customer_key bigint Surrogate key to dim_customer, the version of the customer that applied when the order was placed.
product_key bigint Surrogate key to dim_product.
date_key integer Key to dim_date for the order date, in the form YYYYMMDD.
order_no varchar The order number from the online shop. Not a dimension of its own, but useful for finding the order again.
line_no integer The line’s number within the order. Together with order_no it identifies the row.
quantity integer Number of units of the product on the line.
unit_price numeric(12,2) Price per unit in NOK, excluding VAT, before discount.
discount numeric(12,2) Total discount on the line in NOK, excluding VAT.
amount numeric(12,2) quantity × unit_price − discount, in NOK excluding VAT.

Notice that the description of amount says both how it is calculated and whether VAT is included. That is the kind of decision that otherwise gets made again in every report. More on what descriptions should contain is in From business term to column.

order_no sits directly in the fact table without a dimension of its own. This is called a degenerate dimension: an identifier that is useful to filter on, but has no attributes of its own to describe.

dim_customer: one row per version of a customer.

Column Type Description
customer_key bigint Surrogate key, created in the data platform. A new value for each version of the customer.
customer_id varchar The customer number from the online shop (the natural key).
name varchar The customer’s name.
postcode char(4) The postcode of the delivery address.
municipality varchar The municipality the postcode belongs to.
county varchar The county the municipality belongs to.
valid_from date The first day this version applied.
valid_to date The last day this version applied. 9999-12-31 for the current version.
is_current boolean True for the version that applies now.

dim_product: one row per product.

Column Type Description
product_key bigint Surrogate key, created in the data platform.
product_id varchar The item number from the online shop.
name varchar The product name as shown in the shop.
category varchar Main category, for example “Kitchen”.
subcategory varchar Subcategory, for example “Coffee makers”.
brand varchar The manufacturer’s brand name.

dim_date: one row per calendar day.

Column Type Description
date_key integer The date in the form YYYYMMDD, for example 20261021.
date date The date itself.
weekday varchar The day of the week, for example “Wednesday”.
week integer Week number according to ISO 8601.
month integer Month number, 1–12.
quarter integer Quarter, 1–4.
year integer The year.
is_holiday boolean True for Norwegian public holidays.

With these four tables, an analyst can answer “revenue per county and quarter for the Kitchen category” with a single query: the fact table joined to three dimensions, filtered and grouped on columns in the dimensions, and summed over amount.

Natural keys and surrogate keys

The source system already has keys: customer number, item number, order number. These are natural keys. The dimensions keep them, in customer_id and product_id, so that a row can be traced back to its source.

Even so, the fact table points to the dimensions with surrogate keys, numbers the data platform creates itself. There are three reasons for this:

  • History. If a customer has several versions in dim_customer, customer_id is no longer unique. The surrogate key tells the versions apart, and the fact row points to exactly the version that applied when the order was placed.
  • Independence from the source. If the online shop changes systems and the customer numbers change, or two sources have overlapping numbers, only the dimension is affected, not every row in the fact table.
  • Unknown values. An order line without a known customer can point to a dedicated “unknown customer” row in dim_customer, instead of having an empty key that disappears in an ordinary join.

The date dimension is a common exception: there the date itself is often used as the key, because a calendar day never changes.

History: when the customer moves

Customer 10427 lives in Bergen and has shopped there for two years. In August the customer moves to Trondheim and updates the address in the online shop. What should happen to dim_customer? That is the question SCD, slowly changing dimensions, is about. The three most common types answer it differently.

Type 1 overwrites. The row is updated with the new address. It is simple, but all history is lost: every order from Bergen now looks as if it came from Trondheim, and last year’s revenue per county changes after the fact. Type 1 suits corrections, such as a misspelt name, where the old value was never right.

Type 2 adds a new row. The old row gets valid_to set to the day before the move and is_current set to false. A new row with a new surrogate key gets the new address:

customer_key customer_id municipality valid_from valid_to is_current
5810 10427 Bergen 2024-06-03 2026-08-14 false
9132 10427 Trondheim 2026-08-15 9999-12-31 true

Orders from before the move point to 5810, the new ones to 9132. Last year’s revenue for Bergen stays put, and you can still count the customer once by grouping on customer_id. That is why the surrogate key is needed.

Type 3 keeps the previous value in a column of its own. dim_customer gets a previous_municipality column next to municipality. That lets you compare “before” and “after” for one change, but no more: if the customer moves again, Bergen is gone. Type 3 is mostly used where a change happens to many rows at once, for example when a sales region is split up.

You choose the type per column, not per table. In dim_customer, the address can be type 2 while the name is type 1. Write the choice into the column’s description.

Common mistakes

Mixed grain in one fact table. Some put the shipping cost, which applies to the whole order, into fact_order_line. It then appears on every line, and total shipping comes out as many times too large as the order has lines. Order-level measures belong in a separate fact table with one row per order, or must be allocated to the lines by a rule stated in the description.

Facts stored in dimensions. A total_revenue column in dim_customer looks handy, but it is an aggregate that is only correct at the moment it was calculated, and it cannot be filtered by period. Numbers that are meant to be added up belong in the fact table.

No date dimension. Without dim_date, every report has to work out week, quarter and holidays from a date, and different reports do it differently. Week numbers around New Year are a well-known example. A date dimension does it once.

Double counting when two fact tables are joined. The online shop also has fact_return, with one row per returned item. If you join fact_order_line and fact_return directly on customer, each order line is repeated for every return the customer has made, and both totals come out too large. Add up each fact table on its own at the shared grain, for example customer and month, and join the results afterwards. This works because both use the same dimensions.

Needless snowflaking. Snowflaking means splitting a dimension into several normalised tables, for example dim_product → dim_subcategory → dim_category. It saves a little storage, but every query needs more joins, and the model gets harder to read. In most cases it is simpler to keep category and subcategory as columns in dim_product, as the example above does.

Where to start

You do not have to model the whole business at once. Kimball describes four steps for each star:

  1. Choose the business process. Pick a process people are already asking questions about; for the online shop, sales.
  2. Declare the grain. Write it down: one row per order line.
  3. Identify the dimensions. Choose the few dimensions the questions actually use, typically who, what and when. For each column in the dimensions, decide whether changes should be overwritten or kept.
  4. Identify the facts. Include the measures that belong to the grain: quantity, unit price, discount and amount.

Once the first star is in use, the next process, for example returns, can reuse dim_customer, dim_product and dim_date as shared (conformed) dimensions. That is how the model grows, one fact table at a time, with dimensions that mean the same thing everywhere. If you describe the tables with an owner, a meaning and quality requirements, you are also on your way to a data contract for them.

Sources