Galaxy vs Star schema vs DataVault

[bi-sync]

Hi Community, 

We have a data vault and we are currently creating a star schema data mart. 
But we are unsure if we can use a galaxy star schema approach (sharing dimension tables) or if we have to go the extra mile and create a simpler star model. How well does Power BI work with those approaches?

Thank you very much! 

[bpsearle · Accepted solution]

Microsoft best practice is to use a star schema. This is what I usually start with. However, sometimes I’ve found that requirements coupled with the way visuals work lead to needing a customised data model specific to the report. Don’t forget the overall goal is to have a model that can be understood by business users and is maintainable. From my experience business users quickly pick up how to work with star schemas. I use a star schema as the main model and then use that to feed a custom report specific model if I need to. The other thing Microsoft advocates is only pulling in data to the model that is needed by the reports. The model really should be built to serve the needs of reports and users, so having a requirements / top down / business value focus. Data modelling in Power BI is a big subject, as you get further into it you will find there is a lot of excellent material on the web to help. One helpful point I’ve found is, when I’ve been struggling to get a calculation to work its usually because I’m going about it in the wrong way and need to change the model. Sometimes things don’t work out as you first envisage them!

Thanks, Brian

[v-xicai · Accepted solution]

Hi @bi-sync ,

You may create relationships between theses main tables first of all, recommend to use star schema instead of circle schema for the data model, and change the Cross filter direction of all relationships from Single to Both , which will take these tables treated as a single table. See more about relationship :https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bpsearle · Accepted solution]

Microsoft best practice is to use a star schema. This is what I usually start with. However, sometimes I’ve found that requirements coupled with the way visuals work lead to needing a customised data model specific to the report. Don’t forget the overall goal is to have a model that can be understood by business users and is maintainable. From my experience business users quickly pick up how to work with star schemas. I use a star schema as the main model and then use that to feed a custom report specific model if I need to. The other thing Microsoft advocates is only pulling in data to the model that is needed by the reports. The model really should be built to serve the needs of reports and users, so having a requirements / top down / business value focus. Data modelling in Power BI is a big subject, as you get further into it you will find there is a lot of excellent material on the web to help. One helpful point I’ve found is, when I’ve been struggling to get a calculation to work its usually because I’m going about it in the wrong way and need to change the model. Sometimes things don’t work out as you first envisage them!

Thanks, Brian