Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 56 ยท Data Modelling

Star Schema: Facts and Dimensions

Learn why Power BI models should be star schemas: fact and dimension tables, grain, snowflaking, and how a flat table compares with a star.

Power Query gets your data clean. The next question decides whether your reports will be fast, correct and easy to extend: how are the tables arranged? Beginners often load one huge table with every column they can find. It works for the first chart, then slows down, then produces strange totals when a second table arrives.

The arrangement that Power BI is designed for is the star schema: a central table of events surrounded by small descriptive tables. In this lesson you will learn what fact and dimension tables are, how to decide the grain of a fact table, what a snowflake is, why a flat table causes trouble, and how to turn one into the other. Relationships between the tables come in Relationships, Cardinality and Filter Direction.

Model view in Power BI Desktop is a point-and-click tool and cannot run on this website, so the screens are described in words. The runnable examples are Python stand-ins built with pandas. They reproduce what a star schema does with the data, but the real engine (VertiPaq) is not involved.

Facts and dimensions

A fact table records things that happened: a sale, a click, a shipment, a payment. Each row is one event. It contains numbers you will add up (quantity, amount) and keys that say which product, customer and date the event involved.

A dimension table describes the things you analyse by: products, customers, dates, stores. Each row is one product, one customer, one day. It holds descriptive columns that you will group, filter and slice by (category, city, month name).

Two kinds of table

Fact One row per event, with numbers and keys.
Dimension One row per thing, with descriptions.

Facts are long and narrow. Dimensions are short and wide.

1 / 2

A rule of thumb: if you would put it on a chart's axis, legend or slicer, it belongs in a dimension. If you would put it in the values well and add it up, it belongs in a fact table.

flowchart TD
    D["DimDate<br/>one row per day"] --> F["FactSales<br/>one row per order line"]
    P["DimProduct<br/>one row per product"] --> F
    C["DimCustomer<br/>one row per customer"] --> F
    S["DimStore<br/>one row per store"] --> F

Four dimensions point at one fact table; this is the star.

Grain: what does one row mean?

The grain (or granularity) of a fact table is the exact meaning of one row. Decide it first and write it down in a sentence: "one row per order line", "one row per customer per day", "one row per month per department".

Why it matters:

  • Every column in the table must be true at that grain. An order line can have a quantity, but the order's shipping cost belongs to the whole order, not to one line. If you repeat it on each line, summing it will overcount.
  • Mixing grains in one table is the most common source of wrong totals. A budget by month and sales by day are different grains: they belong in two fact tables.
  • A finer grain (order line) can always be summarised to a coarser one (month). The reverse is impossible.

Choose the finest grain your business questions and your data size allow.

Dimension design

Good dimensions share a few traits:

  • A unique key. Each row has one key value (ProductID) that appears only once. The relationship needs this.
  • Descriptive columns, denormalised. Put category and subcategory in the same product table, even though that repeats the category text on many rows. Dimensions are small, so repetition costs little.
  • A key in the fact table. The fact table stores only the key (ProductID), not the product's name or category.
  • Friendly names. Dimension columns appear in the field list, so name them like labels: Product Category, not prod_cat_cd.
  • A real date dimension. Dates deserve their own table, covered in Date Tables and Time Intelligence Setup.

Number fields that you sum belong in facts. Sometimes a number is really a category, for example a rating from 1 to 5 that you will group by. Think about how it will be used.

Snowflake: normalised dimensions

A snowflake schema splits dimensions into further tables: DimProduct links to DimSubcategory, which links to DimCategory. It saves a bit of storage and mirrors a normalised database, but in Power BI it usually hurts more than it helps:

  • More tables and relationships make the model harder to read and slower to filter.
  • Users must know which of three tables holds the column they want.
  • Chained filters can behave in surprising ways.

The usual advice is to flatten snowflakes back into stars, by merging the lookup tables into one dimension in Power Query (see Merge and Append Queries). There are exceptions: a very large dimension that shares an attribute table, or a source you cannot reshape, may justify a snowflake. Start with the star.

Why not one flat table?

A single wide table, with the order, product, customer and date columns all together, is easy to build but costly:

  • Repeated text. A product name is stored on every order line. The compressed model grows and the refresh slows down.
  • Wrong or hard-to-write measures. Counting customers means counting distinct values in a column that repeats; counting products that had no sales is impossible, because those products have no rows.
  • Several fact tables do not mix. If you add a budget table or a returns table, a flat table has no shared place for the common dimensions (a date table that both tables use).
  • Messy field list. Users see hundreds of columns without structure.

In a star schema, the product list exists on its own, so "products with no sales" is a question with an answer, and a second fact table simply connects to the same dimensions.

Stand-in 1: from a flat table to a star

This stand-in splits a flat export into one fact and two dimensions, then rebuilds the original view by joining them. It shows that the star loses no information and shrinks the repeated text.

Python Stand-in: split a flat table into a star

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In Power Query you would do the same with Reference queries, Remove Duplicates and a key column. Notice that the fact table now holds only keys and numbers, and each product's text exists once.

Stand-in 2: what a star makes possible

The second stand-in shows two things that are easy in a star and awkward in a flat table: grouping by any dimension attribute, and finding products with no sales.

Python Stand-in: slice by a dimension and find unsold products

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A flat table has no row at all for the ruler, because it was never sold. The dimension keeps it, which is how a Power BI visual can show a product with a blank value.

Building a star in Power BI

A practical recipe, using what you already know:

  1. Load your source tables. In Power Query, give each a clear name with a prefix (Fact or Dim) or a naming convention you will remember.
  2. If the source is flat, Reference the query once for each dimension, keep only the dimension's columns and Remove Duplicates on the key.
  3. In the fact query, keep keys and numeric columns. Remove descriptive text.
  4. Load, then open Model view and check that relationships run from each dimension (the "one" side) to the fact (the "many" side).
  5. Hide the key columns in the fact table so report builders use the dimension columns instead.

If keys are not clean in the source, create a surrogate key (a simple integer) in the dimension, as the stand-in does, and attach it to the fact table with a merge.

Whenever a number looks wrong in a report, ask two questions first: "what is the grain of this table?" and "does this column belong at that grain?" Most wrong totals come from a column repeated at a finer grain than its meaning.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which columns belong in a fact table?

    A fact table holds one row per event, with keys that point to the dimensions and numbers to aggregate. Descriptive text lives in the dimensions.
  2. 2. What is the grain of a fact table?

    The grain states what one row represents. Every column must be true at that grain, and different grains belong in different fact tables.
  3. 3. Why do experts usually flatten a snowflake in Power BI?

    Chained lookup tables add relationships and confusion. A single denormalised dimension is easier to use and usually performs better.
  4. 4. A manager asks for products that had no sales. Which model makes this easy?

    Products that never sold have no rows in the fact table. A separate dimension keeps them, so a visual or measure can show them with a blank value.

Summary

  • Fact tables hold events (keys plus numbers); dimension tables hold descriptions, one row per thing.
  • Decide and write down the grain first; do not mix grains in one table.
  • Dimensions are unique-keyed and denormalised; fact tables store keys, not text.
  • Prefer a star over a snowflake or a flat table: it is smaller, faster and answers more questions.
  • Build the star in Power Query with Reference, Remove Duplicates and merges.
  • The Python stand-ins mirror the structure; the real model lives in Power BI's Model view.

Next, connect the tables in Relationships, Cardinality and Filter Direction.