Working with Overrides
This topic provides overviews of default aggregation, override order of precedence, and the PSACETREEOVRD subrecord.
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.
Note: This default aggregation is not used if you specified a cube dimension override user function, a member override user function, a dimension override user function, or do not have any aggregates in the relevant part of the analytic model.
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:
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.
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.
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.
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.
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.
Field Name |
Description |
Possible Values |
---|---|---|
TREE_NAME |
The name of the tree that contains the node to override. |
DEPT_TREE |
SET_ID |
The setID of the tree. |
123 |
EFFDT |
The effective date of the tree. |
12/1/03 |
TREE_NODE |
The name of the dimension member on which the override should operate. |
GBL |
ACERULEID |
The name of the user function to apply as an override. This field can be null because it can be reused. |
USER_FUNCTION_NAME |
OPERATION |
Add, subtract, or ignore this entry when aggregating a parent member. |
MIN, IGN Note: The default value in the OPERATION field is null, causing the analytic calculation engine to use the sum operator for aggregating members. Other possible values in the table are MIN for subtraction aggregation and IGN for ignore. |
Note: The PSACETREEOVRD subrecord must contain a value in either or both of the OPERATION or ACERULEID fields. If both of these fields are null, the analytic calculation engine ignores the row.
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:
Create these dimensions:
DEPARTMENT
DATE
Create these data cubes:
SALES
PRICE_PER_UNIT
Enable the Calculate Aggregates field for the SALES data cube, because you want to calculate aggregates for this data cube.
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.
Create a cube collection called SALES.
Select MainData as the main record for the SALES cube collection.
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
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.
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
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
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.
Dimension |
Dimension Order/Priority |
Dimension Override User Function |
---|---|---|
ACCT |
1 |
<ACCT_DIM_DEFAULT_FORMULA> |
TRANS_DATE |
2 |
<NONE> |
DEPT |
3 |
<DEPT_DIM_DEFAULT_FORMULA> |
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.
Note: Overrides are not performed on cells denoted (leaf) or <none>.
Parent |
Child |
Member Override User Function |
---|---|---|
100 |
110 |
<SALES_ACCT_SUM> |
100 |
110 |
<DIRECTOR_ACCT_SUM> |
100 |
120 |
<none> |
110 |
111 |
(leaf) |
110 |
112 |
(leaf) |
120 |
121 |
(leaf) |
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.
Note: Overrides are not performed on cells denoted (leaf) or <none>.
Parent |
Child |
Member Override User Function |
---|---|---|
Q1 |
Q1 |
<none> |
Q1 |
Jan |
(leaf) |
Q1 |
Feb |
(leaf) |
Q1 |
Mar |
(leaf) |
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.
Note: Overrides are not performed on cells denoted (leaf) or <none>.
This example uses the <RED_HERRING> child node override as incorrect data. Leaf nodes do not have aggregations.
Parent |
Child |
Member Override User Function |
---|---|---|
GBL |
GBL |
<SOME_DEPT_AVG> |
GBL |
USA |
<none> |
GBL |
EUR |
<none> |
USA |
CA |
(leaf) |
USA |
GA |
(leaf) |
USA |
NY |
(leaf) |
USA |
TX |
<RED_HERRING> |
USA |
IL |
(leaf) |
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.
Row |
ACCT Dimension Member Priority 1 |
TRANS_DATE Dimension Members Priority 2 |
DEPT Dimension Members Priority 3 |
Cube Dimension Override User Function |
---|---|---|---|---|
1 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Q1 |
USA |
N/A |
2 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Q1 |
CA |
N/A |
3 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Q1 |
NY |
N/A |
4 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Q1 |
TX |
N/A |
5 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Jan |
USA |
N/A |
6 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Jan |
CA |
N/A |
7 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Jan |
NY |
N/A |
8 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Jan |
TX |
N/A |
9 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Feb |
USA |
N/A |
10 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Feb |
CA |
N/A |
11 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Feb |
NY |
N/A |
12 |
Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM> |
Feb |
TX |
N/A |
13 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Q1 |
USA |
N/A |
14 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Q1 |
CA |
N/A |
15 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Q1 |
NY |
N/A |
16 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Q1 |
TX |
N/A |
17 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Jan |
USA |
N/A |
18 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Jan |
CA |
N/A |
19 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Jan |
NY |
N/A |
20 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Jan |
TX |
N/A |
21 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Feb |
USA |
N/A |
22 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Feb |
CA |
N/A |
23 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Feb |
NY |
N/A |
24 |
Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM> |
Feb |
TX |
N/A |
25 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Q1 |
USA |
N/A |
26 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Q1 |
CA |
N/A |
27 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Q1 |
NY |
N/A |
28 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Q1 |
TX |
N/A |
29 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Jan |
USA |
N/A |
30 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Jan |
CA |
N/A |
31 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Jan |
NY |
N/A |
32 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Jan |
TX |
N/A |
33 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Feb |
USA |
N/A |
34 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Feb |
CA |
N/A |
35 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Feb |
NY |
N/A |
36 |
Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member. |
Feb |
TX |
N/A |
37 |
111 |
Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension. |
USA |
<SALES_CUBE_OVERRIDE> |
38 |
111 |
Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension. |
CA |
<SALES_CUBE_OVERRIDE> |
39 |
111 |
Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension. |
NY |
<SALES_CUBE_OVERRIDE> |
40 |
111 |
Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension. |
TX |
<SALES_CUBE_OVERRIDE> |
41 |
111 |
Jan |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
42 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Jan |
CA |
N/A |
43 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Jan |
NY |
N/A |
44 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Jan |
TX |
N/A |
45 |
111 |
Feb |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
46 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Feb |
CA |
N/A |
47 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Feb |
NY |
N/A |
48 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
111 |
Feb |
TX |
N/A |
49 |
112 |
Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member. |
USA |
<SALES_CUBE_OVERRIDE> |
50 |
112 |
Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member. |
CA |
<SALES_CUBE_OVERRIDE> |
51 |
112 |
Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member. |
NY |
<SALES_CUBE_OVERRIDE> |
52 |
112 |
Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member. |
TX |
<SALES_CUBE_OVERRIDE> |
53 |
112 |
Jan |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
54 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Jan |
CA |
N/A |
55 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Jan |
NY |
N/A |
56 No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Jan |
TX |
N/A |
57 |
112 |
Feb |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
58 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Feb |
CA |
N/A |
59 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Feb |
NY |
N/A |
60 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
112 |
Feb |
TX |
N/A |
61 |
121 |
Winning aggregate: Q1 Use default sum aggregation. |
USA |
<SALES_CUBE_OVERRIDE> |
62 |
121 |
Winning aggregate: Q1 Use default sum aggregation. |
CA |
<SALES_CUBE_OVERRIDE> |
63 |
121 |
Winning aggregate: Q1 Use default sum aggregation. |
NY |
<SALES_CUBE_OVERRIDE> |
64 |
121 |
Winning aggregate: Q1 Use default sum aggregation. |
TX |
<SALES_CUBE_OVERRIDE> |
65 |
121 |
Jan |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
66 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Jan |
CA |
N/A |
67 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Jan |
NY |
N/A |
68 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Jan |
TX |
N/A |
69 |
121 |
Feb |
Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member. |
N/A |
70 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Feb |
CA |
N/A |
71 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Feb |
NY |
N/A |
72 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values. |
121 |
Feb |
TX |
N/A |
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.
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:
Create a data cube called TOTAL_BONUS.
Attach the EMPLOYEE_BONUS dimension to the TOTAL_BONUS data cube.
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 |