DIM organization in use for differently grouped data

[FatherTheWizard]

Hi,

I have an organization structure that has 4 levels (1 level is top, 4 bottom). My basic fact data (let's say invoicing and hours) comes in a way that the per row grouping is done on the 4th level (i.e. no grouping at all). However, I have another "external" set of data (let's say amount of products) which is forced to come in a way that the data is grouped on the 3rd level.

What would be the best solution if I would like to have a basic data table that contains these two datasets and have a measure hours/amount of products and I could have Dim org as a working row field? Should I create another dim table for organization for 1-3 levels so the unique values would be on 3rd level and do some magic with connections in model? Or should I create grouped fact tables to level 3 in order to tackle this? Or something else?

Thanks!

[Anonymous · Accepted solution]

You could create a table for each level, and provide each level with an ID code.  Next link each table to its level above.  You could then simply link your products to the approriate table via its ID and handle all of this through the Table Relationships.