Building Essbase Cubes

This chapter discusses how to:

Click to jump to parent topicBuilding Essbase Cubes

This section discusses how to create Essbase cubes:

Click to jump to top of pageClick to jump to parent topicCreating Essbase Cubes

Access the Create Cubes page (CUB_RUNCNTL) by selecting PeopleTools, Cube Builder, Essbase Cube Builder, Create Cube.

Use the Create Cubes page to run the process that sends all of the defined information to the Essbase database to build Essbase cubes.

Outline

Displays the outline that is used to create this cube.

Essbase Connection ID

Displays the Essbase connection ID that is used to create this cube.

Application Name

Displays the Essbase application name that is used in the Essbase system to create the online analytical processing (OLAP) cube.

Database Name

Displays the database name that is used in the Essbase system to create the OLAP cube.

Meta-Data Update Action

Select an update action to use to build the metadata (the cube shape). The selected update action controls what to do in case this cube creation process is not the first time the cube is built.

Available options are:

  • Create or Re-create: Create or recreate metadata from the beginning.

  • Incremental Replace All: Update metadata incrementally and remove unspecified members.

  • Incremental Update: Merge updates incrementally with existing metadata.

  • No Action: Do not perform an action on the metadata.

Restructuring Options

Select an option to build the data (the cube contents).

Available options are:

  • Discard all data: Discard all previously loaded or manually modified data.

  • Keep all data: Do not modify the data.

  • Keep only input data: Keep only input data or data blocks created by data loading.

  • Keep only level 0 data: Delete upper level data incrementally.

  • Reload all data: Reload all data from its source.

Aggregations on Load

Three available options are:

  • Replace

    Select this option to overwrite or replace the currently stored value in the data cell with the value being passed. Basically, if the data file has multiple rows with the same dimensional intersection, then the last value in the data cell is stored and the previous ones are ignored.

  • Add

    Select this option to add all the values for the stored data cell to the currently stored value. Use this option for instances in which loads are always additive, for example, an implementation that loads transactions to an already stored summary amount.

  • Subtract

    Select this option to subtract the values passed from the currently stored value. This option is rarely used, but you can use it to perform an additive load that then needs to be backed out.

See Aggregations on Load Tool.

Log Level

Enter the log level that pertains to the level of logging while the process is running.

These are the logging levels that can be selected to view specific messages or errors:

  • 0 (eRequisite): no logging.

  • 1 (eBasic): basic logging.

  • 2 (eDetail): detailed logging.

  • 4 (eDimensional): single dimension logging.

  • 8 (eFactMapping): single factory logging.

  • 16 (eAPITrace): Essbase APT traces.

For example, to see logging for Single Dimensions and Essbase APT, enter 20 (sum of eDimensional and eAPITrace = 4 + 16 = 20). To see all available logs, enter 31 (1 + 2 + 4 + 8 + 16 = 31).

Dimension

Select a dimension as the single dimension that loads data.

This selection sets the data update action to No Action. The only action that will occur in the current run is for the structure of dimension to be rebuilt. No data will be loaded.

Query

Select a query as the single query that loads data.

This selection sets the metadata action to No Action. While the process is loading in a single query, no structural changes are made to the cube.

Note. You should not provide both a dimension and a query because this selection would cause no actions to occur in the current run of the cube builder.

Last Update Dttm (last update date and time)

Displays the date and time that the cube creation run control ID was last modified.

Run

After saving the defined information, click this button to run the batch program.

See Also

Submitting and Scheduling Process Requests

Click to jump to parent topicIntegrating with Oracle Smart View

Using Oracle Smart View for Microsoft Excel, you can view the data and metadata of the recently built cube from Process Monitor. To view cube data using Smart View:

  1. Build Essbase cube.

    See Creating Essbase Cubes.

  2. After successfully building an Essbase cube, click the Process Monitor link on the Create Cube page to access the Process List page.

  3. Click the Details link for the selected process instance.

    If an Essbase cube was built successfully, the Open Cube in Smart View link is available on the Process Detail page, as shown in this example:

  4. Click the Open Cube in Smart View link.

  5. If required, enter the user name and password that was used to build the cube.

    If your user name and password are authorized, Smart View displays the top members of the two dimensions created, as shown in this example:

  6. (Optional) Drill down or use the POV Smart View tool to manage the data.

Note. If you modify data or metadata using Smart View, these modifications will affect the cube that is stored in Essbase but will not affect the PeopleSoft tree or query used by PS2Essbase to create the cube.

If you make any modifications using Smart View, these modifications may be overwritten in the next run of PS2Essbase.

If multiple process instances of PS2Essbase were run using Process Scheduler, the Process List page displays only one Open Cube in Smart View link for the last cube built.

Click to jump to parent topicUsing API and Command-line Specifications

You can run PS2Essbase from the Create Cube page using Process Scheduler or the command line.

This table lists the parameters that PS2Essbase uses when PS2Essbase runs from the command line:

Parameter

Description

A, a

Outline Name.

R, r

Run Control ID.

I, i

Process Instance ID number, such as I42, usually passed by Process Scheduler.

CT, ct

Database type (for example, -CTORACLE).

CD, cd

Database name (for example, -CDPT850RC1).

CO, co

Operator ID (for example, -COPTDMO).

CP, cp

Password (for example, -CPPTDMO).

EC, ec

Essbase Connection Name.

OP, op

Log files output destination.

Note. When running PS2Essbase from the command line, in the –OP command line option, you must enclose the output destination patch in quotes. For example, –OP “c:\tmp\ps”.

M, m

Metadata action, such as -mC:

  • N - No action on metadata.

  • C - (Re)create metadata from scratch.

  • U - Incrementally merge updates with existing metadata.

  • R - Incrementally update existing metadata, remove unspecified members.

D, d

Outline Restructure option:

  • N - Keep all data.

  • R - Discard all data.

  • I - Reload all data.

  • D - Keep only level 0 data.

  • A - Keep only input data or data blocks created by data loading.

L, l

Logging level (for example, L31).

X, x (optional)

Dimension name for single dimension execution.

Y, y (optional)

PS Data Query name for single factory execution.

Z, z

Parameter used to load the aggregations on load option. Available options are:

  • R,r - Replace.

  • A,d - Addition.

  • S,s - Subtraction.

Example: the parameters list passed using this command line:

ps2essbase.exe -CX -CTORACLE -CDPT850RC1 -COPTDMO -CPPTDMO -I42 -OP "c:\tmp\ps" -
REMPLOYMENT -aEG_
DIVISION -mC -dR-L15 -XGL_ACCOUNT -YQRY_MEASURES

Logging

You can pass the log level with the L option to the command line of psolap.exe; for example, L2 or L6, and so on.

This table lists the log level and the data recorded in each log level:

Log Level

What is recorded

1

Basic logging

2

Detail logging

4

Dimension member logging

8

Fact map logging

16

API logging

When you run the process using Process Scheduler, the logging level is set on the Create Cube page. When you run the process from the command line, the logging level is set using the –L parameter. To calculate these logging settings, PeopleSoft Cube Builder adds the options that you define. For example: