Level 5 · Star, snowflake or spaghetti?

Three models can hold exactly the same data and still behave very differently in a report. The shape decides how filters travel, how simple the measures are and how fast the report feels.

What this practice level is

This is a quiz in a practice tool built for this site; Power BI Desktop runs only on Windows. To see the shape of a real model, open Model view (the third icon on the left rail): tables are boxes, relationships are lines, and the arrowhead on each line shows the direction filters flow.

The star schema

Put the fact table in the middle and each dimension around it, one table each:

customers        Date
      \          /
        orders
          |
     order_items - products

Every relationship is many-to-one, from a fact’s foreign key to a dimension’s key, and filters flow one way: dimension to fact. Pick a category, a state or a month, and the filter has exactly one road to the order lines. This is the shape Power BI’s engine is built for, and the one Microsoft’s own modelling guidance recommends.

Harbor & Pine’s star has two fact tables at different grains, orders and order_items, and that is fine: order_items points at orders, and the dimensions point at whichever fact holds their key.

The snowflake

A snowflake splits a dimension into a chain: products → subcategories → categories. It removes repeated text, which is what a transactional database wants. In Power BI it mostly costs you: a category filter takes three hops, the field list grows, and each extra table is one more thing to relate and refresh. The usual fix is to merge the chain back into one products table in Power Query, keeping category and subcategory as columns.

The spaghetti

A spaghetti model relates everything to everything: tables linked on any matching column, many-to-many relationships, filters going both ways. Power BI can’t keep two active paths between the same tables, so it quietly deactivates some relationships, and a total can depend on which path a filter took. Nobody can explain the numbers, which is the worst thing a report can do.

How to recognise a good model

• one table per dimension, with a unique key;
• facts in the middle, dimensions around them;
• many-to-one relationships, single direction;
• a separate, marked Date table;
• no relationship you can’t explain in one sentence.

Common mistakes

• Two-way filters as a default. They feel helpful and create ambiguity. Use them only for a specific need, like a slicer that should show only products with sales.
• Many-to-many from a non-unique key. Usually a sign the “one” side should be a proper dimension table.
• Normalising for its own sake. Repeated category names in products are not a problem.