Enterprise PeopleTools 8.51 PeopleBook: Analytic Calculation Engine

Creating Cube Collections

This chapter provides overviews of cube collections and types of cube collections and discusses how to:

Create cube collections.
Define cube collection properties.

Understanding Cube Collections

A cube collection is a collection of related data cubes. You create cube collections to load data from the database into the analytic model, receive user input, persist data back to the database, and display calculated data to the end user at runtime.

When the end user loads an analytic instance, Analytic Calculation Engine loads the data from the database into the data cubes of the analytic model. These data cubes exist within cube collections. You map the main record from the database to the cube collection, and the main record's fields to the data cubes and dimensions within the cube collection.

After loading an analytic instance, the end user has access to one or more cube collections within the application. These cube collections which are displayed in PeopleSoft Pure Internet Architecture pages with analytic grids contain the data that end users can view or edit. You create these pages in PeopleSoft Application Designer.

See Creating Analytic Grids.

Note. The analytic model may contain cube collections that are not visible to the end user.

For each record that you want to work with in the analytic model, you generally create read/write cube collection to load data into the analytic model, and a presentation cube collection for end user input, reporting and forecasting. The presentation cube collection calculates the data from the read/write cube collection's data cubes and displays the calculated data to the end user. Presentation cube collections may also receive end user input.

You can also create an intermediate/calculation cube collection to organize data cubes and create calculations whose results are not displayed to the end user.

In the General tab of the cube collection's properties, you map the main record, which stores the fact data that you want to load and persist. For a read/write cube collection, select one of the records that you selected in the analytic type definition that corresponds to the analytic model. For a presentation cube collection, select a derived/work record from the list of available records.

Note. Do not map intermediate/calculation cube collections to any records.

In the analytic type definition, you do not have to select the derived/work records that you want to map to presentation cube collections. However, the analytic type definition must include all derived/work records that are mapped to cube collections on which you use the NetChanges parameter of the GetData CubeCollection class method.

When a cube collection is mapped to either a Writable-only record or a record with the Readable and Writable attributes, all data cubes in the cube collection should share the same set of dimensions.

If desired, you can also use the General tab (in PeopleSoft Application Designer–Cube Collections) to map the cube collection to an aggregate record to persist the cube collection's aggregate data.

After you map the cube collection to the main and aggregate records, use the Field Map tab (in PeopleSoft Application Designer–Cube Collections) to map the cube collection's data cubes and dimensions to the fields of the main and aggregate records.

Note. You can map a data cube or dimension to one field within one record. After you have mapped a data cube or dimension to a particular record field, you cannot use that record field in another mapping. You can, however, reuse the same field if that field is from a different record.

Use the Dimensions tab (in PeopleSoft Application Designer–Cube Collections) to define these additional attributes for the dimensions in the cube collection:

How much aggregate data is saved.
Sort order.
Filter user function.

Understanding Types of Cube Collections

You create three different types of cube collections in an analytic model. This section discusses:

Read/write cube collections.
Intermediate/calculation cube collections.

Note. Intermediate/calculation cube collections are optional.
Presentation cube collections.

Read/Write Cube Collections

Use read/write cube collections to load data from the database, receive user input, and persist data back to the database. For the main record, you can select any record type except derived/work records.

Note. The analytic type that you use with the analytic model must contain the records that you map to read/write cube collections.

See Understanding the Relationship of Analytic Types to Analytic Models.

This table indicates which data cube types are allowed in a read/write cube collection:

Data Cube Type	Allowed in Read/Write Cube Collection?
Input data cubes See Input Data Cubes.	Yes
Calculation data cubes See Input Data Cubes.	Yes Note. Initial data values for calculation data cubes are loaded from the database. Calculated values are written back to the database.
Association data cubes See Association Data Cubes.	Yes
Virtual data cubes See Virtual Data Cubes.	No

Data Cube Type

Allowed in Read/Write

Cube Collection?

Input data cubes

See Input Data Cubes.

Yes

Calculation data cubes

See Input Data Cubes.

Yes

Note. Initial data values for calculation data cubes are loaded from the database. Calculated values are written back to the database.

Association data cubes

See Association Data Cubes.

Yes

Virtual data cubes

See Virtual Data Cubes.

Intermediate/Calculation Cube Collections

Use intermediate/calculation cube collections to organize data cubes and create intermediate calculations in an analytic model. These intermediate values are neither displayed to the end user nor persisted to the database. For this reason, do not map a main record to an intermediate/calculation cube collection. You can view intermediate/calculation cube collections in the Analytic Model Viewer.

Note. Intermediate/calculation cube collections are optional.

This table indicates which data cube types are allowed in an intermediate/calculation cube collection:

Data Cube Type	Allowed in Intermediate/Calculation Cube Collection?
Input data cubes See Input Data Cubes.	Yes Note. Although input data cubes are allowed in calculation cube collections, their data is not updated. For this reason, input data cubes serve no purpose in intermediate/calculation cube collections.
Calculation data cubes See Input Data Cubes.	Yes
Association data cubes See Association Data Cubes.	Yes
Virtual data cubes See Virtual Data Cubes.	Yes

Data Cube Type

Allowed in Intermediate/Calculation

Cube Collection?

Input data cubes

See Input Data Cubes.

Yes

Note. Although input data cubes are allowed in calculation cube collections, their data is not updated. For this reason, input data cubes serve no purpose in intermediate/calculation cube collections.

Calculation data cubes

See Input Data Cubes.

Yes

Association data cubes

See Association Data Cubes.

Yes

Virtual data cubes

See Virtual Data Cubes.

Yes

Presentation Cube Collections

Use presentation cube collections to present data to the end user for the purposes of reporting and forecasting. For forecasting purposes, end users may also enter data into presentation cube collections. You can select only a derived/work record as the main record of a presentation cube collection. If you select any other type of record, you will not be able to select the cube collection on the Analytics tab of the analytic grid.

Note. You must create the derived/work record before selecting it as the main record.

See Creating New Records.

Use the GetData and SetData methods to transfer data between presentation cube collections and the application server.

This table indicates which data cube types are allowed in a presentation cube collection:

Data Cube Type	Allowed in Presentation Cube Collection?
Input data cubes See Input Data Cubes.	Yes
Calculation data cubes See Input Data Cubes.	Yes
Association data cubes See Association Data Cubes.	Yes
Virtual data cubes See Virtual Data Cubes.	Yes

Example: Creating Two Cube Collections

Suppose you want to work with sales data in an analytic model. Create these cube collections:

SALES_RW read/write cube collection.

On the General tab, map this cube collection to the SALES main record to receive and persist raw sales data. This record must exist in the analytic type definition. This cube collection contains these data cubes:
- UNIT_COST data cube.
  
  Map this data cube to the UNIT_COST field.
- UNIT_SOLD data cube.
  
  Map this data cube to the UNIT_SOLD field.
SALES_PRES presentation cube collection.

Map this cube collection to the SALES_WK derived/work record to calculate sales data and display the calculated data to the end user at runtime. This cube collection contains the TOTAL_SALES data cube, which is mapped to the TOTAL_SALES_WK field. The TOTAL_SALES data cube contains this formula:
```
UNIT_COST * UNIT_SOLD
```

This diagram provides a visual representation of these cube collections:

SALES_IN and SALES_WK cube collections

Creating Cube Collections

To create a cube collection:

Select Start, Programs, PeopleTools 8.5x, Application Designer to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select Part, New, Cube Collection.

The Edit Part Name dialog box appears.
Enter the name of the cube collection.
Click the OK button.

You can now drag and drop the desired data cubes and dimensions into the cube collection.

Defining Cube Collection Properties

This section discusses how to:

Map a cube collection to main and aggregate records.
Map data cubes and dimensions to fields.
Define additional cube collection dimension properties.

Mapping a Cube Collection to Main and Aggregate Records

To map a cube collection to Main and Aggregate records:

Select Start, Programs, PeopleTools 8.5x, Application Designer to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the cube collection that contains the main and aggregate records that you want to map, and then select the General tab.

This is an example of the Cube Collections - General tab:

Cube Collection

Displays the name of the cube collection.

Description

Enter a more detailed description of the cube collection.

Main Record

Select a main record to map to the cube collection.

For a cube collection that is used for user input and data retrieval, select one of the records that you selected in the analytic type definition to use in the analytic model. For a cube collection that is used to calculate data and display the calculated data to the end user at runtime, select a derived/work record.

Aggregate Record

Select a record to store the cube collection's aggregate data.

Records that are used as aggregate records should be read after records that are used as main records.

See Synchronization Order.

Mapping Data Cubes and Dimensions to Fields

To map data cubes and dimensions to fields:

Select Start, Programs, PeopleTools 8.5x, Application Designer to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the cube collection that contains the data cubes and dimensions that you want to map, and then select the Field Map tab.

This is an example of the Cube Collections - Field Map tab:

Part Name

Displays the name of the data cube or dimension to which you map fields.

Note. You can map a field to only one data cube or dimension.

Part Type

Displays whether the part to which you map fields is a data cube or dimension.

Main Field

Select a main field to map to the data cube or dimension.

Note. You can map a data cube or dimension to one field within one record. After you have mapped a data cube or dimension to a particular record field, you cannot use that record field in another mapping. You can, however, reuse the same field if that field is from a different record.

When mapping dimensions and data cubes, you may want to map dimensions to the key fields in the main record and data cubes to the non-key fields in the main record. The PeopleSoft system, however, does enable you to map dimensions to non-key and data cubes to key fields. To perform the most appropriate mapping, you must have a deeper understanding of the relationship between data cubes and dimensions.

See Data Cubes and Dimensions.

Aggregate Field

Select a field to store the cube collection's aggregate data.

Defining Additional Cube Collection Dimension Properties

This section discusses how to:

Set additional cube collection dimension properties.
View additional cube collection dimension properties.

Setting Additional Cube Collection Dimension Properties

To set additional cube collection dimension properties:

Select Start, Programs, PeopleTools 8.5x, Application Designer to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the cube collection that contains the dimensions whose properties you want to set, and then select the Dimensions tab.
Double-click any of the cells in the row of a dimension to access the Edit Cube Collection Dimension dialog box.

This is an example of the Edit Cube Collection Dimension dialog box:

Persist Aggregate

Select whether to persist the dimension's aggregate values to the database.

Aggregate data is persisted to the aggregate record that you select on the General tab.

ALL: Select to persist all of the dimension member aggregate values to the database.

NONE: Select to persist none of the dimension member aggregate values to the database.

ROOT: Select to persist only the value of the hierarchy root member to the database.

See Understanding the Persistence of Aggregate Data.

Filter User Function

Select a filter user function to apply to the dimension.

Select None if you do not want to apply a filter user function to the dimension.

See Rules, Formulas, and User Functions.

Sort Type

By Name: Select to sort the dimension member values by name.

By Key: Select to sort the dimension member values by data cube values.

Note. You can only select from the fields that are mapped to data cubes.

Sort Key #1

If the By Key option is selected, select the first data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.

Sort Key #2

If the By Key option is selected, select the second data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.

Sort Key #3

If the By Key option is selected, select the third data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.

Viewing Additional Cube Collection Dimension Properties

The Dimensions tab displays additional properties that you have set for the dimensions in a cube collection.

To view additional cube collection dimension properties:

Select Start, Programs, PeopleTools 8.5x, Application Designer to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the cube collection that contains the dimensions whose properties you want to view, and then select the Dimensions tab.

This is an example of the Cube Collections - Dimensions tab:

Dimension

Displays the name of the dimension.

Persist Aggregate

Displays any aggregates that are persisted for the dimension.

Filter

Displays the filter formula that is applied to the dimension.

Sort Type

Displays the sort type that is applied to the dimension's values.

Sort #1

If the dimension values are sorted by key, displays the first key field by which the dimension member values are sorted.

Sort #1 Order

If the dimension member values are sorted by the first key, displays whether the sort is by ascending or descending order.

Sort #2

If the dimension member values are sorted by key, displays the second key field by which the dimension member values are sorted.

Sort #2 Order

If the dimension member values are sorted by a second key, displays whether the sort is by ascending or descending order.

Sort #3

If the dimension member values are sorted by key, displays the third key field by which the dimension member values are sorted.

Sort #3 Order

If the dimension member values are sorted by key, displays whether the sort is by ascending or descending order.

Cube Collection	Displays the name of the cube collection.
Description	Enter a more detailed description of the cube collection.
Main Record	Select a main record to map to the cube collection. For a cube collection that is used for user input and data retrieval, select one of the records that you selected in the analytic type definition to use in the analytic model. For a cube collection that is used to calculate data and display the calculated data to the end user at runtime, select a derived/work record.
Aggregate Record	Select a record to store the cube collection's aggregate data. Records that are used as aggregate records should be read after records that are used as main records. See Synchronization Order.

Part Name	Displays the name of the data cube or dimension to which you map fields. Note. You can map a field to only one data cube or dimension.
Part Type	Displays whether the part to which you map fields is a data cube or dimension.
Main Field	Select a main field to map to the data cube or dimension. Note. You can map a data cube or dimension to one field within one record. After you have mapped a data cube or dimension to a particular record field, you cannot use that record field in another mapping. You can, however, reuse the same field if that field is from a different record. When mapping dimensions and data cubes, you may want to map dimensions to the key fields in the main record and data cubes to the non-key fields in the main record. The PeopleSoft system, however, does enable you to map dimensions to non-key and data cubes to key fields. To perform the most appropriate mapping, you must have a deeper understanding of the relationship between data cubes and dimensions. See Data Cubes and Dimensions.
Aggregate Field	Select a field to store the cube collection's aggregate data.

Persist Aggregate	Select whether to persist the dimension's aggregate values to the database. Aggregate data is persisted to the aggregate record that you select on the General tab. ALL: Select to persist all of the dimension member aggregate values to the database. NONE: Select to persist none of the dimension member aggregate values to the database. ROOT: Select to persist only the value of the hierarchy root member to the database. See Understanding the Persistence of Aggregate Data.
Filter User Function	Select a filter user function to apply to the dimension. Select None if you do not want to apply a filter user function to the dimension. See Rules, Formulas, and User Functions.
Sort Type	By Name: Select to sort the dimension member values by name. By Key: Select to sort the dimension member values by data cube values. Note. You can only select from the fields that are mapped to data cubes.
Sort Key #1	If the By Key option is selected, select the first data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.
Sort Key #2	If the By Key option is selected, select the second data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.
Sort Key #3	If the By Key option is selected, select the third data cube name by which you would like to sort the dimension member values. Select to sort the dimension's key values in ascending or descending order.

Dimension	Displays the name of the dimension.
Persist Aggregate	Displays any aggregates that are persisted for the dimension.
Filter	Displays the filter formula that is applied to the dimension.
Sort Type	Displays the sort type that is applied to the dimension's values.
Sort #1	If the dimension values are sorted by key, displays the first key field by which the dimension member values are sorted.
Sort #1 Order	If the dimension member values are sorted by the first key, displays whether the sort is by ascending or descending order.
Sort #2	If the dimension member values are sorted by key, displays the second key field by which the dimension member values are sorted.
Sort #2 Order	If the dimension member values are sorted by a second key, displays whether the sort is by ascending or descending order.
Sort #3	If the dimension member values are sorted by key, displays the third key field by which the dimension member values are sorted.
Sort #3 Order	If the dimension member values are sorted by key, displays whether the sort is by ascending or descending order.