Example: Using Default Aggregation
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
-