Star vs Snowflake Schema
The till database stores products the tidy way a database likes: Products has a CategoryID that points to a Categories table, which has a DepartmentID that points to a Departments table. Arjun loads all three exactly as they are. The field list fills with near-empty tables, and a filter on Department has to travel through three relationships before it reaches Sales. There is a better shape for Power BI.
The rule
Star: every dimension one hop from the fact. Snowflake: dimensions chained to dimensions.
- In a star schema, the fact table sits in the middle and every dimension joins straight to it. Dimensions are flattened: Products holds Product, Category and Department as ordinary columns.
- In a snowflake schema, a dimension is split into a chain of smaller tables — Products → Categories → Departments — the way source databases often store them to avoid repeating text.
Star: Stores ─── Sales ─── Products (Product, Category, Department)
│
Date
Snowflake: Stores ─── Sales ─── Products ─── Categories ─── Departments
│
DatePower BI works best with a star:
- fewer relationships to maintain, and fewer tables in the field list for report builders;
- filters travel one hop instead of several, so the model is simpler to reason about and usually faster;
- the repeated category text in a flattened Products table costs little, because the model's column store compresses repeated values very well.
To flatten a snowflake, work in Power Query. Merge Categories into Products on CategoryID and expand the category name; then merge Departments on DepartmentID and expand the department name. Finally, right-click the Categories and Departments queries and untick Enable load. They still feed the merge, but they no longer appear as tables in the model.
The shortcut
Count the hops from a dimension column to the fact. More than one hop → flatten it into the dimension that touches the fact.
Count the hops
A filter on Departments crosses Departments → Categories, Categories → Products and Products → Sales: three relationships. Category needs two.
The trap is copying the source's shape because "that's how the database does it". A database is designed to store and update data; a Power BI model is designed to filter and total it. The two jobs favour different shapes.
Drills
Answer fast — trust the rule.
Products links to Categories, which links to Departments, and Products links to Sales. What is this shape?
Which shape does Power BI generally work best with?
In the snowflake, how many relationships does a filter on the Departments table cross to reach Sales?
After flattening, how many relationships does a filter on the Department column of Products cross to reach Sales?
After merging Categories into Products, why untick Enable load on the Categories query?
Remember
- Star: every dimension one hop from the fact. Snowflake: dimensions chained to dimensions.
- Count the hops; more than one means flatten with a merge in Power Query.
- Trap: copying the source database's shape into the model.