Star schema and dimensional modelling: a practical introduction

A good data model is the difference between a dashboard that is fast and trusted, and one that is slow, confusing and quietly wrong. The star schema, from Ralph Kimball's dimensional modelling approach, remains the most widely used design for analytics, and it is what Power BI's engine is optimised for.

Facts and dimensions

A star schema has one central fact table surrounded by dimension tables.

• Fact tables record events or measurements: sales, orders, website sessions, stock movements. They are long and narrow, full of numbers and foreign keys.
• Dimension tables describe the context: who, what, where and when. Customers, products, stores and dates. They are short and wide, full of descriptive attributes you filter and group by.

Grain: the decision that matters most

The grain is what one row in a fact table represents. Decide it first and write it down: "one row per order line", "one row per product per store per day". Every number in the table must be true at that grain. Most double-counting bugs come from mixing grains, for example storing an order-level delivery charge on every order line.

Choose the lowest useful grain. You can always aggregate order lines into orders, but you can never split an order back into lines.

Building the tables

-- Dimension: one row per product, with a surrogate key
CREATE TABLE dim_product AS
SELECT
    ROW_NUMBER() OVER (ORDER BY product_code) AS product_key,
    product_code,
    product_name,
    category,
    brand
FROM staging.products;

-- Fact: one row per order line, with keys and numeric measures only
CREATE TABLE fct_sales AS
SELECT
    ol.order_id,
    ol.line_number,
    d.date_key,
    p.product_key,
    c.customer_key,
    ol.quantity,
    ol.unit_price,
    ol.quantity * ol.unit_price AS gross_amount
FROM staging.order_lines ol
JOIN dim_date     d ON d.date = ol.order_date
JOIN dim_product  p ON p.product_code = ol.product_code
JOIN dim_customer c ON c.customer_id = ol.customer_id;

A surrogate key such as product_key is a meaningless integer that belongs to the warehouse, not to the source system. It protects you when source IDs change, collide between systems, or need history tracking.

Always build a date dimension

A date table with one row per day unlocks time intelligence in Power BI and makes "sales by week, month, quarter or financial year" trivial.

-- Snowflake: generate ten years of dates
CREATE OR REPLACE TABLE dim_date AS
SELECT
    TO_NUMBER(TO_CHAR(d, 'YYYYMMDD')) AS date_key,
    d                                  AS date,
    YEAR(d)                            AS year,
    QUARTER(d)                         AS quarter,
    MONTH(d)                           AS month,
    MONTHNAME(d)                       AS month_name,
    DAYOFWEEKISO(d)                    AS day_of_week,
    IFF(DAYOFWEEKISO(d) IN (6, 7), TRUE, FALSE) AS is_weekend
FROM (
    SELECT DATEADD(day, SEQ4(), '2020-01-01'::DATE) AS d
    FROM TABLE(GENERATOR(ROWCOUNT => 3653))
);

Slowly changing dimensions

Customers move, products change category. How you handle that is called a slowly changing dimension (SCD) strategy.

In dbt, snapshots implement Type 2 for you, which is covered in the dbt guide.

Why Power BI loves star schemas

• Filters flow cleanly from one dimension to the fact table, so measures behave predictably.
• The VertiPaq engine compresses narrow fact tables extremely well, keeping reports fast.
• DAX becomes simpler: CALCULATE([Sales], dim_product[category] = "Coffee") just works.
• Business users see friendly tables named after things they understand.

Avoid the temptation to build one giant flat table. It feels simpler at first but grows slowly, duplicates attributes and makes time intelligence awkward.

A checklist for your next model

• Write the grain of every fact table in one sentence.
• Keep facts numeric and dimensions descriptive.
• Give every dimension a surrogate key and an "unknown" row for missing matches.
• Build a proper date dimension.
• Test uniqueness of keys and relationships between tables, as described in the data quality guide.

Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.