Oracle BI EE 11g – Parent Child Hierarchies

As i had explained in my previous post here, Parent Child Hierarchies introduced in BI EE 11g are much more flexible in terms of handling aggregation of measures when compared with a Level Based Hierarchy. In this blog post, we shall see the various types of modeling techniques that we can use on a Parent-Child Hierarchy.

Lets take the example of a Staff Hierarchy in a Sales Department as shown below.

A parent-child hierarchy is unique as Attributes & Measures can be used interchangeably i.e an Attribute like Staff Salary can be modeled as a Measure and a Measure like Revenue can be modeled as an attribute. So, whether a column is treated as a measure or an attribute depends purely on how the end users want the reporting to be. Lets try to understand this through some examples.

In a parent-child hierarchy, there are 4 different ways of modeling measures & attributes(using the Staff Hierarchy above as an example). They are

1. Modeling Staff Salary as an attribute - In this case, every Staff's Salary will be showed as an attribute and there will be no roll-up of the Salary data. Salary is at the same grain as the Staff.

Modeling Staff Salary as a measure - In this case, every Staff's Salary will be added to all his/her descendant's salary. When we are at the CEO level, we would like to know what is the total cost to the company because of the Salary offered to the employees. Salary is at the same grain as the Staff.
Modeling Staff Revenue as an attribute - In a sales department, even though there is a hierarchy, even a staff at the top level of a hierarchy can make a sale. Modeling as an attribute will show the Revenue figures contributed by that specific staff without any roll-ups from the bottom of the hierarchy.
Modeling Staff Revenue as a measure - In this case, every Staff's individual revenue contribution will be added to all his/her descendant's revenue contribution. This way, when we are at the CEO (or the top node) level, we can know overall revenue of the company. Revenue is at a lower grain than Staff.

BI EE 11g can quite easily handle all these types of aggregations. For the points 2 and 4 above, we can control the behavior of aggregation (roll-up only from children or all descendants etc). I have already blogged about it here. So, i will not be covering that in this blog post. Revenue is at a lower grain than staff.

To understand all the 4 points above, we need to understand our base tables first. We have one parent-child hierarchy table for Staff dimension, a closure table for the Parent-Child hierarchy and a Sales Fact table as shown below.

For now, none of these tables are joined together. We will establish different join relationships for each of the 4 cases above.

Case 1: Modeling Salary as an Attribute

In this case, only one step is required. We need to create a Staff dimension and then add a Closure table to the Parent-Child Hierarchy. Remember there are no physically defined joins between the Closure and the Parent-Child tables.

Salary is defined as being part of the Logical Dimension itself. If we now do the reporting on the Parent-Child Hierarchy and Salary, we should now be able to see Salary as an attribute

In the case above, 10000 is the Salary of Pierre Houdan, 6000 is the Salary of Edith Houton etc. If we look at the underlying query, you will notice that the Closure table is primarily used only for extracting the next level members (that we are drilling on). It uses the Distance & IsLeaf attributes for enabling and disabling drills.

WITH
SASUBWITH60281 AS (select MEMBER_KEY as c1
from
     GCBC_SALES.STAFF_CLOSURE T82195
where  ( ANCESTOR_KEY = 'Edith Lofton' and DISTANCE = 1 ) )
select T82182.SALARY as c1,
     T82182.STAFF_NAME as c2,
     T82182.MANAGER_NAME as c4
from
     GCBC_SALES.STAFF_PARENT_CHILD T82182 /* Dim_STAFF_PARENT_CHILD */
where  ( T82182.STAFF_NAME in (select D1.c1 as c1
from
     SASUBWITH60281 D1) )

Case 2: Modeling Salary as a Fact

In the above case, there is no aggregation applied on the Salary column. What if we want to roll-up the salary data from all the descendants. In order to do that, there are a couple of things that we will have to do. First one is to create an Alias for the Parent-Child table. This Alias will act as a Fact table.

After creating the Alias, we need to enable joins between the following 3 tables

a. Parent-Child table b. Closure table c. Parent-Child Fact Alias

Parent-Child table will join to the Closure table as shown below (Child joining to Ancestor Key)

Closure table will join to the Fact Parent-Child alias table as shown below (Member Key joining to Child)

After joining these 3 tables in the physical layer, in the Dimension LTS of the Staff dimension, ensure that the closure table join is brought in as well. Also include a column from the Closure table into the Logical Dimension(primarily to avoid a warning in the repository).

Then create a new fact logical table and include the Salary column from the Parent-Child Fact alias table. Assign the aggregation as SUM and also create complex joins to the necessary dimension tables. Since Salary is related only to the Staff dimension, we need to assign the measure to TOTAL level of all the other dimensions (to enable reporting across other dimensions as well).

If we now create a report containing, the Staff Hierarchy and Salary, we can notice that the salaries get rolled up. Also, the individual's salary gets added to the Rolled-up salary (For ex. Edith Lofton's Salary = Sum of Salaries of Descendants + Salary of Edith Lofton).

If we look at the underlying query, we can notice 2 separate queries for each level that we are drilling on. One is for extracting the next level members (using the closure table similar to Case 1 query). And the other one is for doing the roll-ups.

Case 3: Modeling Revenue as an Attribute

In the first 2 cases, we had Salary at the same grain as the Staff. But in this case and the next, Revenue will actually be at a lower grain than the Staff. For example, a staff might get revenue for a company by selling multiple products at different points in Time. In this case, we shall see how to model Revenue coming from a Fact table to be modeled as an attribute i.e amount of revenue contributed by each staff.

To model this, we start with defining the joins. The only join we will need for this is an equi join between the Staff Parent-Child table and the SALES fact table. There need not be any join between the Closure table and the other tables.

Just model the SALES table as a Logical Fact table with proper aggregation on the fact measures.

If we now create a report containing, the Staff hierarchy and the Revenue measure, we will notice that Revenue measure gets treated as an attribute at the Staff grain i.e Revenue obtained by each staff will be shown. Even the numbers will not aggregate within the Staff Hierarchy.

If you look at the actual query being fired, you can see that Closure table is used just for navigation (drills). But the actual join is only between the Parent-Child and the Fact table.

Case 4: Modeling Revenue as a Measure

In the previous case, revenue value of Pierre Houdan was 141. This is an individual contribution by him (consider a CEO himself closing a sale) towards revenue. But ideally, at the CEO level we would like the numbers to Roll-up thereby finding out the overall revenue contributed by all the staff as we upwards in the hierarchy. To model Revenue as a measure, we need to alter our joins as shown below for the following 3 tables.

1. Staff Parent-Child table & Closure table - Join on Child (staff) & Ancestor Key 2. Closure table & Fact Sales table - Join on Member Key and Child (staff)

After this, in the Staff Dimension LTS, include the Closure table as well (similar to what we did above in Case 2).

If we now create a report containing the Staff Hierarchy and Revenue, we will see the numbers being rolled up from the bottom of the hierarchy.

In the above screenshot, we have Walter Bridgeman's value to be his revenue contribution + revenue contribution of his direct reportee Gerardo Sae. The underlying query for this will now involve the use of Closure table to get the descendant Revenue values.