Power BI How-To / Data Modeling
Power BI Star Schema Best Practices: Facts, Dimensions & Relationships
The core of Power BI star schema best practices is simple: keep numeric events in fact tables, keep descriptive attributes in dimension tables, and connect them with one-to-many relationships that filter from dimension to fact. This guide explains why that shape beats a flat table, how to split one, and the relationship rules that keep models fast.
What is a star schema, and why does Power BI want one?
A star schema is a model shape with a central fact table of numeric events — sales lines, journal entries, tickets — surrounded by dimension tables that describe them: date, customer, product, region. Power BI’s storage engine, its filtering behavior, and DAX itself are all designed around this shape.
The name comes from the diagram: facts in the middle, dimensions radiating outward like points of a star. Each dimension holds one row per thing (one row per customer) while the fact table holds one row per event (one row per sale), and a one-to-many relationship ties them together. Slicers and visuals filter dimensions; the filter flows across the relationship and the engine sums only the matching fact rows. Microsoft’s own star schema guidance (opens in new tab) is explicit that this is the recommended shape for Power BI models, and it is the assumption behind most published DAX patterns.
What is the difference between a fact table and a dimension table?
Fact tables record events with numbers you aggregate — amounts, quantities, durations — plus key columns linking to dimensions; they are long and narrow. Dimension tables describe entities with attributes you filter and group by — names, categories, dates, regions; they are short and wide. If you sum it, it is a fact; if you slice by it, it is a dimension.
| Characteristic | Fact table | Dimension table |
|---|---|---|
| What a row represents | One event or transaction (a sale, a payment, a ticket) | One entity (a customer, a product, a date) |
| Typical columns | Numeric measures plus foreign keys to dimensions | Descriptive attributes: names, categories, groupings |
| Row count | Large and growing — often millions of rows | Small and stable — hundreds to thousands of rows |
| How reports use it | Aggregated by measures (SUM, COUNT, AVERAGE) | Sliced, filtered, and used on axes and in slicers |
| Relationship side | The ‘many’ side | The ‘one’ side, filtering down into facts |
Why does one big flat table hurt performance and DAX?
A single flat table repeats every descriptive attribute on every event row, which inflates model size, raises column cardinality, and slows the engine’s compression. Worse, it breaks DAX assumptions: shared slicing across multiple fact tables becomes impossible, and time intelligence needs a proper date dimension that a flat table cannot provide.
Flat tables feel natural coming from Excel — one big sheet, everything visible — and for a tiny one-off report they can be fine. They stop being fine as soon as the data grows or a second fact arrives. Repeating ‘Customer Region’ on ten million sales rows stores the value ten million times; in a dimension it is stored once per customer. And when budget data shows up at a different grain than actuals, a flat model has nowhere to put it, while a star simply adds a second fact table sharing the same dimensions. If your report is already slow, model shape is the first suspect — our guide to fixing slow Power BI reports starts there before touching a single visual.
What are the relationship rules that keep a star schema healthy?
Keep relationships one-to-many from dimension to fact, filtering in a single direction. Avoid bidirectional filters except for well-understood cases, avoid many-to-many relationships unless the grain genuinely demands them, and never relate two fact tables directly — they share dimensions instead. One active relationship per table pair; extras stay inactive.
Each rule guards against a specific failure. Bidirectional filters make filter propagation ambiguous and are a classic cause of both slow visuals and mysteriously wrong totals. Many-to-many relationships remove the engine’s guarantee of a unique ‘one’ side, so totals can silently over- or under-count. Fact-to-fact relationships almost always mean a missing shared dimension. And when a fact has several dates — order date, ship date — keep one active relationship to the Date table and activate the others per-measure with USERELATIONSHIP. Follow those defaults and most published DAX just works.
How do you split an existing flat table into a star?
Work in Power Query: identify the descriptive column groups (customer attributes, product attributes, dates), reference the flat query to build one deduplicated dimension per group with a key, keep the fact query with just numbers and keys, and relate each dimension to the fact one-to-many. Measures then live on the fact.
In practice this is an afternoon of Power Query for a simple model and a genuine design project for a messy one — the hard part is deciding grain and handling attributes that change over time. A pragmatic sequence: build the Date dimension first (everything depends on it), then the two or three dimensions your slicers actually use, and leave rarely-used attributes on the fact until someone needs them. If the model has grown past what the team can safely restructure, that is the exact shape of work our data modeling and DAX service does — scoped and quoted in hours before any work starts.
Is a flat-table model slowing your reports down?
Restructuring into a star schema is well-bounded work: we scope it, quote the hours, and you approve before anything starts. Most model restructures are measured in days, not months — and the speed difference shows up immediately.
FAQ
Frequently asked questions
Not always, but it is the right default. A tiny single-source report can live on one table without harm. The star pays off as soon as data volume grows, a second fact table appears, or you need time intelligence — and retrofitting later is harder than starting close to the right shape. When in doubt, model it as a star.
A snowflake normalizes dimensions into sub-dimensions — product linking to subcategory linking to category. Power BI guidance generally prefers flattening those chains into a single denormalized dimension table, because extra relationship hops add complexity and cost without helping the storage engine. Flatten in Power Query and keep the star flat.
There is no fixed limit — the practical test is whether each dimension earns its place as something users filter or group by. Most business models settle around five to ten dimensions per fact. Warning signs are dimensions with one column, dimensions nothing filters by, or chains of dimensions pointing at other dimensions.
Yes. The fact table's date column is the key you relate on, but time intelligence, continuous axes, and fiscal calendars all need a dedicated, gap-free Date table marked as such. It is the one dimension virtually every Power BI model should have, and it is usually the first one to build.
Try us risk-free for 4 hours
If you’re unhappy within the first 4 hours of your block, we’ll refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, and they never expire.
Book a free demo
Get senior Power BI help, on demand
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
- Prepaid hours from $100/hour — and they never expire
- Senior-led delivery — 20+ years on the Microsoft platform
- Typical 3–5 business day turnaround
- Full refund if you’re unhappy in your first 4 hours
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.