Technical data

Creating Hierarchies Chapter 9

1. Create a data cube called TOTAL_BONUS.

2. Attach the EMPLOYEE_BONUS dimension to the TOTAL_BONUS data cube.

3. Add the TOTAL_BONUS data cube to the BONUS cube collection.

In this example

’s hierarchy, all t he members are detail members except for the hierarchy root member. Bec ause

aggregate user

functions are only used to calculate aggregate members, you should create a regular formula for

the TOTAL_BON

US data cube to calculate its aggregates. Use the FORCHILDREN built-in function to sum

the value of th

eEMPLOYEE_BONUSdatacubeplusallofthechildrenofthemember.Forexample:

FORCHILDREN(DIMENSION, MEMBER, EXPRESSION)

Because you sp

ecified level two as the details start level, the root member is calculated as an a ggregate. The

analytic calc

ulation engine calculates both the TOTAL_BONUS and EMPLOYEE_BONUS data cubes by

using the sum d

efault, because you did not create and select an aggregate function for this purpose.

This table displays the calculation results of all members that are attached to the EMPLOYEE_BONUS

data cube:

Full Path to Employee

in Hierarchy

Employee (Dimension

Member)

Employee Bonus (in

thousands)

Total Bonus (in

thousands)

BUS1 BUS 300 670

BUS1.VP

300 670

BUS1.VP.D1 D1 200 240

BUS1.VP.D2 D2 100 130

BUS1.VP.D1.M1 M1 40 40

BUS1.VP.D2.M2 M2 10 10

BUS1.VP.D2.M3 M3 20 20