Forum Discussion
Advanced Modelling, How do you model multiple related hierarchical tables in a star schema?
Hi Tooley
Based on the description, what't the correct output you want to get? can you provide some sample output you want so that can provide more suggestion for you.
Best Regards!
Yolo Zhu
- Tooley2 years agoFrequent Visitor
Hi Yolo Zhu (@v-xinruzhu-msft),
Thanks for responding. I am trying to better understand how to convert a Snowflake Schema into a multi-star/galaxy/constellation schema for Power BI whilst following the best practises mentioned. In the pbix for the example shown, there are essentially 3 processes (Contracts, Orders & Changes). Contract only has dimension data, whereas Orders & Changes have dimensions & facts and the data has been separated into dim & fact tables.
This data is hierarchically related (i.e. Contract has Orders which have Changes), and users will want to see related items (e.g. all Orders for a Contract and/or all Changes for and Order) and they will want to filter by other things not in the sample data (i.e. all Outstanding Changes for Open Orders).
The pbix has the following simple matrix:...Just looking at Order 2, only the green items should show in the report for Order 2 as the Changes items (yellow) relate to a different Contract and Orders. I understand this is happening because there are no relationships between the dimension tables but how do you get the matrix to only show the Changes as related to their Orders (i.e. the green highlighted items for Order 2)?
My problem is understanding how to model data that has related sub-tables like in this scenario (Contract to Order to Change).....I am fine with 'flatter'/non hierarchical data (e.g. multiple facts such as purchases & sales sharing same dimensions).
This is part of a much larger model, and as mentioned there are other related sub-tables excluded to keep simple.
Thanks for your help.
Tooley