cancel
Showing results 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

## 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_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.)

1 ACCEPTED SOLUTION
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

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

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

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