Working with Overrides

By default, Analytic Calculation Engine sums all of the values of a parent member's direct children to calculate the value of the parent member. Analytic Calculation Engine executes this default aggregation by iterating over all child members and applying the plus operator. The default aggregation operates on all children, even if the child member itself is an aggregate value.

This is an example of Analytic Calculation Engine's default aggregation:

2003  (170)
     Q1  (80)
        Jan  (10)
        Feb  (20)
        Mar  (50)
     Q2 (90)
        Apr   (20)
        May   (30)
        Jun   (40)

In this example, 2003, Q1, and Q2 are nonleaf members—that is, aggregates. The numbers in parentheses to the right of the aggregate members represent their aggregate values. The numbers in parentheses to the right of the nonaggregate members represent their nonaggregated values.

You must understand default aggregation and the order of precedence that the analytic calculation engine uses to override the default aggregation. When calculating aggregate members, Analytic Calculation Engine begins with the most specific override available, and then proceeds to more general overrides. The analytic calculation engine uses this order of precedence to evaluate aggregate members:

1. If the dimension does not contain any aggregate members, use the data cube's formula.

   If the dimension does contain aggregate members, perform one of these actions:

   • If aggregate calculation is not enabled for the data cube, do not perform any more calculation of aggregates.

     Note: The analytic calculation engine generates an error when the analytic model is loaded.

   • If the Calculate Aggregates option is selected for the data cube, perform step 2.

2. Perform one of these actions:

   Note: In either of these cases, the analytic calculation engine loads initial values from the aggregate record when the analytic model is loaded, but overwrites the initial values upon recalculation.

   • Use the cube dimension override user function if it exists.

     This override operates on all of a dimension's aggregate members for the dimension as it is attached to a specific data cube.

     For example, you can create one cube dimension user function to operate on the PRODUCTS dimension when it is attached to the SALES data cube, and another cube dimension user function to operate on the PRODUCTS dimension when it is attached to the COST_OF_GOODS data cube.

     To set a cube dimension override user function, create a user function in the analytic model, and then select the user function in the Aggregate Rule column in the Dimensions tab of the data cube's properties.

   • If the cube dimension user function does not exist, perform step 3.

3. Perform one of these actions:

   • Use the member override user function.

     This override operates on specific members of a dimension. You create the member override user function in the analytic model. However, because members are often instantiated at runtime, you use the PSACETREEOVRD subrecord to assign the member override user function to the dimension rather than assign the member override user function to specific members within the analytic model.

     For example, if the end user enters aggregate data, you can write a member override function that uses the INPUT built-in function to capture the user input, and use the PARENT and CHILDCOUNT built-in functions to push down the aggregate data and create new members. Then you can assign the member override user function to the appropriate dimension in the PSACETREEORRD subrecord.

     Note: If the member override user function does not contain a value, Analytic Calculation Engine applies the default aggregation (the plus operator) to the dimension members.

   • If the member override function does not exist, perform step 4.

4. Perform one of these actions:

   • Use the dimension override function.

     The analytic calculation engine uses this override user function to calculate all of a dimension's aggregates as they apply to all dimensions.

     For example, suppose you create a dimension override function for the PRODUCTS dimension. If this dimension is attached to both the SALES and COST_OF_GOODS dimensions, the dimension override function applies to the aggregates for both data cubes.

     To set a dimension override user function, create a user function and select the user function in the Aggregate Function field of the dimension's properties.

     Note: This override function does not apply to leaf members or detail members.

   • If the dimension override function does not exist, perform step 5.

5. Aggregate the values of the child members by using the operators that are attached to each child.

   The analytic calculation engine iterates over each value to evaluate the aggregate.

   The default operator for each member is the plus operator.

   You set the operators in the OPERATION field of the PSACETREEOVRD subrecord. When the default sum operator is used, the actual value in the OPERATION field is null.

   If you do not want to use the default sum aggregation, you can populate the OPERATION field with one of these values:

   • MIN.

     Use this value for the analytic calculation engine to use subtraction aggregation. The analytic calculation engine iterates over each member and applies the subtraction operator to each iteration. The aggregate value is the resulting value after the final iteration.

   • IGN.

     Use this value for the analytic calculation engine to ignore the OPERATION field.

You use the PSACETREEOVRD subrecord to assign an override to a tree. To use the subrecord, you must first create a record definition. This record definition can contain additional fields. After you create the record definition, insert the PSACETREEOVRD subrecord into the record definition.

To attach the PSACETREEOVRD subrecord to a tree, specify the name of the override record as the RecordName parameter of the AttachTree method.

The following table describes the PSACETREEOVRD subrecord.

This topic provides an example of how to create an analytic model that uses default aggregation.

Requirements for Analytic Model

The following table provides an example of a table named MainData, which you specified as the main record of a cube collection.

DEPT field Data Type: Number	SOMEDATE field Data type: Date	NUM_SALES field Data Type: Number	PRICE_PER_UNIT field Data Type: Number
101	20040101	1	10
102	20040102	2	14
103	20040101	4	15
201	20040101	8	20
202	20040201	16	23

You want the analytic model to:

• Calculate aggregates for the NUM_SALES field.

• Save the aggregates for the NUM_SALES field.

• Establish hierarchies for the DEPT and SOMEDATE dimensions.

• Refrain from calculating aggregates for the PRICE_PER_UNIT field.

• Save all aggregates for the DEPT dimension.

• Refrain from saving aggregates for the SOMEDATE dimension.

Creating the Aggregates

To fulfill the requirements of the analytic model, perform these steps:

1. Create these dimensions:

   • DEPARTMENT

   • DATE

2. Create these data cubes:

   • SALES

   • PRICE_PER_UNIT

3. Enable the Calculate Aggregates field for the SALES data cube, because you want to calculate aggregates for this data cube.

4. Ensure that the Calculate Aggregates field is disabled for the PRICE_PER_UNIT data cube, because you do not want to calculate aggregates for this data cube.

5. Create a cube collection called SALES.

6. Select MainData as the main record for the SALES cube collection.

7. Map the data cubes and dimensions within the SALES cube collection to fields in the main record.

   The following table provides the mappings.

   Data Cube or Dimension to Map	Field in Main Record
   DEPARTMENT dimension	DEPARTMENT field Data type: Number
   SOMEDATE dimension	SOMEDATE field Data type: Date
   SALES data cube	NUM_SALES field Data type: Number
   PRICE_PER_UNIT data cube	PRICE_PER_UNIT field Data type: Number

8. Select the AGGRDATE record as the aggregate record.

   The AGGRDATE database record currently contains no data. The following table describes the fields within the record.

   Field Name	Data Type
   DEPARTMENT	String
   TREE_DATE	String
   NUM_SALES	Number

   Notice that even though the DEPARTMENT and TREE_DATE fields are of the String data type, none of the main record's fields are of this same data type. The DEPARTMENT and TREE_DATE fields are of the String data type because the hierarchy's members display strings, not dates or numbers. The data types of the aggregate record's fields must match the data types and lengths of the hierarchy's aggregate members. However, the data types of the aggregate record's fields do not need to match the data types of the main record's fields.

   Note: If you design a tree's nodes so that the fields of the nodes are of the same data type as the fields of the detail members, you can use the same data type for both the dimension's aggregate record fields and main record fields.

9. Map dimensions and data cubes to fields in the aggregate record.

   The following table lists the mappings.

   Data Cube or Dimension to Map	Field in Aggregate Record
   DEPARTMENT dimension	DEPARTMENT field
   SOMEDATE dimension	TREE_DATE field
   SALES data cube	NUM_SALES field

10. Use PeopleSoft Tree manager to create two trees:

    Note: In the two tables, italicized children represent detail values. Remember, Analytic Calculation Engine creates detail members out of the tree's detail values to establish a dimension's parent-child relationships (in a tree, detail values can serve as children and parents).

    • DEPT_TREE

      Parents	Children
      (no parent root)	GBL
      GBL	US
      GBL	LAT AM
      US	101
      US	102
      US	103
      LAT AM	201
      LAT AM	202

    • DATE_TREE

      Parents	Children
      (no parent root)	CAL2004
      CAL2004	JAN
      CAL2004	FEB
      JAN	20040101
      JAN	20040102
      FEB	20040201

11. Consider these scenarios for default sum aggregation:

    • If you select to persist all aggregates of both dimensions on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record.

      Note: Italicized values are the actual persisted aggregate members. Zero (0) values in this table are not persisted. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL CAL2004.JAN.20040102	GBL	20040102	2
      GBL CAL2004.FEB.20040201	GBL	20040201	16
      GBL.US CAL2004.JAN.20040101	US	20040101	5
      GBL.US CAL2004.JAN.20040102	US	20040102	2
      GBL.US CAL2004.FEB.20040201	US	20040201	0
      GBL.LAT AM CAL2004.JAN.20040101	LAT AM	20040101	8
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	0
      GBL.LAT AM CAL2004.FEB.20040201	LAT AM	20040201	16
      GBL.US.101 CAL2004	101	CAL2004	1
      GBL.US.102 CAL2004	102	CAL2004	2
      GBL.US.103 CAL2004	103	CAL2004	4
      GBL LAT AM.201 CAL2004	201	CAL2004	8
      GBL LAT AM.202 CAL2004	202	CAL2004	16
      GBL.US.101 CAL2004.JAN	101	JAN	1
      GBL.US.102 CAL2004.JAN	102	JAN	2
      GBL.US.103 CAL2004.JAN	103	JAN	4
      GBL.LAT AM.201 CAL2004.JAN	201	JAN	8
      GBL.LAT AM.202 CAL2004.JAN	202	JAN	0
      GBL.US.101 CAL2004.FEB	101	FEB	0
      GBL.US.102 CAL2004.FEB	102	FEB	0
      GBL.US.103 CAL2004.FEB	103	FEB	0
      GBL. LAT AM.202 CAL2004.FEB	201	FEB	0
      GBL. LAT AM.203 CAL2004.FEB	202	FEB	16
      GBL CAL2004	GBL	CAL2004	31
      GBL CAL2004.JAN	GBL	JAN	15
      GBL CAL2004.FEB	GBL	FEB	16
      GBL.US CAL2004	US	CAL2004	7
      GBL.US CAL2004. JAN	US	JAN	7
      GBL.US CAL2004.FEB	US	FEB	0
      GBL.LAT AM CAL2004	LAT AM	CAL2004	24
      GBL.LAT AM CAL2004.JAN	LAT AM	JAN	8
      GBL. LAT AM CAL2004.FEB	LAT AM	FEB	16

    • If you select to persist all aggregates of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record.

      Note: Italicized values are the actual persisted aggregate members. Zero (0) values in this table not persisted. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL CAL2004.JAN.20040102	GBL	20040102	2
      GBL CAL2004.FEB.20040201	GBL	20040201	16
      GBL.US CAL2004.JAN.20040101	US	20040101	5
      GBL.US CAL2004.JAN.20040102	US	20040102	2
      GBL.US CAL2004.FEB.20040201	US	20040201	0
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	8
      GBL.LAT AM CAL2004.JAN.20040102	LAT AM	20040102	0
      GBL.LAT AM 20040201	LAT AM	20040201	16

    • If you select to persist only the root aggregations of the DEPT_TREE dimension and to persist none of the aggregates of the DATE_TREE dimension on the Dimensions tab of the cube collection's properties, the following rows are persisted in the aggregate record.

      Note: Italicized values are the actual persisted aggregate members. The Fully Qualified Member Name field is not a database value.

      Fully Qualified Member Name	DEPARTMENT	TREE_DATE	NUM_SALES
      GBL CAL2004.JAN. 20040101	GBL	20040101	13
      GBL 20040102	GBL	20040102	2
      GBL 20040201	GBL	20040201	16

This topic provides an example of creating overrides and discusses the affect of dimension order on calculation.

The following table describes the three dimensions used in this example. The first column lists the names of the dimensions. The second column lists the dimension order, which determines calculation priority. The third column lists the dimension override functions that are used if member override functions do not exist for the children of the parents in the dimension.

The following table describes the hierarchy of the ACCT dimension that is associated with the AcctTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

The following table describes the hierarchy of the TRANS_DATE dimension that is associated with the QrtrlyTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

The following table describes the hierarchy of the DEPT dimension that is created from the DeptTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

Assume that a SALES data cube exists in the cube collection, and the three dimensions of this example are attached to this data cube.

Read the instructions carefully before analyzing the following table; the table describes two methods that the analytic calculation engine can use to calculate hierarchies.

• When you analyze only the first four columns of the table (ignore the fifth column), the basic analytic model does not contain any cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions.

• When you analyze all five rows of the table, the basic analytic model does contain cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions. The fifth column—where it applies—lists the override that the analytic calculation engine uses to calculate the row.

  For example, if the developer applies the SALES_CUBE_OVERRIDE cube dimension override to the TRANS_DATE dimension as it is attached to the SALES data cube, the Cube Dimension Override User Function column indicates where the override is applied.

In the following table, italicized values indicate aggregate members. A row may contain more than one aggregate member. If a row contains one or more aggregate members, the table denotes the winning aggregate member along with the method that the analytic calculation engine uses to calculate the member. If a row does not contain any aggregate members, the analytic calculation engine uses the data cube's rule to calculate values.

The analytic calculation engine used this logic to determine which cell of the row it finally used to calculate the aggregate:

• Aggregation for row 25:

  The analytic calculation engine used the 120 value from the TRANS_DATE dimension because this dimension was the only dimension that contained an aggregate member. Understand that if either of the two other dimensions contained an aggregate member, the analytic calculation engine would still select the 120 value because the TRANS_DATE dimension is first in priority. The analytic calculation engine used the <ACCT_DIM_DEFAULT_FORMULA> dimension override because neither a cube dimension user function nor a member override user function existed for this member.

• Aggregation for row 37:

  The analytic calculation engine used the Q1 value from the TRANS_DATE dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used the default sum aggregation because neither a member override user function existed for this member nor a dimension override user function existed for this dimension.

• Aggregation for row 41:

  The analytic calculation engine used the USA value from the DEPT dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used <DEPT_DIM_DEFAULT_FORMULA> because neither a cube dimension user function nor a member override user function existed for this member.

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.

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.