Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am aware that a star schema is the optimal approach for modelling data within Power BI however I have seen many videos where people for example instead create snowflake schemas with tables to model the hierarchy such as:
Category table -> Sub category table -> Product table ->
which finally links to your fact table. So at this point you have 4 tables linked via keys (including the fact table).
Alternatively I have also seen it where the user instead creates a single product table containing columns for category, sub category and product which is then subsequently linked to the fact table by a key (such as here https://youtu.be/VV3tYTudNBY?t=313). So here instead we just have two tables, a single product table and a fact table.
If you took the second approach, would you then create a Category -> Sub category -> Product hierarchy to effectively replicate the snowflake schema approach? Would this be an optimal approach? Is there something else I am not considering? If this is the optimal approach, why would anyone use a snowflake approach instead?
Any insight would be greatly appreciated.
Solved! Go to Solution.
Yes, star Schema is preferred
Snowflakes work, but can be more expensive. It may not be material, but it's better practice to flatten to a single dim
Snowflakes are also less user friendly. How does a user know to go to 3 different tables to get all the info about a product? Repeat for other dims.
yes, you can create a hierarchy. Not to replicate the snowflake, but to model the data in a user value adding way.
In my experience, people build snowflakes because
1. They come from a SQL background where snowflakes are often preferred
2. The data is a snowflake, and they don't know how to flatten it, or or know that they should.
Yes, star Schema is preferred
Snowflakes work, but can be more expensive. It may not be material, but it's better practice to flatten to a single dim
Snowflakes are also less user friendly. How does a user know to go to 3 different tables to get all the info about a product? Repeat for other dims.
yes, you can create a hierarchy. Not to replicate the snowflake, but to model the data in a user value adding way.
In my experience, people build snowflakes because
1. They come from a SQL background where snowflakes are often preferred
2. The data is a snowflake, and they don't know how to flatten it, or or know that they should.
interesting, I'll have to rethink my approach a little. going to have a read of this too as I expect it will be useful info. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |