Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jct999
Advocate II
Advocate II

Best data modeling approach with hierachical source data

Hello,


I receive 4 tables : 3 tables form a hierarchical structure, and the last one one is a fact table.
The relations between these 4 table is like this :

LEVEL_1  --(one to many)-->  LEVEL_2  --(one to many)-->  LEVEL_3  --(one to many)--> FACTS

The tables LEVEL_1 to LEVEL_3 can be more or less assimilated to dimension tables.
Furthemore, the relationships between the items in 2 tables in the hierarchy are associated with quantities.
Example :


LEVEL_1.ITEM_A is linked to :
-> LEVEL_2.ITEM_X : Qty = 100,
-> LEVEL_2.ITEM_Y : Qty = 300,
-> LEVEL_2.ITEM_Z : Qty = 250
...

 

If I want to calculate an aggregated KPI at the upper level of the hierarchy I have to iterate over hierachy (from the fact to the upper level) and compute a weighted KPI at each level, based on the quantities. It can be done thanks to nested iterators in DAX...

My question concerns the data model. What is the best approach for my data model, in order not to degrades the performance ?
- Would it be better to have a data model with 4 tables ?
- Or would it be better to merge the table LEVEL_1 to LEVEL_3 in a single table ?

(I have 4 millions rows in the fact table and 12K rows in the LEVEL_1 table. PK and FK in the hierarchical tables are strings.)

Thanks for your help !

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jct999 , Based on what I got. In case Level 1 , level 2 and level 3 are need both as fact and dimension. fact means they have something to aggregate

 

amitchandak_0-1716524266732.png

 

 

Like fact will be modified to have keys from level 2 and level 2. Level 2 will created as fact and Level 2 dim. Level 2 fact will have key and data from level 1

 

same way you can have level 3 fact

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@jct999 , Based on what I got. In case Level 1 , level 2 and level 3 are need both as fact and dimension. fact means they have something to aggregate

 

amitchandak_0-1716524266732.png

 

 

Like fact will be modified to have keys from level 2 and level 2. Level 2 will created as fact and Level 2 dim. Level 2 fact will have key and data from level 1

 

same way you can have level 3 fact

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.