Advanced Modelling, How do you model multiple related hierarchical tables in a star schema?

[Tooley]

Hi All,

Summary: How to convert related sub tables from Snowflake to Star schema

I am struggling to find sufficient help online that can help me understand what I would have thought is a 'common real business example', i.e. how related cascading/drill down/sub tables should be modelled in Power BI, whilst following best practise rules (e.g. multiple star schema's with shared dimensions, single direction filter propagations from dim to fact, facts not related to facts, dims not related to dims). 

Most help I find covers converting Snowflaked dimensions into Star dimensions, but I cannot find anything useful on Snowflaked facts.

Scenario:
- Contracts can have none to many Orders (1-many)
- Orders can have none to many Changes (1-many)

- Contracts, Orders and Changes have other related sub tables (e.g. Quotes), but have been excluded for simplicity

Based on the above scenario and best practise rules, the following model could be created:

However, this model has problems because "Order" (dim) has no relationship to it's parent "Contract", and "Change" (dim) has no relationship to either it's parent "Order" dim or grandparent "Contract").  This causes problems with calculations and visuals, especially where information from all tables is needed in the same visual such as a matrix/table (e.g. the well known show all/sum all issue where no relationship exists).
 
Hopefullly the above adequately explains the problem without going into great detail (but happy to provide more if needed).  Can anyone help by advising what the above model should look like and/or provide some useful online links that provide the solution.

Many thanks, Tooley

[Anonymous]

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

[Tooley]

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

[Tooley]

A sample pbix which replicates the above can be downloaded from Sample pbix 

Many thanks, Tooley