Best data modeling approach with hierachical source data

[jct999]

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 !

[amitchandak · Accepted solution]

@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