IT-eye

One of the more challenging issues when modelling dimensions are variable depth recursive structures. The most common example is the manager-employee example. These employees both exist as a record in an employee table and are linked to one another by a recursive ID (manager_ID) which reflects to the tables sequence number (ID):

Table: Employees

The organisational structure following this table can be variable in depth. For instance, its easy to to add another manager to the table and thus making this dimension three layers deep.

Generally, there are two ways to approach this: wrapping the data to a fixed level depth or creating a bridge table for more flexibility. Today I’ll focus on the fixed depth solution.

For this solution you decide how many levels you want to show in your dimension (preferably the most detailed level) and read out the recursive table into a cursor, in order to fill out any branches who do not reach this level of depth.

But first, try this query to show how the hierarchy is build up from the data as it is:

SELECT 	LPAD(' ',2*(LEVEL-1)) || Name AS Hierarchy
FROM 		Employees
START WITH  Manager_ID IS NULL	-- Highest level in hierarchy
CONNECT BY PRIOR ID = MANAGER_ID	-- Determine recursive pattern

This query shows the possible ‘gaps’ in the hierarchy, because not everyone fits easily in every level. For instance: a delivery organisation has more managing layers than sales in most cases, but they both are part of the organisational structure as a whole.

In order to fill out the branches where the lowest level in that particular branche is higher than the lowest possible level (max level) you need a cursor to prepare your data for the dimension. Use this cursor to select the level and the employee name from your source table and cycle through the whole structure to fill out the blanks.

You will need to define a variable for each attribute (level) in your dimension and use the cursor to fill these variables and insert them into a temporary table from which you can process your organisation dimension (for instance).

There are many solutions for this and they all depend nt on specific situations. In this example you could use logic like this:

If level = 1 (highest ranking manager) and you have defined your structure to be 4 layers deep. You can set the following variables: top_manager = name , sub_manager = name, team_lead = name, employee = name.

The next record in the cursor will be level = 2 (sub_manager). You don’t have to update the top_manager variable now because it’s already known at this point. Instead your record will be name_old, name, name, name. If you use the employee ID’s the end result will be something like this:

Top Sub Team Emp
1 1 1 1 (entry of top manager)
1 2 2 2 (entry of sub manager)
1 2 3 3 (entry of team lead)
1 2 3 4 (entry of employee one)
1 2 3 5 (enty of employee two)
1 2 6 6 (entry of team lead two )
1 2 6 7 (entry of employee three) and so forth.

By inserting these records in your dimension you can add your facts (for instance earnings, or costs) to everyone at the lowest level (employee) and you will avoid facing the problem of combining aggregated facts to different levels in your dimension. For instance if you want to see the total costs which the top manager if facing, just make a count using the TOP attribute. You can do this because everyone has the same top managers (1 in this example).

Have fun!

Just so you know: CONNECT BY cannot be used in joins.