Star schema modelling

What it does

Star schema splits model tables into two roles: dimensions, which describe things and provide the columns you filter and group by, and facts, which record events and provide the numbers you summarise. Power BI's engine is built around this shape, and every report visual generates a query that filters, groups and summarises — exactly the operation the shape is designed for.

Key facts

There's no table property that declares a table as a fact or a dimension. The relationship cardinality decides it: the "one" side is a dimension, the "many" side is a fact.
Dimensionality comes from which dimension key columns a fact table has. Granularity comes from the values in those keys — a fact with Date and ProductKey is two-dimensional, but whether it's daily or monthly depends on what's in the Date column.
Fact tables should load at a consistent grain. Mixing grains in one table is where totals start disagreeing with each other.
Normalising means storing only keys in the fact table. Denormalising means carrying descriptive attributes alongside. Star schema wants normalised facts and denormalised dimensions.
A snowflake dimension is a dimension split across several related tables. Power BI supports it, and the usual advice is to denormalise it into a single dimension table where practical.
Related concepts that come with the model: surrogate keys, role-playing dimensions, slowly changing dimensions, junk dimensions, degenerate dimensions and factless fact tables.
Explicit measures are DAX formulas that summarise; implicit measures are columns a visual aggregates for you. MDX clients — Excel PivotTables connected to the model — can't summarise columns, so any model consumed from Excel needs explicit measures.
Microsoft's own guidance is that when the transformation gets hard — large volumes, slowly changing dimensions — you build a data warehouse and an ETL process first, and point the model at that.

When to use / skip

Always, effectively. The engine, the relationship model, the DAX filter propagation and every piece of Microsoft's performance guidance assume it. The interesting question isn't whether to use a star schema but how far to go: a flat wide table imported from an export file will work for a departmental report and will fall over the moment somebody wants to slice by two things that share a dimension. Bring the star in as soon as there's more than one fact table or more than one report against the model. The one place to hold your nerve is snowflaking — collapsing a snowflake into one dimension is usually right, but not when the outer level has its own facts hanging off it.

Configuration decisions

Where the modelling happens: Power Query in the semantic model, or a warehouse layer upstream. Above a certain size and complexity, upstream is the only sustainable answer.
The grain of each fact table, agreed with the business and written down.
Whether snowflaked dimensions get denormalised into single tables, per dimension.
How role-playing dimensions are handled — duplicate date tables with active relationships, or one with inactive relationships and USERELATIONSHIP.
Whether the model exposes explicit measures for everything, which it must if Excel is a consumer.
Which columns are hidden: keys, technical columns, anything that isn't a real reporting attribute.

Gotchas

Tables that are half fact and half dimension are the most common design failure. They filter badly, they summarise badly, and the fix is always to split them.
Two fact tables at different grains related to the same dimensions produce totals that look wrong to users and are technically correct. Sales at line level and targets at month-category level is the standard example.
A degenerate dimension — an order number that lives on the fact table with no dimension of its own — is legitimate, but it's usually high cardinality and it's expensive in an Import model.
People flatten everything into one big table because "it worked in Excel". It'll work until they need a slicer that filters two facts at once, at which point the whole thing needs rebuilding.
Slowly changing dimensions done in Power Query are painful and slow. That's the signal that a warehouse is overdue.

Consultant notes

Draw the star on a whiteboard in the first workshop. It's the fastest way to get the business to tell you the grain, and grain arguments are cheaper before you've built anything.
Frame denormalising dimensions as a usability decision, not just a performance one. Fewer tables in the field list is a genuine benefit to report authors.
When a client insists their existing extract is fine, ask what happens when they want to compare two facts. That usually settles it without an argument.
Microsoft's own advice recommends a warehouse when things get hard, which is a useful line to have on hand when the client's expectation is that Power BI replaces the data platform.
Optimal model design is part science and part art — the docs say so. Break the rules where it clearly helps, but be able to say why.

Worth a look whenever a second fact table joins the model — that's when grain problems surface.