Power BI Wiki

Guide

Why a star schema

You will be able to build a star schema that survives time intelligence, many-to-many and role-playing dates — and to recognise the reports where a flat table is the better answer.

Why a star

Put the same sales data in one flat table and in a star, and the reports look identical. The difference is in what the DAX has to say, and in what happens when the data grows.

In a flat table, every question about a time period has to rebuild the period itself, and clear every date column the report might slice by:

Sales YTD Flat =
VAR LastDay = MAX ( SalesFlat[OrderDate] )
RETURN
    CALCULATE (
        SUM ( SalesFlat[Amount] ),
        REMOVEFILTERS ( SalesFlat[Year], SalesFlat[Month] ),
        SalesFlat[OrderDate] >= DATE ( YEAR ( LastDay ), 1, 1 ),
        SalesFlat[OrderDate] <= LastDay
    )

Forget one of those columns (add a quarter column next year) and the measure quietly returns the quarter’s total instead of the year’s.

In a star, the date table is marked as a date table and the function does the work:

Sales YTD = TOTALYTD ( SUM ( Sales[Amount] ), 'Date'[Date] )

That is the first benefit: fewer lines, and the logic lives in one place instead of in every measure.

The second is speed. In Guy in a Cube’s comparison, the flat-table version of a report took 29 milliseconds against about 7 million rows; Patrick says the star version improved performance “several times over”, with no time spent on the storage engine CPU. The refresh told a bigger story: 34 minutes 32 seconds to process the flat table, 4 minutes 41 seconds for the same 7.8 million rows as a star.

The third is the one nobody notices until it breaks: the star is where relationships are defined, and relationships are how Power BI filters. A filter on a product or a date travels through the dimension to the fact. Without that path, every measure has to carry its own filters.

The shape

Facts hold the events: one row per sale, one row per order line. Dimensions hold the things you filter by: customer, product, date, store. In a star, a dimension joins straight to the fact, once, on one key.

The shape is the point. It gives Power BI one obvious route from a filter to a number, which is what makes both the DAX and the AI readable.

Surrogate keys

A dimension key must be unique. Source systems do not always give you that.

Adam Saxton’s example is a product list where the child ID restarts for each category: products 1 to 10 under category one, then 1 to 10 again under category two. Join that to the sales table and Power BI suggests a many-to-many relationship, because the key is not unique on either side.

The fix is a surrogate key: add an index in Power Query, 1 to 30 across the rows, and carry that same key into the sales table. It is an integer the model generates, not a business fact. Hide it in the dimension; nobody needs to see it in a visual.

The same rule answers the question “why an integer and not the code?”: integer keys are smaller and faster to join. If you can do it upstream — in the data warehouse — do it there; Adam quotes Matthew Roche’s advice to transform as far upstream as possible and as far downstream as necessary.

Relationships and filter direction

Two dimensions cannot filter each other in a star. Customer does not filter Product; both filter Sales, and the fact cannot pass the filter back up. RADACAD’s pattern for the rare case where one dimension must filter another is a bridge table or a redesign of the grain, not a bidirectional relationship on a hunch.

When you need it for a single measure, CROSSFILTER changes the direction inside that measure without changing the model:

Sales With Product Filter =
CALCULATE (
    [Sales],
    CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH )
)

Use it as an exception you can explain, not as the baseline.

Many-to-many and bridge tables

A real many-to-many exists when the business itself is many-to-many: a product can sit in several campaigns, an account can have several owners over time. Power BI will let you draw a direct many-to-many relationship. It will also let you forget what it costs.

SQLBI’s 2026 benchmark measured limited relationships and many-to-many coordinates against a regular relationship: in their test the query took about 148 seconds over a limited relationship against 15 over a regular one (around ten times longer), and the share spent on the relationship itself grew from about 7 seconds to over two minutes. The cost is paid per measure, per visual, per query.

The bridge-table design makes the relationship many-to-one on both sides: a table with one row per combination, joined to the fact and to each dimension. PeryTUS builds both designs on the same data and tests one author’s revenue in each; his advice is to use a bridge table whenever you can, with single-direction filters. The bridge is more model to build and more to understand; the direct relationship is less honest about why it is slow.

Role-playing dates

A fact table often has several dates: order date, ship date, due date. One date table can serve all of them, but only one relationship can be active.

The pattern is to join the date table to one date (usually the order date), then switch relationship inside a measure:

Shipped Sales =
CALCULATE ( [Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )

Guy in a Cube’s role-playing dimensions video shows both the problem and the pattern: with one date table and two slices on the same table, the slicers fight each other; with USERELATIONSHIP, each measure uses the date it means. Label the measures so the reader knows which date they are looking at.

The alternative is a copy of the date table per role. That is more model and more refresh, but some teams find it easier to explain and to secure.

Whichever you choose, mark the date table: SQLBI’s video on the setting explains that without it, the automatic date hierarchy can take over, and time intelligence starts using a date column that is not the one you designed.

Snowflaking

A snowflake normalises a dimension: product categories in their own table, joined to the product table. Curbal explains the trade: it compresses better, because a repeated category name is stored once, and it can be faster to write; in Power BI it costs you another relationship to cross and another table for the reader to understand.

The test is whether the sub-dimension is a thing in its own right. If people filter by category and read reports about categories, it deserves a table. If the extra table only exists because the source database was normalised, flatten it in Power Query.

There is a subtler cost. PeryTUS shows a snowflaked model where a measure returning zero for a selected store turns into blank once the store type is read from a separate table, and the DAX has to be changed to force the zero back. The model was not wrong; the extra hop changed the answer. In a star the same requirement is straightforward.

When a flat table is fine

This is where the advice stops being a rule.

Microsoft Hates Greg tested flat tables against star schemas up to 100 million rows and could not find a meaningful difference in query performance — and in some of his benchmarks the star was significantly slower. File size was about 10 GB for the flat table against 9 GB for the star. On refresh he is clear the other way: the star is faster, and the difference starts to matter around 10 million rows. Below that, he says, it is so fast it does not matter; above it, he asks whether a 50-million-row table refreshing in 12 minutes instead of 6 or 7 matters to you.

That is not the same conclusion as SQLBI’s, who end their header-detail video saying that in their experience a star schema always wins. Guy in a Cube’s test found the star much faster at 7 million rows. Same question, three different answers.

The difference is the workload. SQLBI are testing DAX-heavy models with several measures and complex filters; Greg is testing the same report shapes on pre-aggregated flat data. For a small model with one table and one question, flattening is a legitimate design: it is simpler to build and faster to refresh. For a model that will grow, be reused, or be asked new questions, the star is what keeps the DAX short and the relationships explainable.

Measure it rather than assume it.

What goes wrong

  • Totals disagree between reports because each measure carries its own filter, or because two many-to-many paths are fighting.
  • Blank where there should be zero when an extra dimension hop changes the filter context.
  • A slow model nobody can explain because a direct many-to-many relationship is being paid for on every visual.
  • Time intelligence that quietly misses days because the date table is not marked, or has gaps.
  • A measure nobody can trace because the logic lived in the flat table’s columns and not in a definition.

Where it can be used

  • several fact tables, such as sales and returns, sharing the same customer and date dimensions;
  • month-over-month and year-to-date reports, where a marked date table removes the filter code;
  • a model other people will build reports on, where the relationships are the documentation;
  • one small model with one question and one source table, where a flat table is the simpler answer.

Where to go next (optional)

The star works without either of these. Both are a step up once the model is right, and both cost something.

Aggregations. An aggregation is a summary table at a coarser grain (one row per day instead of one per sale) that Power BI answers from when a visual does not need the detail. Patrick (Guy in a Cube) built one over a 128-million-row fact table: aggregated to the date level, it came back as 4,582 rows. He also names the reason most people miss: the model you have today will grow, and an aggregation is a proactive step before refreshes and queries slow down. It joins to the same dimensions as the fact (in his example, the date table), so it needs the star first. The cost is another table to build and refresh, a grain you have to choose, and the work of checking that visuals actually hit it; Guy in a Cube has a whole video on that question.

Calculation groups. Patrick’s other example starts with two measures, quantity and sales. Then the requests arrive: month-to-date, quarter-to-date, previous year, each for both. The measure list explodes. A calculation group holds each time calculation once, as a calculation item, and applies it to whichever measure is in the visual:

-- calculation item "YTD" in a calculation group called Time Intelligence
CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )

That depends on the marked date table from the star. The cost: once a model has a calculation group, implicit measures stop working (dragging a column into a visual and letting Power BI sum it), because SELECTEDMEASURE expects an explicit measure. Patrick shows a matrix breaking exactly this way; the fix is an explicit measure for every number the reports use.

More guides

Sources

These teachers taught me this. The ideas are theirs; the mistakes are mine.