Power BI star schema: the model shape that works
Star schema design in Power BI is the most useful modeling idea an author can learn. It splits data into fact tables, which hold events and numbers, and dimension tables, which describe those events. Filters then flow from the descriptions to the numbers in a predictable way. As a result, measures stay short, visuals respond quickly and totals come out right.
- Facts and dimensions
- Why the engine likes it
- Steps to reshape a model
Last updated
What is a Power BI star schema?
Picture a sales table in the middle of a page. Each row records one sale, with a date, a product key, a customer key and an amount. That central table is the fact.
Around it sit smaller tables for dates, products and customers. Each one holds a single row per item, along with its descriptive columns. Those tables are the dimensions. Because relationships run from each dimension to the fact, the drawing looks like a star.
So the shape separates two kinds of data. Numbers to add up live in the center, while words to filter and group by live at the points.
Six building blocks of a Power BI star model
These terms appear in every modeling discussion, so they are worth knowing.
Fact table
Holds one row per event, such as an order line or a payment, with keys and numeric columns.
Dimension table
Describes a business entity, such as a product, and contains exactly one row for each.
Key column
Links the two. A unique key in the dimension matches a repeating key in the fact.
One-to-many relationship
Connects a dimension to a fact, so a filter on one product reaches all of its sales.
Grain
States what one fact row means. When the grain is clear, measures rarely double count.
Date dimension
Provides a full calendar. Time comparisons depend on it, and every fact should relate to it.
Why does a Power BI star schema perform better?
The engine stores data by column and compresses repeated values. A narrow fact table with keys and numbers compresses very well. Wide tables full of long text do not, so they take more memory.
Filtering is also cheaper. When a reader picks a product category, the engine filters a small dimension first. Then it follows one relationship to the fact. In a flat table, however, the same filter scans a huge text column.
Finally, DAX becomes simpler. Measures such as total sales or sales last year need only a few words. Because filter direction is clear, authors spend less time debugging odd totals. If you are weighing the star against a normalized design, see Power BI star schema vs snowflake schema, and for hands-on help reshaping a model, see Power BI data modeling.
Signs that a model needs a Power BI star schema
If several of these sound familiar, the model shape is the likely cause.
- One very wide table holds both transactions and descriptions
- Relationships filter in both directions to make visuals work
- Totals change depending on which column sits in a slicer
- Measures contain long workarounds for simple questions
- Several tables relate to each other in a chain or a loop
- The file grows large although the row count is modest
How to build a Power BI star schema in four steps
Reshaping takes thought, but the method is the same each time.
Name the business process
First decide what the fact records, such as sales or shipments, and state its grain in one sentence.
List the descriptions
Then group descriptive columns by subject. Each group becomes a dimension with its own key.
Shape the tables in Power Query
After that, split the flat source into facts and dimensions, remove duplicates and keep only needed columns.
Relate and test
Finally, create one-to-many relationships and compare totals with the source. When they match, rebuild the measures.
Questions about Power BI schema and model design
Is a star schema required in Power BI?
No. The product will accept almost any table layout. However, Microsoft's guidance recommends this design, because the engine and the DAX language both work best with it. Small models can survive a flat table for a while. As data and reports grow, the cost of a poor shape rises quickly.
What is the difference between a star and a snowflake?
In a snowflake, dimensions split into further related tables, such as product, subcategory and category. A star keeps those levels together in one product table. Both can work. Still, the flatter design usually gives fewer relationships, simpler filtering and an easier field list for report authors.
Can a star schema have more than one fact table?
Yes. Many models hold several facts, such as sales, budgets and inventory. They share common dimensions like date and product. That shared set lets one visual compare measures across facts. The rule is to relate each fact to the dimensions, and never to link fact tables directly.
Should the star be built in Power Query or in the source?
The earlier, the better. A warehouse or lakehouse that already provides facts and dimensions serves every model at once. When no such layer exists, Power Query can do the reshaping inside the model. We often start there and then move the logic upstream as the estate grows.
Can you remodel an existing report for us?
Yes. We review the current tables, design the new shape and rebuild the measures, while checking totals against the old version. A free 30-minute assessment starts the conversation. Power BI Agency is a division of Sataware Technologies and is independent of Microsoft. Work happens remotely inside your tenant.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.