Snowflake schema vs Power BI Hierarchies

[Anonymous]

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.

[MattAllington · Accepted 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. 

[Anonymous]

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