11.3 Star Schema vs Snowflake Schema
Star schema: one central fact table directly connected to each dimension table. The diagram looks like a star (see Figure 1.2). This is the recommended design for Power BI.
Snowflake schema: dimensions are normalised into several related tables – for example Orders → Product → Sub Category → Category. The chain of tables looks like a snowflake.
OrdersProduct ID Amount | ← | ProductProduct ID Sub Category ID | ← | Sub CategorySub Category ID Category ID | ← | CategoryCategory ID Category Name |
| Star schema | Snowflake schema | |
|---|---|---|
| Structure | Denormalised dimensions (one table per dimension) | Normalised dimensions (split into sub-tables) |
| Number of relationships | Fewer | More |
| DAX complexity | Simpler | Harder (filters travel through chains) |
| Performance in Power BI | Generally better | Generally slower |
| Model readability for users | Easy – all product fields in one table | Harder – fields spread across tables |
Flatten snowflakes in Power Query
Aata he bagha: if the source is snowflaked, use Merge Queries in Power Query to bring Category and Sub Category into the Product table, then disable load for the helper tables. Result: a clean star schema (तारा रचना).
Star schema ha Power BI cha "golden rule" aahe. Lakshat theva: fact madhyabhagi, dimensions bajula.
Ravindra Bagale's Tip
Mitrano, khup students keep a snowflake (Product → Sub Category → Category) karan that is how the source delivered it. In Power BI, flatten it into one Product table in Power Query. Fewer relationships mean simpler DAX, easier hierarchies and faster reports. He exam aani interview doghansathi important aahe.