Why Your Power BI Model Is Slow: Star Schema vs the Flat Table

The flat table feels right and isn't

Almost every Power BI report that becomes slow started the same way. Someone had a question, they built a query in SQL that joined everything together, and they loaded the result as one wide table. It worked. It was easy to filter. Then it grew to 30 million rows and 90 columns, the file hit two gigabytes, and every slicer click took nine seconds.

The instinct behind the flat table is sound — one table means no relationships to get wrong. But it fights the engine underneath, and the engine is the thing you need on your side.

What VertiPaq actually does with your data

Power BI stores data in VertiPaq, a columnar in-memory engine. Each column is compressed independently, and the compression is dictionary-based: it builds a list of distinct values and stores cheap integer pointers to them instead of the values themselves.

The consequence is direct and often surprising: a column's memory cost is driven by its number of distinct values, not its number of rows.

A Country column with 200 distinct values across 30 million rows compresses to almost nothing — 200 dictionary entries and a highly repetitive integer array that run-length encoding then squeezes further. A TransactionID column with 30 million distinct values across the same rows can't compress at all. Every value is unique, so the dictionary is as large as the data.

Now look at what a flat table does. Denormalizing means repeating every descriptive attribute on every fact row. The product name, the category, the customer address, the sales rep's email — all copied 30 million times. Worse, mixing high-cardinality columns into the table disrupts the sort order that VertiPaq relies on to compress everything else.

A star schema keeps those descriptive columns in small dimension tables where they appear once each, and leaves the fact table holding little more than keys and numbers. It is not merely tidier. It is a fundamentally smaller data structure.

What a star schema looks like

One fact table in the middle, dimensions around it, relationships one-to-many from dimension to fact.

        DimDate         DimProduct
            \               /
             \             /
              FactSales (keys + measures)
             /             \
            /               \
      DimCustomer        DimStore

The fact table holds foreign keys and numeric values that make sense to aggregate — quantity, amount, cost. Nothing else. Every attribute you slice, filter, or group by lives on a dimension.

Concretely, replace this:

FactSales: OrderID, OrderDate, ProductName, ProductCategory,
           ProductSubcategory, CustomerName, CustomerCity,
           CustomerCountry, StoreName, StoreRegion, Quantity, Amount

with this:

FactSales:   DateKey, ProductKey, CustomerKey, StoreKey, Quantity, Amount
DimProduct:  ProductKey, ProductName, Category, Subcategory
DimCustomer: CustomerKey, CustomerName, City, Country
DimStore:    StoreKey, StoreName, Region
DimDate:     DateKey, Date, Year, Quarter, Month, MonthName, DayOfWeek

The fact table went from twelve columns to six, and the four text-heavy ones now exist once per product rather than once per sale.

Callout: Drop columns you don't use. An unused column still costs memory and still slows refresh. OrderID in particular is worth scrutinising — if no visual displays it individually, a column with one distinct value per row is pure overhead. Remove it and the whole table often compresses noticeably better.

Why filters flow better too

Beyond size, the star schema matches how DAX propagates filters. Filters travel down the one-to-many relationship, from the dimension to the fact table. Put a Category slicer on the page, and it filters DimProduct, which filters FactSales. One clean path.

Two things go wrong when you deviate.

Bidirectional relationships. Turning on two-way filtering to solve one visual creates ambiguity everywhere else — the engine may find several paths between two tables and can't tell which you meant. It's also slow, because each filter application becomes a larger operation. Keep relationships single-direction and use CROSSFILTER inside the one measure that genuinely needs the other behaviour.

Snowflaking. Splitting DimProduct further into DimProduct → DimSubcategory → DimCategory is correct third normal form and the wrong choice here. Every hop is another join at query time. Dimensions are small; flatten them. Denormalize within a dimension, normalize between fact and dimension.

Always build a real date table

This is the highest-value single change in most models. Time intelligence functions — TOTALYTD, SAMEPERIODLASTYEAR, DATEADD — require a proper date dimension. Without one, they either fail or quietly return wrong results.

DimDate =
ADDCOLUMNS(
    CALENDAR(DATE(2022,1,1), DATE(2026,12,31)),
    "Year",      YEAR([Date]),
    "MonthNum",  MONTH([Date]),
    "MonthName", FORMAT([Date], "MMM"),
    "Quarter",   "Q" & FORMAT([Date], "Q"),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "DayOfWeek", FORMAT([Date], "ddd")
)

Three rules make it work: the table must cover every date in your fact data with no gaps, it must be marked as a date table in the model, and you sort MonthName by MonthNum or your charts will run alphabetically from April to September.

Measuring the damage

Don't guess which columns are costing you. DAX Studio connects to an open report and its VertiPaq Analyzer view lists every column by size, cardinality, and share of the model. Open it once on a slow report and the answer is usually obvious within thirty seconds — one or two high-cardinality columns dominating everything else.

The typical offenders, in order:

1. A datetime column carrying seconds. Split it into a date column and, if you truly need it, a separate time column. A datetime at second granularity has 86,400 distinct values per day; a date has one.
2. A transaction ID or GUID nobody displays.
3. A free-text notes or description field on the fact table.
4. Decimal amounts stored at excessive precision. Rounding currency to two decimals reduces distinct values dramatically.

Fixing those four typically shrinks a bloated model by more than half before you touch anything else.

The order to change things

If you have a slow flat-table model in front of you, work in this sequence:

1. Remove unused columns. Free, immediate, and it tells you how much of the problem was pure waste.
2. Split off a real date dimension. Enables time intelligence and removes the datetime column from your fact table.
3. Extract the widest text columns into dimensions, largest first — usually product, then customer.
4. Check relationships: single-direction, one-to-many, from dimension to fact.
5. Re-measure in DAX Studio and confirm the model shrank.

You don't have to do all of it at once, and you shouldn't. Each step is independently useful and independently verifiable, which matters when you're changing a report other people depend on.

The underlying principle is worth stating plainly: a star schema is not a tidiness convention inherited from data warehousing textbooks. It is the shape that lets a compression engine do its job. Give it repeated values in narrow fact tables and small dimensions, and it will reward you with a model that's a fraction of the size and answers in milliseconds.