Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Employee Dimension and Reports to Hierarchy

4 posters

Go down

Employee Dimension and Reports to Hierarchy Empty Employee Dimension and Reports to Hierarchy

Post  businessintelligence Mon Aug 17, 2015 12:44 am

Hi Guys,

I have a type 2 employee dimension. Users need a report which will show employees reporting to them directly which is level 1, plus they should be able to see indirect reports which are basically Level 2 or for that matter it could be any level.

I understand that this is complex model but I appreciate help from you guys.

businessintelligence

Posts : 14
Join date : 2015-06-23

Back to top Go down

Employee Dimension and Reports to Hierarchy Empty Re: Employee Dimension and Reports to Hierarchy

Post  ngalemmo Mon Aug 17, 2015 8:57 am

The relationship is maintained as a recursive foreign key in the dimension (employee ==> manager). You publish this as an exploded hierarchy bridge.

Type 2 complicates things. The easiest thing to do is to carry a static (type 1) alternate key in the dimension and a 'current' flag. Use the static key as your foreign key to the manager and in the bridge (for both employee and manager). This gives you a stable bridge. You could include the type 2 keys in the bridge as well, but they have little value.

There are other ways to build the bridge depending on what you are trying to achieve.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Employee Dimension and Reports to Hierarchy Empty Employee Dimension and Reports to Hierarchy

Post  zoom Mon Aug 24, 2015 7:48 am

I think your question is how to write a SQL to show different level of hierarchy. If hierarchy is built in the employee dimension and if your RDBMS is Oracle then Oracle provides functionality how to write a SQL for any table defined as recursive relationship. Oracle key words to write SQL to explode recursive hierarchy are "connect by prior" or "LEVEL". Other RDBMS also provide some kind of help on how to write SQL to explode recursive hierarchy. Your best bet is to search for it on the internet.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

Back to top Go down

Employee Dimension and Reports to Hierarchy Empty Re: Employee Dimension and Reports to Hierarchy

Post  BoxesAndLines Wed Aug 26, 2015 10:57 am

You can't do this with one table, you need two tables. You're modeling a network not a hierarchy. The first table is your Employee table, the second table is your Employee Map table. You have two relationships from Employee to Employee Map, one for Managers, the other for Managed.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Employee Dimension and Reports to Hierarchy Empty Re: Employee Dimension and Reports to Hierarchy

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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