Star Schema vs Flat Table in Power BI

Two reports can answer the same questions, yet one is fast, easy to extend, and trustworthy while the other is sluggish and full of subtle totaling bugs. The difference is almost always the data model underneath. This guide compares the two shapes you meet first in Power BI — the star schema and the single flat table — and shows when each is the right call.

The two shapes, defined

A flat table is one wide table where every column sits side by side: order date, product name, product category, customer name, customer region, sales rep, quantity, revenue, and so on. Each row repeats all the descriptive text again and again. It is the shape you get straight out of a spreadsheet export or a single CSV.

A star schema splits that same data into two kinds of tables:

• Fact tables hold the events you measure — each sale, session, or transaction — as numeric values plus short keys that point to the descriptive tables. Facts are long (many rows) and narrow (few columns).
• Dimension tables hold the things you slice and filter by — Date, Product, Customer, Region. Each dimension has one row per unique item and a key column. Dimensions are short and relatively wide.

Relationships run one-to-many from each dimension to the fact table. Drawn out, the fact sits in the middle with dimensions radiating outward — a star.

        ┌──────────┐
        │   Date   │
        └────┬─────┘
             │
┌────────┐   │   ┌──────────┐
│Product ├───┼───┤ Customer │
└────────┘   │   └──────────┘
        ┌────┴─────┐
        │  Sales   │   ← fact table (keys + measures)
        └──────────┘

Why the star schema is the recommended default

Microsoft recommends the star schema for Power BI, and the reasons are concrete, not stylistic.

Performance. Power BI's storage engine, VertiPaq, compresses columns based on how many distinct values they contain (their cardinality). A flat table that repeats "Northeast Region" across a million rows stores a low-cardinality value many times, and while it still compresses, the model carries that wide text column in the table you scan most. In a star schema, "Northeast Region" lives once in a small Region dimension, and the fact table carries only a compact integer key. Narrow, mostly-numeric fact tables compress tightly and scan fast. The wider and more repetitive your single table grows, the more memory it eats and the slower your visuals refresh.

Simpler, more reliable DAX. This is the benefit people underestimate. In a star schema, filter context flows predictably from dimensions into facts. A base measure is usually just:

Total Revenue = SUM ( Sales[Revenue] )

Drop Region[RegionName] on a chart and the filter propagates through the relationship automatically — no extra code. On a flat table you can often get the same SUM, but the moment you need a clean distinct count or a metric that must not double-count, the lack of a dedicated dimension makes life harder. A proper Customer dimension gives you one honest row per customer to count:

Active Customers = DISTINCTCOUNT ( Customer[CustomerKey] )

Reuse. One Date dimension can filter sales, returns, and support tickets at the same time. One Customer dimension serves every fact table you add later. With a flat table, each new dataset is its own island — you re-import the same date and customer columns over and over, with no clean shared axis to compare them on.

A real Date table unlocks time intelligence. DAX functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATESINPERIOD work best against a continuous date dimension that has one row per day and is marked as a date table. That is a dimension table by definition. Bury your dates inside a wide fact table and time-intelligence calculations get unreliable fast.

When a single flat table is genuinely fine

The star schema is the default, not a religion. A flat table is a reasonable choice when all of the following hold:

• It is a one-off or short-lived report — a quick look you will not maintain.
• The data comes from one source and you do not plan to join other datasets.
• The table is small (think thousands or low tens of thousands of rows, not millions).
• You only need straightforward sums and counts, not careful distinct counts, ratios, or time intelligence across periods.
• There is only one fact in play — you are not relating sales to budget, or visits to outcomes.

For a single CSV of last quarter's expenses that you need to chart once, building five dimension tables is over-engineering. Load it, make your visuals, move on. The trouble starts when "just this once" quietly becomes the foundation everyone builds on for two years.

Side-by-side comparison

Turning a flat table into a star schema

The good news: you do not need a new data source. You reshape what you already have inside Power Query, before the data loads into the model.

1. Reference, do not duplicate. Right-click your flat query and choose Reference to spin off a new query for each dimension — Customer, Product, Date.
2. Trim and de-duplicate each dimension. Keep only that entity's columns (for example CustomerID, CustomerName, Region), then Remove Duplicates so you have one row per unique customer. Make sure there is a key column.
3. Slim the fact table. Back in the original query, remove the descriptive columns that now live in dimensions, keeping the keys plus the numeric measures (quantity, revenue). This is your fact table.
4. Build a dedicated Date table. Generate a continuous calendar in Power Query, or with DAX CALENDAR or CALENDARAUTO, then mark it as a date table in Model view.
5. Create relationships. In Model view, drag each dimension key onto the matching fact key to create one-to-many, single-direction relationships.

A few practical guardrails: keep relationships single-direction where possible, because bidirectional filtering is a common source of ambiguous results; give every dimension a genuinely unique key; and resist stuffing calculated text columns into the fact table when they belong in a dimension. If you are handing this model to colleagues, a clean star is far easier for the next person to read than a 40-column mystery table. For the full walkthrough, see our guide to Power BI data modeling basics.

The short version

Reach for a flat table only for small, single-source, one-off reports you will not grow. For anything you will maintain, share, or extend — and certainly anything touching real volume — model it as a star schema. You get faster reports, DAX that behaves, accurate totals, and dimensions you can reuse the next time a new question lands on your desk. If you are just getting comfortable with measures, our guide to DAX for beginners pairs well with this one.

Don't want to model any of this by hand? That is exactly what Instant PowerBI does. Send your raw CSV or Excel files and we build a finished, branded Power BI report on a proper star schema — clean dimensions, working relationships, and ready-to-use DAX measures — delivered as a project file that opens in the free Power BI Desktop. Want to match your brand colors first? Try our free Power BI theme generator, then sign up and send us your data.