Star schema for Power BI: model your data properly from day one

A star schema splits your tables in two. One central fact table holds the events you want to count — sales lines, timesheet entries, deliveries. Around it sit dimension tables that describe those events: date, customer, product, site. Filters flow one way, from dimensions into facts. Power BI is built for this shape.

That is the whole idea. The hard part is not understanding it — it is doing it on your own data, which almost never arrives in that form. So this is a working method rather than a theory lesson.

What is a star schema in Power BI?

A star schema is a way of arranging your tables so that one central fact table — the events you want to count, like sales lines or timesheet entries — sits surrounded by dimension tables that describe those events, such as date, customer and product. Each dimension has a one-to-many relationship into the fact table, and filters flow in one direction, from the dimensions into the facts.

Microsoft's own star schema guidance makes a point that trips people up: there is no setting in Power BI that marks a table as a fact or a dimension. The role is decided entirely by the relationship. In a one-to-many relationship, the "one" side is always the dimension and the "many" side is always the fact. Get the relationship right and the table type follows.

	Dimension table	Fact table
Holds	Things — customers, products, dates, sites	Events — orders, hours, transactions
Row count	Small, and fairly stable	Large, and grows over time
Key column	Unique — one row per thing	Repeating — many rows per thing
Job in the model	Filtering and grouping	Summarisation
Typical columns	Names, categories, regions, attributes	Keys plus numbers to add up

If you can put a table on one side of that line, you have made the decision the model needs.

Why does Power BI need a star schema?

Because of how report visuals work. Microsoft's guidance puts it plainly: dimension tables enable filtering and grouping, fact tables enable summarisation. Every visual you drop on a page generates a query that filters, groups and summarises, so a model shaped as a star gives the engine exactly the two kinds of table it is looking for. It also keeps DAX simple, because the filter path from any dimension to the facts is obvious.

You will find blog posts quoting specific percentage gains from restructuring a model. Treat those with caution — the number depends entirely on the data. What is reliably true, and what Microsoft states directly, is that the design is what the semantic model is optimised for. If you have not met that term before, our explainer on Power BI semantic models covers the layer this all sits in.

How do you build a star schema from one flat export?

Most SME projects start with a single wide extract from Sage, Xero, a CRM or a bespoke system — one row per order line, with the customer name, product name and salesperson repeated on every row. Microsoft calls that shape denormalised, and the fix is a Power Query job. Here is the sequence we use.

1. Find the grain. Ask what one row of the export actually represents. One order line? One order? One day of one product at one site? Write it down in a sentence. Every decision after this depends on it, and mixing grains in one fact table is the error that produces silently wrong totals.

2. List the descriptive columns. Anything that describes rather than measures — customer name, region, product, category, salesperson, status. These are your future dimensions. Anything numeric that it makes sense to add up — quantity, net value, hours, cost — stays in the fact table.

3. Reference the query, once per dimension. In Power Query, right-click your source query and choose Reference. Keep only the columns for one entity, then Remove Duplicates. You now have a customer table with one row per customer. Repeat for each dimension.

4. Give each dimension a unique key. If the source has a genuine customer code, use it. If it does not — and plenty of exports only carry the name — add an Index Column in Power Query, then merge that query back into the fact query so the key lands on both sides. Microsoft describes this as adding a surrogate key, and it is the standard move when a dimension has no natural unique column.

5. Strip the fact table back. Remove the descriptive columns you have just lifted out, leaving keys and numbers. The fact table gets narrower and the repeated text disappears.

6. Build the relationships. One-to-many, dimension to fact, filter direction single. Then check them in Model view.

7. Write explicit measures. Total Sales = SUM(Sales[NetValue]) rather than dragging the raw column onto a visual. Explicit measures are required if anyone will use Analyze in Excel, because PivotTables issue MDX queries and MDX cannot summarise columns on its own. They also let you hide columns that should never be summed — a unit price, for example.

The first time this takes an afternoon. By the third model it is an hour, and it is the same hour whether the source is a finance system or a stack of spreadsheets. If your reporting still runs on the latter, Power BI vs Excel covers when that switch is worth making at all.

Can I just use one big flat table in Power BI?

You can, and for a single small report it will work. It stops working when you add a second fact table, because there is no shared dimension to filter both — you cannot put sales and budget on the same chart if each carries its own copy of the customer name. Flat tables also make text values repeat on every row, which enlarges the model, and they leave no clean place to fix a misspelt customer name once.

That last point is the one clients feel soonest. In a star schema, "Acme Ltd" and "ACME Limited" get reconciled in the customer dimension and every report inherits the fix.

Do I need a separate date table?

In almost every model, yes. Power BI's Auto date/time feature creates a hidden date table behind every date column, which means each one increases model size and refresh time, and a filter on one cannot propagate to another table. One date dimension related to every fact table gives you a single slicer that filters everything at once, plus the custom periods — financial years, weeks, 4-4-5 calendars — that Auto date/time cannot produce.

Microsoft's auto date/time guidance is explicit about the limitations: the feature applies to all date columns or none, uses calendar years running 1 January to 31 December with no way to customise the start, and cannot add columns for other periods such as weeks. For a UK business on an April-to-March financial year, that is disqualifying on its own. Build a date table with CALENDAR or CALENDARAUTO, add the columns your reporting actually needs, and turn the global option off so new files start clean.

Where the same date dimension needs to filter by more than one date — order date, despatch date, invoice date — Microsoft's recommended pattern is a separate dimension table for each role, each with its own single active relationship. Only one relationship between two tables can be active at a time, and the alternative is writing a USERELATIONSHIP measure for every combination.

Should I use a snowflake schema instead?

Usually not. A snowflake keeps a dimension split across several normalised tables — product, subcategory, category — instead of flattening it into one. Microsoft's position is that the benefits of a single model table generally outweigh the benefits of multiple tables: fewer tables to load, shorter filter paths, a tidier Data pane, and the ability to build a hierarchy, which is not possible across more than one table.

The exception is size. A very large dimension where the denormalised columns would repeat across millions of rows may be worth leaving snowflaked. That is a judgement call on your data, not a rule.

Which modelling mistakes cost the most?

Four, in the order we see them.

Bi-directional filters used as a default. Microsoft recommends minimising them: they need more processing, can hurt query performance as their number grows, and produce slicers whose options appear and disappear in ways report users find confusing. Where you genuinely need cross-filtering, the bi-directional relationship guidance suggests handling it in a measure with CROSSFILTER rather than in the relationship.

Mixed grain in one fact table. Daily actuals and monthly budget in the same table cannot both be right. Two fact tables sharing a date dimension is the answer.

Relating on mismatched columns. A text column related to a whole number is allowed but will never match, so filters silently propagate nothing. If a visual shows the same value for every row, or nothing at all, Microsoft's relationship troubleshooting checklist works through cardinality, direction, active state and data types in order.

Dimensions built from the fact table's own text. Deriving a customer list from the sales export means customers with no sales this period vanish from your slicers. Where a proper source list exists, use it.

When should the star schema move upstream?

Power Query is doing real work in the method above, and there is a point where it becomes the wrong place for it. Microsoft's own recommendation is that when you are dealing with large data volumes, or need advanced patterns like slowly changing dimensions — keeping the version of a customer's region as it was when a sale happened, not as it is today — you should build a data warehouse and load it with ETL, then point the semantic model at that.

For most SMEs that point arrives later than vendors suggest. A well-built Power Query model handles a surprising amount. But if refreshes are creeping past their window, or several reports are each rebuilding the same dimension logic, the modelling belongs in a warehouse layer instead — which is what our data engineering work generally starts with.

Where to go next

Get the star right and most of the things people find hard about Power BI get easier: DAX gets shorter, relationships stop surprising you, and adding a second subject area becomes an afternoon instead of a rebuild. If you are working through the fundamentals, part two of our beginners' guide covers getting data in before you shape it, and our Power BI and Microsoft Fabric service page sets out how we build models for clients. The wider data and analytics practice covers where this sits alongside engineering and report design.

Easy Insight is a UK consultancy for AI, web, apps and data — senior specialists only, no juniors.