Example: Creating a Hierarchy with Mixed Aggregate and Detail Members

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 the following 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
   D1 - 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.

• M2 and 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 aggregate 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 correct members to the nodes of this tree, specify the details start level as level two (because the root level is level one). With this specification, detail members are created out of every tree node at the VP level and any level lower than the VP level. End users use the hierarchy for navigating throughout the tree. Remember 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:

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 the members are detail members except for the hierarchy root member. Because aggregate user functions are only used to calculate aggregate members, you should create a regular formula for the TOTAL_BONUS data cube to calculate its aggregates. Use the FORCHILDREN built-in function to sum the value of the EMPLOYEE_BONUS data cube plus all of the children of the member. For example:

FORCHILDREN(DIMENSION, MEMBER, EXPRESSION)

Because you specified level two as the details start level, the root member is calculated as an aggregate. The analytic calculation engine calculates both the TOTAL_BONUS and EMPLOYEE_BONUS data cubes by using the sum default, because you did not create and select an aggregate function for this purpose.

The following 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	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