Technical data

Chapter 9 Creating Hierarchies
Example: Creating a Hierarchy with Mixed Aggregate
and D etail Mem
bers
In this example, the analytic model contains a BONUS cube collection that calculates the bonus for a group of
employees. The BONUS cube collection uses the main record, as described in this table:
Employee Bonus (in thousands)
VP
300
D1 200
D2 100
M1 40
M2 10
M3 20
This example uses the following tree, named BUS1:
VP - Vice president
D
1 - Director 1
M1 - Manager 1
D2 - Director 2
M2
- Manger 2
M3 - Manager 3
The hierarchical relationships in the BUS1 tree are:
•D1
and D2 are directors who report to VP.
•M1
is a manager who reports to D1.
•M2a
nd M3 are managers who report to D2.
The BONUS cube collection contains a data cube called EMPLOYEE_BONUS, to which the EMPLOYEE
dimension is attached.
You do not want to calculate the Vice President’s bonus by summing the bonuses of all of the Vice President’s
children. The VP node should not exist as an a ggregate member of the hierarchy; it should exist instead as a
detail member. For this reason, do not calculate aggregates for the EMPLOYEE_BONUS data cube.
To
create the correc t members to the nodes of this tree, specify the details start level as level two (because the
ro
ot level is level one). With this specification, detail members are crea ted out of every tree node at the VP
le
vel and any level lower than the VP level. End users use the hierarchy for navigating throughout the tree.
Re
member that you can create hierarchies that are only used for navigation.
Make this data available to the end user:
• The bonus for every employee.
• The total bonuses for each employee, plus the total bonuses for each employee who reports to him or her.
To
achieve these results, perform these steps:
Copyright © 1988-2007, Oracl e. All rights reserved. 109