Semantic model design: why the star schema is still the answer, and what breaks when you ignore it

Nearly every slow, fragile or distrusted Power BI model we have been asked to repair had the same root cause, and it was not DAX. It was that someone loaded tables in the shape the source system happened to use, connected them until the relationships stopped erroring, and started building visuals.

The star schema is forty years old, unfashionable, and still the correct answer for an analytical model. This is why, and what specifically goes wrong when it is skipped.

What a star schema actually is

One fact table at a defined grain, surrounded by dimension tables that describe the things being measured, with single-direction one-to-many relationships from dimension to fact. That is the whole idea.

The reason it works in Power BI specifically is the engine. The VertiPaq engine compresses columnar data extremely well, and relationship traversal from a small dimension to a large fact is the operation it is optimised for. A snowflaked or normalised model forces more joins; a flattened wide table destroys compression on repeated dimension attributes; a model with ambiguous relationship paths forces the engine, and the developer, to guess.

A properly conformed star schema is faster, smaller and easier to reason about, and the gap widens as data grows.

Grain is the decision everything else follows from

Before any table is loaded, answer one question for each fact table: what does one row represent?

"Sales" is not a grain. "One line of one invoice, after allocation of order-level discount" is a grain. The second version tells you how to handle the discount, whether a credit note is a negative row or a separate fact, and what a count of rows means.

Where organisations get this wrong, the symptom appears later and looks unrelated: a measure that double counts when sliced by one dimension but not another, or a total that is correct at month level and wrong at day level. Almost always the fact table contains mixed grain, order-level and line-level rows in the same table, because someone unioned two sources that looked similar.

Rule we apply: one grain per fact table, stated in the model description. If you need two grains, build two fact tables and conform their dimensions. Two facts at different grains sharing dimensions is a normal, correct design.

Conformed dimensions are the actual deliverable

A conformed dimension is one shared definition of a business entity used by every fact that references it. One customer dimension, one product dimension, one date dimension, not a copy per source system.

This is where the political work lives, and it is why modelling projects take longer than the technical estimate suggests. Two systems will disagree about what a customer is: one counts trading accounts, another counts legal entities, a third counts delivery addresses. Conforming them means someone has to decide, and that someone has to be a person with authority rather than the analytics engineer.

Our sequence is deliberately blunt about it:

1. Profile each source's version of the entity: key, grain, row count, overlaps.
2. Put the differences in front of the business in a single table.
3. Get a decision, recorded with the name of the person who made it.
4. Build the conformed dimension with a surrogate key and a mapping back to each source key.

Step four is what lets you answer "why does this customer appear once here and twice in the source" two years later.

The date dimension, and role-playing

Every model needs a date dimension: contiguous dates across the full range, marked as the model's date table, with the attributes the business actually uses, fiscal period, week-ending, trading day flags, public holidays per country if you operate in several.

Where a fact has several dates (order date, dispatch date, invoice date, due date), you have a role-playing dimension. There are two valid designs:

• One date dimension with an active relationship to the primary date, plus inactive relationships activated in DAX via USERELATIONSHIP. Compact, and the right default.
• Separate date dimension copies, one per role. More tables, but self-documenting for business users who want to drag "Dispatch date" from a table called Dispatch date.

What is not valid is bidirectional relationships to make several dates "work at once". That produces ambiguity, and ambiguity in a model produces numbers nobody can explain.

Slowly changing dimensions: only where history matters

A type 1 dimension overwrites: the customer's current region replaces the old one, and all history reports under the new region. A type 2 dimension keeps versions with validity dates, so historical facts report under the attribute as it was at the time.

Type 2 is more work and more expensive to query, so it should be a decision rather than a default. The test is simple: if an attribute changes, should last year's numbers change with it?

• Sales rep assignment: usually type 2. You want last year's sales attributed to who held the account then.
• Customer email address: type 1. Nobody wants historical reporting by former email.
• Product cost: type 2, almost always, margin history is meaningless otherwise.

Mixed types in one dimension are fine and common. Document which columns are which, because the next developer cannot infer it.

The five mistakes that generate most of the support calls

Bidirectional relationships used to "make filtering work". They introduce ambiguity, degrade performance, and produce results that change depending on query path. There are legitimate uses, a bridge table for a genuine many-to-many, and they should be deliberate, documented and rare.

Calculated columns doing transformation work. A calculated column is computed at refresh and stored, consuming memory and compressing poorly if it has high cardinality. Transformation belongs upstream: in the source, in the warehouse, or in Power Query. Calculated columns are for things that genuinely need the model's context.

Measures iterating over large fact tables. SUMX over a 200-million-row fact with a row-level calculation inside is a decision to be slow. Push the arithmetic to a column computed upstream, or restructure the measure so the engine can use its aggregation paths.

No surrogate keys. Natural keys as relationship columns means string joins, poor compression and breakage when a source renumbers. Integer surrogate keys are cheap and solve all three.

Too many hidden columns kept "in case". Every column costs memory and refresh time. A model carrying forty unused columns from a source table is slower and larger for no benefit. Remove them; the source still has them if you need them later.

Testing a model before anyone sees a report

We run four checks before a report is built on a new model:

1. Row counts and totals reconcile to the source of record for several periods, including a period with known adjustments.
2. Every measure returns the expected value at three grains, day, month, year, and when sliced by each dimension. Double counting shows up immediately.
3. Security tested from a non-privileged account for each role, including drill-through paths. A row-level security rule nobody has tried is an assumption.
4. Performance traced with Performance Analyzer and DAX Studio on realistic volumes, with the slowest visual's query plan examined rather than guessed at.

Those four checks take a day and prevent most of what otherwise becomes a six-month trust problem.

The measure dictionary

The deliverable that outlives the model is the measure dictionary: every KPI with its business definition, its owner, its DAX, and any deliberate exclusions. Not generated documentation of syntax, a statement of intent that a finance director can read and challenge.

It is also the artefact that makes Copilot and natural-language querying work, because field descriptions and synonyms derived from a real dictionary are what the language layer has to reason over. We cover that in Copilot readiness is a metadata project.

Where to start if your model is already a mess

Do not rebuild from scratch as a first move. Inventory what exists, identify the two or three measures the business actually argues about, and conform those first. A model that answers three contested questions correctly buys more credibility than a complete rebuild delivered in nine months.

Composite models and aggregations: use sparingly, document heavily

Composite models let one semantic model combine import and DirectQuery storage modes, a large fact queried directly, with small dimensions held in memory. Used deliberately they solve a real problem: a fact table too large to import, with dimensions small enough that in-memory filtering keeps the user experience acceptable.

Used as a way to avoid a modelling decision, they produce a model nobody can reason about, where performance depends on which combination of filters a user happens to apply.

Our rules:

• A composite model needs a written justification in the architecture document, naming the table that cannot be imported and why.
• Dimensions stay in import mode. If a dimension is large enough to need DirectQuery, the grain is probably wrong.
• Aggregation tables are the better first answer. A pre-aggregated table at month and product-category grain, mapped to the detail fact, serves the majority of queries from memory and only falls through to the large table when someone drills.
• Test the fall-through path explicitly. The query that drops to DirectQuery is the one a user will run in a meeting.

Row-level security patterns that survive an audit

Row-level security is where modelling meets the control function, and the patterns that work are the ones driven by data rather than by hand-maintained lists.

The dynamic pattern. A security dimension holding user principal names mapped to the entities they may see, loaded from your directory or HR system. The RLS rule filters that dimension by USERPRINCIPALNAME(), and the filter propagates to the facts. Nobody maintains a list inside the model, and leavers disappear with the next refresh.

The hierarchy problem. Where authority is hierarchical, so that a regional manager sees their region and a director sees several, model the hierarchy explicitly with a path or a bridge table rather than enumerating combinations. Enumeration works until the organisation reorganises, which it will.

Object-level security hides whole tables or columns rather than rows, and is the right answer for a sensitive measure that some roles must not see at all. It is less widely used than it should be, because people reach for report-level visibility instead, which is a presentation trick, not a control.

Testing. Every role tested from a non-privileged account, including drill-through paths and any export. This is the step that gets skipped, and drill-through is specifically where a correctly restricted summary leaks the underlying rows.

Treat a model like code

A semantic model that exists only as a binary file on someone's laptop is a model you cannot review, diff, or roll back.

• Store it as metadata, not as a binary. Tabular Editor can save a model as a folder of files, which makes changes reviewable in a pull request.
• Deploy through pipelines, with development, test and production workspaces and an approval step between test and production.
• Use the ALM Toolkit to diff two models when you inherit something undocumented. It is frequently the fastest way to find out what actually changed.
• Keep a changelog of measure definition changes, because a measure that quietly changes definition is indistinguishable, to a business user, from the business changing.

This is the part that converts a model from a personal artefact into an organisational one, and it is the single biggest difference between estates that scale and estates that get rebuilt every three years.

If you want that inventory done properly, a technical review will produce it along with a prioritised remediation plan, and the plan is yours whether or not we do the work.