TL;DR
A star schema in Power BI splits one wide table into a fact table and a few dimension tables. The fact table holds the numbers you add up. The dimension tables hold the text you slice by. Power BI is faster on this shape because each row stores a small key and the long text lives once. Your numbers also stay right, because a filter on one wide table takes away the rows you wanted to compare against. You build one in Power Query in six steps, and this guide walks through all six with real code.
Key Takeaways A star schema has one fact table holding the numbers and the keys, surrounded by dimension tables holding the text you slice by. Everything else in this guide follows from that split. The performance gain is a storage effect. VertiPaq builds a dictionary per column. A customer name repeated ten million times costs far more than ten million integer keys pointing at one copy of that name. Correctness matters as much as speed. On a flat table, a filter on a descriptive column also removes the rows you wanted as a comparison baseline. Ratios and group averages then come back wrong while the report still looks fine. Grain is the first decision, before any Power Query step. Write down what one fact row represents, and every later choice about keys, measures and relationships becomes mechanical. Use Reference rather than Duplicate when you split a staging query into dimensions. One change to the source logic then flows to every table built from it. Keep relationships one-to-many with a single filter direction from dimension to fact. Microsoft advises minimising bi-directional filtering because it slows queries and produces results report readers do not expect. Every model needs its own Date table marked as a date table. Power BI’s automatic date hierarchy builds a hidden table per date column, which grows the model and cannot filter two fact tables at once. Prove the result rather than assuming it. Performance Analyzer finds the slow visual, DAX Studio splits storage engine time from formula engine time, and VertiPaq Analyzer shows which column is actually expensive. Watch on YouTube
End-to-End Microsoft Power BI Implementation and Migration with Kanerika
A walkthrough of how a Power BI implementation actually runs from source data to a governed semantic model, which is the same sequence the six modelling steps below sit inside.
The Report That Was Fast Until It Was Not A finance analyst builds a sales report from one exported spreadsheet . Nine columns, forty thousand rows, four visuals, and it renders instantly. Everyone is happy.
Eighteen months later the same report carries four years of history and three new columns the sales team asked for. It also carries a regional comparison the CFO checks every Monday. Now a slicer click takes nine seconds. Worse, the regional comparison has been returning a number nobody has questioned, because the percentage it shows against the company average looks plausible.
Both problems have the same cause and the same fix. The report is reading one wide table, and Power BI was designed to read a star schema. Building a star schema in Power BI fixes the speed problem and the wrong number at the same time, because both come from the same shape.
What Is a Star Schema in Power BI? A star schema arranges tables so that measurements live in one place. The descriptions you slice those measurements by live in another. One central table holds the numbers. Several smaller tables around it hold the context. Draw the relationships and the picture looks like a star, which is where the name comes from.
In Power BI the star schema is not a database object you create. It is the shape of the semantic model you build in Power Query and the Model view. Microsoft’s own guidance treats it as the design Power BI semantic models are meant to use . That distinction matters. Your warehouse can be normalised to third normal form and your lakehouse can hold whatever it holds. The star schema still applies to the model layer your reports query.
Fact Tables vs Dimension Tables in Power BI A fact table stores business events. Each row is one thing that happened, recorded at a consistent level of detail. The columns are mostly numbers you want to add up, plus the keys that connect the row to its context. Sales transactions, order lines, inventory movements and ledger postings are all fact tables. Whichever data modeling tool produced them, the definition does not change.
A dimension table stores the entities those events involve. Each row is one customer, one product, one date, one location. The columns are descriptive. They are what a report author drags onto a slicer, an axis, or a field parameter , and the table is usually small enough that its size never matters.
There is a one-sentence test that settles almost every column. Ask whether you would ever want to sum it. If the answer is yes it belongs in the fact table. If instead you would want to group or filter by it, it belongs in a dimension. Order quantity is a fact. Product category is a dimension. Order status sits in between. The usual answer is a degenerate dimension, a descriptive value that stays in the fact table because it has no other attributes to travel with.
Why the Shape Is Called a Star Put the fact table in the middle of the Model view and drag each dimension around it. Every dimension connects directly to the fact table and nothing connects to anything else. The diagram has a hub and a set of points, and that is the star.
The visual shape carries a real rule. A dimension should never relate to another dimension. When it does, you have a snowflake, and Power BI has to traverse two hops to filter the fact table. Keeping the star flat means every filter path is one step long, which is why the model stays predictable as you add tables to it.
Why Power BI Is Faster on a Star Schema Than on a Flat Table Most articles on this topic say the star schema performs better and stop there. The reason is worth understanding, because it tells you which columns to worry about.
How VertiPaq Stores a Column Power BI’s import engine, VertiPaq, stores data column by column rather than row by row. For each column it builds a dictionary of the distinct values it found, then stores the column itself as a list of pointers into that dictionary. Two things follow. Compression depends on how many distinct values a column has, not on how many rows the table has. And the cost of a long text value is paid once in the dictionary plus a small pointer on every row.
Now compare the two shapes. In a flat table with ten million sales rows, the customer name column has as many pointers as there are rows, and its dictionary holds every distinct customer name. Add customer region, customer segment, product name and product category and each of those carries its own dictionary and its own ten million pointers.
In a star schema, the fact table holds one small integer key per dimension instead. The names, regions, segments and categories live once each, in tables that might hold a few thousand rows. The fact table shrinks to keys and numbers, which are exactly the data types VertiPaq compresses hardest.
Where a Flat Table Returns the Wrong Number Speed is the argument everyone makes. The stronger argument is correctness, and it is the one almost nobody on this topic writes down.
Alberto Ferrari’s worked example at SQLBI makes the point with a company running four beauty salons. He sets out to compare one salon’s customer mix against the average of the salons like it, and shows that the flat model returns the wrong answer. His article states the goal plainly, that a star schema is the best practice for performance and “to ensure accurate results” .
The mechanism generalises, so you can apply it to your own model. In a flat table every column sits on the same rows. When a slicer filters on one descriptive column, it removes rows, and every other column loses those rows too. That is fine when you want a filtered total. It breaks the moment your measure needs a denominator that is supposed to ignore the filter, because the comparison population you wanted has already been filtered away.
In a star schema the descriptive columns live in their own tables. A measure can remove the filter from one dimension and keep it on another. The filters arrive from separate tables, so they can be handled separately. The correct comparison becomes expressible. In the flat model, it is not.
Decide the Grain of Your Fact Table Before You Build Anything Grain is the statement of what exactly one row of the fact table represents. It is the first decision, it takes one sentence, and skipping it causes more broken Power BI models than any other single mistake.
Write the sentence out. One row is one order line. One row is one product in one warehouse on one day. One row is one invoice. Once that sentence exists, every other decision follows from it, because a column either makes sense at that level of detail or it does not.
Three questions settle a grain that is not obvious. What is the smallest event the business actually cares about measuring? Could two rows in this table ever describe the same event twice? And at what level do the measures need to add up correctly without double counting?
The grain mistakes worth knowing are the ones that surface a quarter later. Mixing order headers and order lines in one table makes order-level values repeat across lines, so summing them inflates the total. Mixing daily inventory snapshots with inventory transactions puts two different grains in one table, so no single measure is right for both. And copying customer attributes into the fact table looks harmless until the customer moves region, at which point history rewrites itself.
Kanerika Service
Power BI Semantic Model Design
Kanerika writes down the grain of every fact table, the conformed dimensions and the surrogate key strategy in a design document before build starts, so the star does not get re-cut after reports already depend on it.
Explore Power BI Services →
The Worked Example: One Flat Sales Export Everything below uses the same file, so you can follow each step against something concrete. The source is a single sheet called Sales.xlsx with nine columns.
Order ID and Order Date , which identify and date the transactionCustomer ID , Customer Name and Country , which describe who boughtProduct ID , Product Name and Category , which describe what was boughtSales Amount , the one number the report has to add upThe grain sentence is short. One row is one product on one order. The target model is a FactSales table plus DimCustomer, DimProduct and DimDate, with Power Query doing all the reshaping before anything reaches the model.
Step 1: Load the Flat File and Keep It as a Staging Query Open Power BI Desktop, choose Get Data, pick the Excel workbook and select the sheet. In the Navigator window click Transform Data rather than Load, which opens the Power Query Editor with the source query in place.
Rename that query to stg_Sales and disable its load. Right-click it in the Queries pane and clear Enable Load, so the staging table never reaches the model. Every dimension and the fact table will be built from this one query, which means any cleaning you do here happens once.
Do the shared cleaning now. Set the correct data types, trim whitespace from the text columns, and fix any obvious inconsistency in the descriptive fields. This is ordinary data transformation work, done once so nothing downstream repeats it. Say Customer Name arrives with trailing spaces in some rows. A later Remove Duplicates step will then treat those as different customers, so this step is doing real work.
Step 2: Build the Dimension Tables With Reference Instead of Duplicate With the staging query in place, each dimension becomes a short query built on top of it.
Why Reference and Not Duplicate Right-click stg_Sales and Power Query offers both Duplicate and Reference. They look interchangeable and they are not.
Duplicate copies every applied step into a new, independent query. You now have two copies of the same cleaning logic. Change a data type in the original and the copy keeps the old one, silently, until someone notices two tables disagreeing about the same source column.
Reference creates a new query whose first step is the output of the original. The cleaning logic stays in one place. Fix a trailing-space problem in stg_Sales and every dimension built by Reference inherits the fix on the next refresh. On a model with four dimensions, that is the difference between one edit and five.
Building DimProduct in Power Query Reference stg_Sales, rename the new query DimProduct, keep only the product columns, remove duplicates and add a key. The Advanced Editor shows what those clicks produce.
let
Source = stg_Sales,
KeepColumns = Table.SelectColumns(
Source,
{"Product ID", "Product Name", "Category"}
),
RemoveDuplicates = Table.Distinct(KeepColumns, {"Product ID"}),
AddProductKey = Table.AddIndexColumn(
RemoveDuplicates, "ProductKey", 1, 1, Int64.Type
)
in
AddProductKeyTwo details are doing the work. Table.Distinct is given the column list {“Product ID”} rather than being left to compare whole rows. A product whose name was edited at some point therefore cannot produce two rows for the same product. And Table.AddIndexColumn is typed as Int64.Type, because an untyped index arrives as a decimal and a decimal key relationship is both slower and easy to break.
DimCustomer is the same pattern against Customer ID, Customer Name and Country. If you want a separate DimGeography, reference stg_Sales again, keep Country alone, remove duplicates and index it.
Surrogate Keys and Natural Keys Product ID already identifies a product, so adding ProductKey can look like extra work. Microsoft lists surrogate keys among the core star schema concepts for Power BI models, and there are three practical reasons to add one.
An integer key compresses better than an alphanumeric code, which matters on the fact table where the key repeats on every row. A surrogate key survives a source system that reissues or reformats its own identifiers. And history eventually matters. When the same product has two versions valid at different times, only a surrogate key can point at the right one.
If your source identifiers are already clean integers and you have no history requirement, using them directly is a reasonable choice. Make it deliberately rather than by omission, and note it in your data model documentation.
Step 3: Build a Real Date Dimension Every analytical model needs one date table that every fact table filters through. This is the step most often skipped, because Power BI appears to handle dates on its own.
Why Auto Date/Time Is Not a Date Table Power BI’s Auto date/time option creates a hidden date table behind each date column. It works for a single visual and it costs you three things Microsoft documents directly. Each date column that generates a hidden auto date/time table increases the model size and extends the data refresh time . The periods are calendar-only, so a fiscal year starting in April cannot be expressed. Each date column also gets its own hidden table, so a filter on one cannot propagate to another. Microsoft calls that out as a problem exactly when you report on multiple fact tables such as sales and sales budget.
That last point is the one that ends the argument. The moment your model has two fact tables, a single shared Date table is the only way one slicer can filter both.
A Date Table in DAX, Then Mark as Date Table Create the table with a calculated table so the range is explicit and stable.
DimDate =
VAR BaseCalendar = CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) )
RETURN
ADDCOLUMNS (
BaseCalendar,
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Year Month", FORMAT ( [Date], "yyyy-mm" )
)Then do the step people forget. Select the table, open Table tools and choose Mark as Date Table, pointing it at the Date column. Marking the table is what lets DAX time intelligence functions work correctly against it. It is also what lets you turn Auto date/time off in Options without losing date hierarchies. Keep a Year Month sort column in place so month names sort chronologically rather than alphabetically. If your reporting leans on period comparisons, the patterns in time intelligence in DAX all assume a marked date table like this one.
Step 4: Build the Fact Table and Merge the Keys In The fact table is the last query to build, because it needs the keys the dimensions just created.
Reference stg_Sales one more time and rename it FactSales. Now merge each dimension in and pull back only its key. In the interface that is Merge Queries, matching Product ID to Product ID, then expanding only the ProductKey column from the result. Repeat for the customer dimension.
let
Source = stg_Sales,
MergeProduct = Table.NestedJoin(
Source, {"Product ID"},
DimProduct, {"Product ID"},
"dp", JoinKind.LeftOuter
),
ExpandProductKey = Table.ExpandTableColumn(
MergeProduct, "dp", {"ProductKey"}, {"ProductKey"}
),
MergeCustomer = Table.NestedJoin(
ExpandProductKey, {"Customer ID"},
DimCustomer, {"Customer ID"},
"dc", JoinKind.LeftOuter
),
ExpandCustomerKey = Table.ExpandTableColumn(
MergeCustomer, "dc", {"CustomerKey"}, {"CustomerKey"}
),
KeepFactColumns = Table.SelectColumns(
ExpandCustomerKey,
{"Order ID", "Order Date", "CustomerKey", "ProductKey", "Sales Amount"}
)
in
KeepFactColumnsThe final step is the important one. Selecting the fact columns drops Customer Name, Country, Product Name and Category out of the fact table entirely. They are not deleted from the model, they now live once each in their dimensions, and the fact table carries a small integer pointing at them. That single step is where most of the compression gain in this whole exercise comes from.
Two checks before you move on. Use JoinKind.LeftOuter rather than Inner, so a fact row whose dimension lookup fails stays visible as a blank key rather than disappearing from your totals. Then add a temporary step filtering ProductKey to null, confirm it returns zero rows, and remove the step. A silent lookup failure that removes revenue from a report is a very expensive bug to find later.
Case Study
60% Faster Reporting for Phoenix Recycling with Power BI
Phoenix Recycling Group had fragmented data across multiple systems and manual reporting that caused delays and errors. Kanerika consolidated it into auto-updating Power BI dashboards and cut reporting time by 60 percent.
Read the Case Study →
Step 5: Create the Relationships Close and Apply, then open Model view. Power BI will have guessed at some relationships. Delete every one it created automatically before you build your own, because the guesses are made on column names and they are frequently wrong.
Cardinality and What Many-to-Many Really Means Drag DimProduct[ProductKey] onto FactSales[ProductKey]. The relationship should read one-to-many, with the one side on the dimension and the many side on the fact table. Repeat for the customer and date keys.
If Power BI reports many-to-many instead, it has found something true about your data. It means the column you dropped on the dimension side has duplicate values, which means your Remove Duplicates step did not do what you expected. Go back to the dimension query and find out why, rather than accepting the many-to-many relationship. A genuine many-to-many relationship should be a deliberate modelling decision that you planned for.
Single vs Bidirectional Cross-Filter Direction Leave the cross-filter direction as Single, flowing from the dimension to the fact table. That is the default and it is almost always right.
Microsoft’s guidance is direct on this. It recommends that you minimise the use of bi-directional relationships , because they can hurt query performance and deliver confusing experiences for report users. The same guidance names three situations where bi-directional filtering genuinely solves a problem, including special one-to-one relationships, slicers that should only show values with data, and dimension-to-dimension analysis.
The practical rule is to reach for it only when you can name which of those three you are solving. Set it on one relationship rather than switching it on everywhere.
Role-Playing Dimensions and USERELATIONSHIP Sales data usually carries more than one date. An order has an order date, a ship date and sometimes a delivery date. All three want to filter through the same Date table, and Power BI allows only one active relationship between two tables.
Create all three relationships and leave two of them inactive, shown as dotted lines in Model view. Then write a measure that activates the one you need.
Total Sales = SUM ( FactSales[Sales Amount] )
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( DimDate[Date], FactSales[Ship Date] )
)One Date table now serves every date in the model. The alternative, duplicating the Date table once per role, works but leaves report authors choosing between three near-identical tables in the field list. The USERELATIONSHIP function exists precisely so you do not have to do that.
Step 6: Apply, Hide the Keys, and Write Explicit Measures The model works at this point. Two more passes make it usable by someone who did not build it.
First, hide what nobody should drag onto a report. Right-click each key column, both the ProductKey on the dimension and the ProductKey on the fact table, and choose Hide in report view. Hide the staging query if it reached the model. Treat this as part of the modelling work. A report author who groups by a surrogate key gets a technically correct chart that means nothing to anyone.
Second, write explicit measures. An implicit measure is what you get when you drag Sales Amount onto a visual and Power BI sums it for you. It works until someone needs the same number with a filter applied, at which point there is nothing to reuse. Write Total Sales as a real measure, base every variation on it, and hide the underlying column. Microsoft’s star schema guidance distinguishes explicit from implicit measures for exactly this reason.
The same discipline carries into DAX calculated columns and tables once your reports need more than a plain sum. Deciding what belongs in Power Query and what belongs in DAX follows the same logic as deciding what belongs in a fact or a dimension.
Star Schema vs Snowflake vs One Big Table in Power BI Three model shapes turn up in real Power BI work. Comparing them side by side is more useful than arguing for one in the abstract.
Shape What It Looks Like Query Performance DAX Complexity Best Used When Star schema Fact table in the middle, flat dimensions one hop away Strongest. Every filter path is one hop, fact columns compress well Lowest. Filter context is predictable Any model that will be reused, extended or handed to report authors Snowflake Dimensions split further into sub-dimensions, two or more hops out Weaker. Filters traverse extra relationships on every query Higher. More tables to reason about per measure A hierarchy is genuinely shared across several fact tables, or a dimension is very large One Big Table Everything joined into one wide flat table, no relationships Poor at scale. Repeated text columns dominate the model size Deceptive. Simple sums are easy, comparison measures become unreliable A one-off exploration, a small extract, or a passthrough report over a single source
The One Big Table argument deserves a fair hearing, because it comes up constantly among practitioners with very large warehouse tables. The case for it is that a single denormalised table avoids joins entirely and the warehouse has already done the work. The case against it in Power BI is the two things covered above. VertiPaq pays for every repeated text value, and filters on descriptive columns cannot be selectively removed. A warehouse serving SQL queries and a semantic model serving DAX are optimising for different things.
Snowflaking sits between the two. If you want the longer treatment of when the extra hop earns its cost, the star schema vs snowflake schema comparison goes through it case by case.
Multiple Fact Tables at Different Grains Real models rarely stop at one fact table. Sales and budget, shipments and returns, actuals and forecast. The same holds whether the source is a warehouse, a Microsoft Fabric lakehouse , or a set of extracts. Two rules keep a multi-fact model working.
First, never relate one fact table to another. If sales and returns both need to be filtered by product, both relate to DimProduct, and DimProduct filters both. A direct relationship between two fact tables creates ambiguous filter paths and a model nobody can reason about.
Second, share the dimensions. A dimension that filters more than one fact table is called a conformed dimension, and it is what makes the two fact tables comparable at all. The Date table is the obvious one, which is also why the auto date/time limitation above matters so much here.
Fact tables at different grains are fine as long as each one is internally consistent. A daily sales fact and a monthly budget fact can both relate to DimDate. A measure comparing them just aggregates the sales side up to the month. The problem is never two grains in two tables. It is two grains in one table.
Best Practices for a Star Schema in Power BI Most best-practice lists assert a rule and move on. Each of these carries the check that tells you whether you actually followed it.
Practice Why It Matters How to Verify It Write the grain down before building Every later decision follows from it The sentence exists in the model description field Every dimension has a unique key Duplicates force many-to-many relationships Every relationship reads one-to-many in Model view Filters flow one direction only Bi-directional filtering slows queries and confuses readers Count the relationships set to Both. The answer should be zero or justified One marked Date table Time intelligence and multi-fact filtering both depend on it Auto date/time is off in Options and the Date table shows the marked icon Dimensions connect only to facts Dimension-to-dimension links snowflake the model No relationship in Model view joins two dimension tables Keys hidden, measures explicit Report authors should not see plumbing or invent their own aggregations The field list shows no key columns and no bare numeric columns Transformations happen in Power Query Calculated columns are computed after compression and cost more memory The calculated column count is near zero outside the Date table Performance measured, not assumed The slow visual is rarely the one people suspect A Performance Analyzer trace exists for the slowest report page
Free Checklist
Power BI Checklist
Run the table above against your own model. Kanerika’s downloadable Power BI checklist covers semantic model design, relationships, DAX measures and report performance in one pass, so you can sign the model off before anyone builds a visual on it.
Get the Checklist →
Two of these interact with the rest of your Power BI setup. Moving transformations into Power Query rather than DAX is also what makes Power BI incremental refresh partition cleanly. And hiding keys while exposing named measures is what lets row-level security filter a dimension and have the restriction reach every fact table through it.
How to Prove the Model Is Actually Faster You rebuilt the model to make reports faster. Measure whether it worked, using three tools in sequence. Each one answers a different question, and running them in this order stops you guessing.
Performance Analyzer Finds the Slow Visual Performance Analyzer ships inside Power BI Desktop, on the View ribbon. Start recording, refresh the page, and it reports DAX query time, visual display time and other wait time for every visual separately. Microsoft documents it as the way to find out how each report element performs .
Read the DAX query column first and sort descending. One visual is usually responsible for most of the page. Copying its query out gives you the exact DAX to take into the next tool.
DAX Studio Splits the Time DAX Studio connects to the open Power BI Desktop file and runs that query with Server Timings enabled. It splits total duration into storage engine time, which is VertiPaq scanning columns, and formula engine time, which is DAX evaluating logic on the results.
The split tells you which problem you have. Storage engine dominating means the engine is reading too much data, which points back at model shape and column cardinality. Formula engine dominating means the measure is doing expensive row-by-row work, which points at the DAX rather than the model. Rebuilding the schema helps the first case and does nothing for the second, so this is the check that stops wasted effort.
VertiPaq Analyzer Finds the Expensive Column VertiPaq Analyzer, available inside DAX Studio, lists every column in the model with its cardinality and the space it occupies. Sort by size and read the top ten.
On a model you have just converted, this is the confirmation step. The customer name column that used to sit in a ten million row fact table should now appear once, in a dimension of a few thousand rows. Watch for a large text column still near the top of the list and still living on the fact table. That means one of your merge steps kept a descriptive column it should have dropped.
When a Star Schema Is the Wrong Answer A star schema in Power BI is the default for an analytical model. It is not a rule that applies to every file someone opens.
Skip it for genuinely small, short-lived work. An analyst pulling a forty thousand row extract to answer one question this week does not need four queries and a Date table. The model will never be extended and nobody else will read it.
Skip it for a passthrough report over a single well-modelled source. Say your warehouse already exposes a curated view designed for one report, and that report does no comparison measures. Rebuilding the view into a star in Power Query then adds maintenance without changing the answer.
Think carefully before it for operational detail reporting. Some reports exist to list individual transactions with all their attributes rather than aggregate them. Those get less from dimensional modelling, because there is nothing to aggregate and no filter context to protect.
And reconsider the boundaries when the model spans sources. With composite models or Direct Lake semantic models , dimensions and facts can come from different storage modes. Where the star sits then becomes a design question rather than a given.
The test is simple. Will anyone other than you use this model, and will it still be here in six months? Two yeses and the star schema pays for itself quickly.
Common Star Schema Mistakes in Power BI Models These are the recurring ones, in roughly the order they cost the most time when they surface late.
No grain statement. Everything else on this list is downstream of skipping the one sentence at the start.Two grains in one fact table. Order headers mixed with order lines, or snapshots mixed with transactions. Totals inflate and no measure fixes it.Relationships on text columns. Joining on Customer Name rather than a key. It works until two customers share a name or one name has a trailing space.Bi-directional filtering switched on everywhere. Usually added to make one visual behave, then left in place, after which filter paths become ambiguous.No Date table, or one that is not marked. Time intelligence silently misbehaves and nothing warns you.Dimensions related to each other. A quiet snowflake, usually created by dragging a field in Model view without thinking about direction.Descriptive columns left on the fact table. The merge brought in the key but the old text column was never removed, so the model pays twice.Calculated columns doing Power Query’s job. Computed after compression, so they cost more memory than the same column materialised upstream.Duplicate instead of Reference. Two copies of the cleaning logic that drift apart over a year.Keys left visible in the field list. A report author groups by a surrogate key and produces a chart nobody can interpret.Most of these are cheap to avoid at build time and expensive to unwind after reports depend on the model. The broader set of habits that prevent them is covered in Power BI data modeling best practices , and the tool-agnostic version in data modeling best practices .
How Kanerika Builds Power BI Semantic Models Kanerika spends much of its Power BI work on exactly the situation this article describes, which is building a star schema in Power BI out of whatever the previous tool left behind. Migrations from legacy enterprise reporting tools arrive as flat extracts by definition, because that is what the old tool produced. A Cognos to Power BI move is no different from a Tableau one in that respect. A SSRS to Power BI or Tableau to Power BI migration that lifts those extracts across unchanged produces a report that behaves like the old one. That is rarely what the client wanted.
The delivery sequence runs in four stages, and the modelling work sits in the middle of it rather than at the end.
Assess. Inventory the existing reports and find the measures that actually get read, rather than rebuilding all of them. Most legacy estates have a long tail nobody opens. The output is a list of facts and dimensions the surviving reports need, which is the grain conversation held before any development starts.
Design. Settle the star for each subject area. Which fact tables exist, at what grain, which dimensions are conformed across them, where the Date table sits, and which relationships need to be inactive for role-playing. This is a document, agreed before build, because changing it afterwards means rewriting measures.
Build. Power Query does the reshaping, following the staging-query and Reference pattern above so the logic lives in one place. Measures are explicit from the start, keys are hidden from the start, and the model is checked with Performance Analyzer and VertiPaq Analyzer before anyone builds a visual on it.
Enable. Hand the model over with the field list already curated, so report authors build against named measures rather than raw columns. This is the stage that decides whether the model survives contact with the people who did not build it, and it is where dashboard development finally starts.
Datasheet
Automating Migration from Tableau to Power BI
How Kanerika converts Tableau workbooks into Power BI, including the step where a flat extract is re-modelled into a proper semantic layer rather than lifted across as it stands.
Read the Datasheet →
The Phoenix Recycling Group engagement is a concrete version of that sequence. The client had fragmented data across multiple systems and manual reporting that caused delays and errors. Kanerika consolidated the sources, built auto-updating dashboards on top of a governed model, and the published outcome is 60 percent faster reporting. The modelling layer produced that number. The visuals sitting on it inherited the gain.
The recurring failure our teams find on inherited models is the quiet one. The report renders, the numbers are wrong, and nobody notices for a quarter because nothing looks broken. It is almost always a comparison measure sitting on a flat table. That is the correctness problem described earlier in this article, and it shows up as a plausible wrong figure instead of a slow page. That is why the model review happens before the dashboard review on every engagement. It is also why data analytics consulting and data modernization work at Kanerika starts with the semantic layer.
Talk to Kanerika
Have a Power BI Model That Slowed Down?
Bring one slow report page to a working session and we will run Performance Analyzer and VertiPaq Analyzer against it with you, then show what the star schema version would change.
Schedule a Demo →
Wrapping Up A star schema in Power BI earns its place for two practical reasons. It is the shape that makes Power BI’s storage engine cheap to run, and it is the shape that makes filter context possible to reason about. Both of those pay off on the same day, one as a faster page and one as a number you can defend.
The six steps in this guide are mechanical once the grain sentence exists. Stage the source, reference it into dimensions, key them, then merge those keys into the fact table. Wire single-direction one-to-many relationships through a marked Date table. Hide the plumbing and write explicit measures. Everything after that is reporting, whether the result is a KPI page or a quadrant chart .
If you take one thing into your next model, make it the measurement habit. Run Performance Analyzer before and after, keep the two traces, and you will know what the rebuild bought instead of assuming.
Frequently Asked Questions
What is the difference between star schema and snowflake schema in Power BI? In a star schema every dimension connects straight to the fact table, so each filter travels one hop. A snowflake splits a dimension further, so product might lead to category and then to department. The extra hops slow queries and make DAX harder to reason about. Snowflake earns its place when a hierarchy is shared across fact tables.
What is star schema with example? Take one sales spreadsheet holding order date, customer name, country, product name, category and sales amount. A star schema turns it into FactSales with the amount and three keys, plus DimCustomer, DimProduct and DimDate holding the descriptions. Each dimension joins to the fact table on its key. Reports then slice the amount without repeating text on every row.
What is a good alternative to star schema? For a small one-off extract, a single flat table is fine and needs no modelling at all. For a passthrough report over a curated warehouse view, querying that view directly works well. Both alternatives hold only while the model stays small and short-lived. Once two fact tables or a comparison measure appear, the star schema becomes the practical choice again.
Is star schema faster than snowflake? Yes, in Power BI it usually is. A star keeps every filter path one hop long, so the engine resolves a slicer without traversing extra tables. A snowflake adds a hop for each level you split out, and every hop costs time. The gap widens as the fact table grows and more slicers appear on a page.
Why is it called star schema? Draw the model in Power BI’s Model view with the fact table in the middle and the dimensions around it. Every dimension connects to the centre and none connects to another dimension. The diagram looks like a star with a hub and points. The name describes the picture, and it also describes the rule that dimensions only ever touch facts.
What is the difference between 3NF and star schema? Third normal form removes redundancy so that each fact is stored once, which suits transactional systems doing many small writes. A star schema accepts some redundancy inside dimensions so that reads stay simple and fast. Operational databases favour 3NF. Analytical models such as a Power BI semantic model favour the star, because reporting workloads read far more than they write.
Can you have multiple fact tables in a star schema? Yes, and most real models have several. Sales, budget, shipments and returns can all sit in one model. Relate each fact table to the shared dimensions rather than to each other, since a fact to fact relationship creates ambiguous filter paths. A dimension used by more than one fact table is called a conformed dimension and makes the two comparable.
What is the purpose of a star schema? It separates the numbers you measure from the text you slice by. That separation does two things. It lets the storage engine compress the fact table down to keys and values, which makes reports faster. It also keeps filter context predictable, so a measure can ignore a filter on one dimension while honouring another and still return the right answer.
Is star schema OLAP or OLTP? Star schema is an OLAP design, built for analytical processing. It reads and aggregates large volumes quickly. OLTP designs such as third normal form suit systems doing many small writes with no duplicated values. A Power BI semantic model is an analytical workload, and that is why Microsoft recommends the star shape for it.
What is the difference between star schema and wide table? A wide table keeps every column on the same rows, so descriptive text repeats and filters hit all columns at once. A star schema moves those descriptions into their own tables joined by keys. The wide table costs more memory and makes comparison measures unreliable, because filtering one column removes the rows you wanted as a baseline.
Is star schema still relevant? Yes. Microsoft still names it the recommended design for Power BI semantic models, and the reason is technical rather than historical. The VertiPaq engine compresses integer keys far better than repeated text, and single hop relationships keep filter context predictable. Newer features such as composite models and Direct Lake change where tables live, and the star shape still applies.
What are the 4 main components of star schema? The fact table holds the numeric measurements at a fixed grain. The dimension tables hold the descriptive attributes used for grouping and filtering. The keys connect the two, usually a surrogate integer on each dimension repeated on the fact rows. The relationships define how filters travel, normally one to many from dimension to fact in a single direction.
How to design a star schema? Start by writing one sentence describing what a single fact row represents. Identify the numbers that belong at that grain and put them in the fact table. Move every descriptive attribute into a dimension, give each one a unique key, and store that key on the fact rows. Add a date table, then wire one to many relationships.
Which schema is best for Power BI? The star schema is the best default for a Power BI semantic model. Microsoft documents it as the design the engine is built around, and both compression and filter behaviour improve on that shape. Snowflake suits a shared hierarchy across several fact tables. One big table suits a throwaway extract. Anything meant to be reused should be a star.
Does Power BI prefer star schema? Yes. The VertiPaq storage engine compresses each column by building a dictionary of distinct values. A fact table of integer keys therefore stores far more cheaply than one full of repeated names. Single direction relationships from dimension to fact also make filter context simple to predict. Microsoft’s own modelling guidance is written around this shape.
Which is better, star or snowflake schema? For a Power BI model the star wins in most cases, because every filter resolves in one hop and DAX stays simpler. A snowflake makes sense when a hierarchy such as product category is shared across several fact tables. It also helps when a dimension is large enough that splitting it saves real memory. Otherwise flatten the hierarchy.
What are the disadvantages of a star schema? Dimensions carry some duplicated text, so they are not as compact as fully normalised tables. Building one takes upfront work in Power Query, which feels slow on a small dataset. Changes to the grain after reports exist mean rewriting measures. And tracking how a dimension value changed over time needs extra design rather than coming for free.
When should you not use star schema? Skip it for a small extract you will use once and discard, where the modelling outlasts the question. Skip it for a passthrough report over a warehouse view already shaped for that report. Think carefully for operational listings that never aggregate anything. If the model will be reused or still exist in six months, build the star.
How do I create a star schema from Excel data in Power BI? Load the sheet through Get Data and choose Transform Data. Rename the query to a staging name and turn its load off. Reference it once per dimension, keep the relevant columns, remove duplicates and add an index key. Reference it again for the fact table, merge each dimension in to pull its key, then drop the descriptive columns.
What is the correct relationship direction in a Power BI star schema? Filters should flow one way, from the dimension to the fact table, with cardinality set to one to many. Leave cross filter direction on Single. Microsoft advises minimising bi-directional relationships because they slow queries and surprise report readers. Turn a relationship to Both only for a specific need such as a bridge table or a slicer showing values with data.
Should I use surrogate keys in Power BI? Usually yes. An integer surrogate key compresses better than an alphanumeric business code, and the saving repeats on every fact row. It also survives a source system that reissues its own identifiers. And it is the only practical way to track a dimension value that changes over time. Clean integer source keys are a fair exception.
Why should I create a separate Date table in Power BI? Power BI’s automatic date hierarchy builds a hidden table behind every date column, which grows the model and lengthens refresh. Those hidden tables also cannot filter two fact tables at once, so a single slicer will not drive sales and budget together. One shared date table, marked as a date table, fixes both and enables proper time intelligence.
When should I use a bridge table in Power BI? Use one when two tables genuinely relate many to many, such as customers holding several accounts or students enrolled in several courses. The bridge stores one row per valid pairing, holding both keys and nothing else. Each side then joins to the bridge with a one to many relationship. Reach for it only when the many to many is real.
What are the best practices for a star schema in Power BI? Write the fact table grain down before building. Give every dimension a unique key and keep relationships one to many. Leave filters flowing in a single direction. Use one date table and mark it as such. Do transformations in Power Query rather than calculated columns. Hide the key columns, write explicit measures, and measure performance instead of assuming it improved.
How do I know if my Power BI star schema is actually faster? Record a page refresh in Performance Analyzer before the rebuild and again after it, then compare the DAX query time on the slowest visual. If the number barely moves, run the same query in DAX Studio with server timings on. That tells you whether the remaining time is the engine scanning columns or the measure doing row by row work.
