Hierarchy in dimension table

View previous topic View next topic Go down

Hierarchy in dimension table

Post  Thindi on Fri Nov 16, 2012 4:54 am

Hi

In our data mart we have a dimension consist of hierarchical data .... We have a table called employee having coulmn c1, c2,c3,c4, mgr_id. This table hold data for two type of employee manager and other employee. In case of manager it populate column c2 and in case of other employee c2 is blank and hold values for column c1,c3 ,c4 and mgr_id . Mgr_id hold information for manager(parent row).
In case of row for manager mgr_id is null.

emp_id----c1----c2----c3---mgr_id
101 ------null---HR---null----null
102-------abc--null----xyz---101

we need to have report on child level ,

102---abc---HR---xyz----101

column c2 is null in case of child row '102' . so it need to retrieve from parent row.
what is best way to model this.
at this point of time, it is modeled as one dimension. Idea is to create alias at universe level and join with parent dimnesion as 1:1 join.
I need suggestion about right way to do this.... is it ok to do it as I mentioned above or create a single dimension by flatening hierarchy or... if there is any other option ?

thanks

Thindi

Posts : 1
Join date : 2012-11-15

View user profile

Back to top Go down

Re: Hierarchy in dimension table

Post  Jeff Smith on Fri Nov 16, 2012 10:43 am

"Ragged Hierarchies" might be what you are looking for.

They are a little complex. Let's say you have 4 levels. President, Vice President, Manager, Worker. The Hierarchy would have a 5th level or a base level. Everyone would show up in the Base level. The worket level would only contain the workers. The rows for the manager, VP, and Pres would be Nulls. The Manager level, would have the manager for each Worker and would be populated for the Manager Rows. It would be Null for the VP and pres. The VP level would be populated for everyone except the Pres and the Pres level would list the president for everyone.

You can have situations where a worker reported up to a President with no manager or VP.

It gets even more complex with the company has different management structures for different departments.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Hierarchy in dimension table

Post  ngalemmo on Fri Nov 16, 2012 4:28 pm

Flattening does not work very well for ragged hierarchies. A hierarchy bridge is the more appropriate choice.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Hierarchy in dimension table

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