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
Friends, many students keep a snowflake (Product → Sub Category → Category) because 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. This matters for both exams and interviews.
Ravindra Bagale's Tip – मराठी
मित्रांनो, source ने तसंच दिलं म्हणून बरेच students snowflake (Product → Sub Category → Category) तसाच ठेवतात. Power BI मध्ये Power Query वापरून तो एकाच Product table मध्ये flatten करा. कमी relationships म्हणजे सोपं DAX, सोप्या hierarchies आणि जलद reports. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, source ने ऐसा ही दिया इसलिए बहुत से students snowflake (Product → Sub Category → Category) वैसे ही रखते हैं. Power BI में Power Query से उसे एक ही Product table में flatten करो. कम relationships यानी आसान DAX, आसान hierarchies और तेज़ reports. यह exam और interview दोनों के लिए ज़रूरी है.