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

View previous topic View next topic Go down

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

Post  p28 on Tue Mar 13, 2012 6:12 am

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?

p28

Posts : 8
Join date : 2011-08-25
Location : Germany

View user profile

Back to top Go down

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

Post  Jeff Smith on Tue Mar 13, 2012 2:08 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  p28 on Wed Mar 14, 2012 2:24 am

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?

p28

Posts : 8
Join date : 2011-08-25
Location : Germany

View user profile

Back to top Go down

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

Post  Vishy on Thu Mar 15, 2012 4:01 am

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.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

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

Post  p28 on Thu Mar 15, 2012 4:14 am

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.

p28

Posts : 8
Join date : 2011-08-25
Location : Germany

View user profile

Back to top Go down

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

Post  Jeff Smith on Thu Mar 15, 2012 10:55 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  Vishy on Fri Mar 16, 2012 2:37 am

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.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

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

Post  p28 on Fri Mar 16, 2012 5:46 am

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

p28

Posts : 8
Join date : 2011-08-25
Location : Germany

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum