How to model 4 parallel views/hierarchies of a dimension?

Hi,

I am facing a peculiar problem in modelling a organization hierarchy . In this company there are 4 parallel views/hierarchies of internal organization like funding grp, functional grp etc, which are all exclusive of each other. I am not sure how should i model these 4 parallel views? creating 4 diff dimensions doesn't sound very intelligent to me.

Further complication is that an employee may belong to either 2 or 3 or all 4 of these parallel hierarchies . Its not necessary that all 4 views is populated for all people (for example only billable/customer facing people will have funding grp hierarchy . I can again create a dummy record for such exception case and handle it but was wondering if there is any elegant ideas to model this case?

If you want them in one dimension, then create sort of a junk dimension. Create the 4 hierarchies individually. Assign the lowest level of each hierarchy to the Employee. Select distinct values of the lowest level of each hierarchy that's been assigned to an employee, and that's your base. Populate the dimension table by joining each hierarchy to the base level.

Thanks , but how will i populate this table? I mean will there be nulls for the places where one hierarchy values are populated ? & how will surrogate keys identified for diff hierachies if they are all in one table?

I don't know how your facts and measures are alinged to your these departments, but you can also think of creating an employee dimenoins with.


EMP_SK, EMP ID, Dept1 (YES/NO),Dept2 (YES/NO),Dept3 (YES/NO),Dept4 (YES/NO)

and have this EMP_SK in the fact table.

I thought of this option but that would create 4 outtriggers , linked to employee table which is linked to fact table. So for any drill/up -drill down we want to do over these hierarchies we would face problem of joining two huge tables . Trying to link these to central fact directly seemed a better option from performance standpoint.

Create dimensions for the 4 hierarchies. Add in a Null row in each hierarchy like you would for any table. Assign the dimension keys like normal in a staging fact table. from that staging table, select distinct combinations of the surrogate keys from 4 hierarchies. This is the base of the junk dimension. Denormalize the columns from the 4 hierarchy dimensions into the junk dimension (the junk has a dimension key). When you load the fact table, replace the 4 dimension keys from the hierarchies with the dimension key from the junk dimension.

See you need to have these depts in the employee dim for history purposes but if you want drill up and down then you need to have these depts SK's in the fact table so that they are directly connected to the fact.

You need to replicate depts in employee as well as in seperate individula dimensions.

thanks jeff it helps. found one link also for mystery dims -http://www.kimballgroup.com/html/articles_search/articles2000/0003IE.html?TrkID=IE200003_2