Employee Dimension and Reports to Hierarchy

View previous topic View next topic Go down

Employee Dimension and Reports to Hierarchy

Post  businessintelligence on 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

View user profile

Back to top Go down

Re: Employee Dimension and Reports to Hierarchy

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Employee Dimension and Reports to Hierarchy

Post  zoom on 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

View user profile

Back to top Go down

Re: Employee Dimension and Reports to Hierarchy

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Employee Dimension and Reports to Hierarchy

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