🎯 Situation

A client sent me a Power BI file last month. One table. 47 columns. Sales data, customer data, product data, region data — all in the same flat sheet, exported from their ERP every Monday morning.

It worked. The visuals loaded. The numbers were right. But every time they added a new slicer or visual, the report slowed down a little more. After two years of adding columns, it had become unmaintainable.

👉 The model was a flat table — the most natural starting point, and one of the most common data modeling mistakes in Power BI.

The fix was a star schema. Not because it's a best practice box to check — but because Power BI's engine is specifically built to run on it.

⚠️ Challenge

When people first connect data to Power BI, they usually work with what they have: one big table from an Excel export or a SQL view. It feels clean and simple. But as the model grows, the limits appear.

📈 Flat table

  • Easy to understand at first glance
  • One export, one load, done
  • No relationships to manage
  • Familiar — looks like Excel

❌ What breaks down

  • Duplicated data everywhere — same customer name repeated on every sales row
  • Massive file size — VertiPaq compresses poorly without structure
  • Slow DAX — measures iterate over millions of redundant rows
  • Hard to maintain — adding a new attribute means touching the whole table
  • Filters on repeated values are expensive and unpredictable

The flat table isn't wrong because it looks bad. It's wrong because Power BI's storage engine — VertiPaq — is column-based and compression-optimized. It performs best when each column has low cardinality and clean, non-repeated values.

🔍 What a Star Schema Actually Is

A star schema has two types of tables:

  • One fact table — the transactions. Each row is one event: a sale, an order, a log entry. It contains numeric values (amounts, quantities) and foreign keys (IDs pointing to dimensions).
  • Several dimension tables — the context. Customers, products, dates, regions, salespeople. Each row is unique. No duplicates.

The fact table connects to each dimension table through a single relationship. That's the star — one center, several branches.

Before: one flat table with 47 columns and 2 million rows — CustomerName repeated 2 million times, ProductCategory repeated 2 million times.

After: one fact table with 8 columns (IDs + amounts) and dimension tables with unique values. Same data, a fraction of the size, filters that run in milliseconds.

VertiPaq compresses repeated values extremely well — but only if they're in a dedicated column with low cardinality. "Canada" repeated in a Region dimension with 5 rows compresses to almost nothing. "Canada" repeated 2 million times in a fact table does not.

✓️ How to Think About the Migration

You don't need to rebuild everything from scratch. Most flat tables can be split into a star schema with a few steps:

  • Identify the grain — what does one row represent? One sale? One order line? That becomes your fact table.
  • Extract the descriptive columns — customer name, product category, region, salesperson → each becomes a dimension with a unique ID
  • Keep only IDs and metrics in the fact table — CustomerID, ProductID, RegionID, Amount, Quantity, Date
  • Create the relationships in Power BI — fact to each dimension, one-to-many, single direction
  • Always add a dedicated Date table — never use a date column from your fact table directly for time intelligence

In the client case above: the 47-column flat table became a 7-column fact table + 5 dimension tables. Report load time went from 9 seconds to under 2.

💡 Summary

The star schema isn't an advanced concept reserved for data engineers. It's the basic structure that makes Power BI work the way it's supposed to.

Three things to remember:

  • Flat tables are a starting point, not a destination — they work fine at small scale, then become a problem as data grows
  • The performance gains are structural — no amount of DAX optimization fully compensates for a poorly modeled dataset
  • The star schema also makes DAX easier to write — filters flow naturally from dimension to fact, which is exactly how CALCULATE and relationships are designed to work

If your Power BI report is slow and your DAX looks correct, the model is almost always the real issue. Start there before optimizing anything else.

👉 A good model makes DAX easy. A bad model makes DAX impossible.

Fix the foundation first.