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.

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.

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

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

AI assistants need the same clarity: see The semantic layer is back.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 30 September 2026. About me