Employee Dimension and Reports to Hierarchy

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.

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.

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.

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.