Defining and Building the Essbase Cube

Page Name

Definition Name

Usage

Essbase Load Setup Options Page

GL_ESSBASE_SETUP

Specify the Outline Name, Essbase Connection ID, and other parameters of the Essbase cube for subsequent or incremental loading of cube data.

Essbase Load Request Page

GL_ESS_LOAD_REQ

Run the Essbase Load process for either incremental load or period load of ledger data to an Essbase cube.

This diagram illustrates the high-level Cube Builder process flow:

Building the Essbase Cube Process Flow

 Essbase Cube Process Flow

Note: Oracle Essbase, Smart View, and other Oracle Hyperion tools are not delivered as part of the PeopleSoft application; therefore, the PeopleSoft Cube Builder documentation does not include information regarding how to use Oracle Hyperion applications. It does, however, include a reference to the appropriate documentation.

After you define the queries and trees (metadata) to use with the cube, define the basic structure of the cube itself.

Use PeopleTools Cube Builder to build the initial Essbase cubes:

  • Load cube metadata using Trees and dimension queries.

  • Load incremental metadata from updated Trees and dimension queries.

  • Load transaction data.

  • Load incremental transaction data, adding appropriate criteria (DateTime stamp) in the query.

For more information, see PeopleTools: PeopleSoft Cube Builder, Cube Builder Implementation.

PeopleSoft General Ledger provides the following for loading subsequent or incremental data for Essbase cubes:

  • Essbase Load Setup Options page to define parameters for loading transaction data.

  • Incremental transaction data load through the regular Journal Posting process.

  • Essbase Load Request page to load transaction data incrementally or reload transactions.

  • Recurring process can be scheduled for incremental load using parameters from the Essbase Load Setup page and log file.

The Post Journals Request Page includes an option to skip Essbase incremental load, if needed.

Defining the Cube

After you have defined the queries and trees (metadata) to use with the cube, define the basic structure of the cube itself and build the cube by using PeopleSoft Cube Builder.

PeopleSoft Cube Builder links the data source (a query or tree) and the dimensions of the OLAP cube that you are creating. You use PeopleSoft Cube Builder to specify how dimension data should roll up by using queries, trees, or both queries and trees. As needed, you can reuse and easily modify the design of the dimensions, cube definition, and cube instance definitions.

To define the cube:

  1. Define the connectivity information (Owner ID - General Ledger). Access the Connections page (PeopleTools, Cube Builder, Connections, Connections).

    See PeopleTools: PeopleSoft Cube Builder, Defining Essbase Cube Builder Connections.

  2. Define the dimensions of the cube. Access the Dimensions component (PeopleTools, Cube Builder, Dimensions, Dimension).

    Use the Dimension component to build the structure of each dimension that comprises the cube. You define the dimension type, object owner, and dimension structure (sources of data such as trees, queries, (or both trees and queries), and other settings for dimension data. You do not define any data for the cube at this point. You must create one entry for each dimension in the cube.

    Note: PeopleSoft does not support duplicate member names when defining blank members. When assigning blank members in the dimension, the member name should be unique (not the same as the parent name), otherwise the data for the blank member is not loaded into the Essbase cubes.

    Note: Use caution when setting up Essbase using the default value for invalid members. Once information is loaded into the default values, it cannot be backed out from the GL load process once the invalid members are added in Essbase. This is because ledger data is loaded into cubes using the Replace aggregation option. This option only updates corresponding rows or inserts new rows in the cube. Once the invalid members are corrected, ledger data will be loaded into the cube and will not be able to replace the information in the default member. To correct this, you must manually reset the default member value.

    Review some of the PeopleSoft delivered sample dimensions before creating your own, such as:

    • GL_ESS_BUSUNIT

    • GL_ESS_LEDGER

    • GL_ESS_ACCOUNTS

    • GL_ESS_DEPARTMENT

    • GL_ESS_FC_PER

    • GL_ESS_CURRENCY

    Note: Be aware of set ID’s when loading metadata and comparing between business units in Essbase. A ChartField value may be the same but if it has a different SetID, it is not a match.

    See PeopleSoft PeopleTools: PeopleSoft Cube Builder, Creating Dimensions for Essbase Cubes.

  3. Create a cube outline that defines the basic structure of the cube. Access the Cube Outline component (PeopleTools, Cube Builder, Outlines, Cube Outline).

    Select the dimensions and measures that make up the cube and the data source queries that populate the members and cube cells with data. Like dimensions, cube outlines are platform independent and can be reused.

    PeopleSoft delivers the sample cube outline GL_ESS_LEDGER to review as a model for building your own outline.

    See PeopleSoft PeopleTools: PeopleSoft Cube Builder, Defining Essbase Cube Outlines.

PeopleSoft delivers the Essbase Load Setup Options component to register the GL Essbase setup that stores the relationships between PeopleTools Cube Builder and General Ledger when loading data. Only one measure is supported by PeopleTools Cube Builder; therefore, cube outlines may be defined for the base amount measure and another cube outline for foreign amounts, if needed.

Use the Essbase Load Setup Options page (GL_ESSBASE_SETUP) to register the GL Essbase setup that stores the relationships between PeopleTools Cube Builder and General Ledger when loading data. Specify the Outline Name, Essbase Connection ID, and other parameters of the Essbase cube for subsequent or incremental loading of cube data.

Navigation:

General Ledger > General Reports > Setup Essbase Load

This example illustrates the fields and controls on the Essbase Load Setup Options page.

Essbase Load Setup Options page

Field or Control

Description

Outline Name

Select the Essbase Cube Outline ID that you want to use for the data load. The outline defines the basic structure of the cube, including dimensions and data queries.

Essbase Connection ID

Select the Connection ID that you have established.

Application Name

Essbase application name where cube resides.

Database Name

Essbase database name to be used where cube resides.

BU Ledger Setup

Field or Control

Description

Incremental Load

Select this check box to load data incrementally. Deselect the check box for period load.

Note: For the incremental load, queries must be based upon the GL_ESS_QRY_VW view to ensure that the ledger rows are selected correctly.

The query that is used when loading ledger data for the Fiscal Year and Accounting Period (when the Incremental Load check box is deselected) can be used to load initial ledger data as well as reloading data for the specified period.

Since requirements vary in every organization, you can use the provided sample query as a basis to build your own requirements.

Use the Essbase Load Request page (GL_ESS_LOAD_REQ) to run the Essbase Load process for either incremental load or period load of ledger data to an Essbase cube.

Navigation:

General Ledger > General Reports > Essbase Load Request > Load Essbase Cube Data

This example illustrates the fields and controls on the Essbase Load Request page.

Essbase Load Request page

The Essbase Load process performs either incremental load or period load of ledger data. This initiates a separate Create Cube process for every outline name in your Selection Criteria using parameter values from the run control as well as the Essbase Load Setup Options page.

You can also call this process from the Post Journals Request Page for incremental load.

Field or Control

Description

Incremental Load

Select to process an incremental load.

Deselect to process a period load.

Log level

Enter the level of logging to be used while the build cube process is running. The default value is 1 for basic logging.

Load from Essbase Load Setup

Load all rows that are defined in the Essbase Load Setup Options page.

Incremental Load (check box selected)

For incremental load, the DateTime stamp at the start and end of the Journal Post process is logged for every business unit and ledger group of every corresponding business unit and ledger that is defined on the Essbase Load Setup Options page and designated as incremental. These DateTime values are used during incremental load to select the rows in the Ledger table. Succeeding incremental loads look at the earliest unprocessed DateTime in the Journal Post log to discern whether to process certain instances where Essbase Incremental load had been previously skipped during journal posting.

Period Load (Incremental Load check box deselected)

For period load, all rows in the Ledger table are loaded for the specified business unit, ledger and accounting period in the request. Because the Replace Aggregation load option in the PeopleTools Cube Builder is utilized, previous rows loaded for the same Essbase dimensions are overwritten.

Selection Criteria

Field or Control

Description

Request Number

This value is used when initiating the process to create cubes.

Business Unit and Ledger

Select a business unit and Ledger for processing. Only defined business unit values and defined combinations of ledger and business units from the Essbase Load Setup Options page are allowed.

Outline Name

Select the Essbase Cube outline. Only defined outline names from the Essbase Load Setup Options page are allowed.

Fiscal Year

This field appears only when the Incremental Load check box is deselected. Select the fiscal year for which to load data. This is used only for period load.

Accounting Period

This field appears only when the Incremental Load check box is deselected. Select the accounting period for which to load data. If blank, all accounting periods for the specified fiscal year are included. This is used only for period load. Prompting for this field includes detail periods, adjustment periods that are defined in the calendar, and periods 0 and 999 (used in the year end closing process).

Note: Be sure to run the period load first for all open periods to serve as the starting point for incremental loads. Use the GL Process request so that the DateTime stamp is logged correctly.

The following options can be used to load ledger data into Essbase cubes depending on your requirements or resources:

  • Period load only and no incremental load, using the Essbase Load request page - A period load Essbase request can be scheduled daily, weekly, monthly, and so on.

  • Incremental load only using the Essbase Load request page but run only once daily, weekly and/or monthly - Period load option is required to load initial data not covered when Journal Posting starts creating the logs .Because of the Cube Builder Replace Aggregation load option, the date and time covered can overlap between incremental and period load and the process always selects the latest update from the Ledger table.

  • Incremental load only through Journal Posting - Period load option is required to load initial data not loaded from previous Journal Posting when Essbase incremental was first set up.

  • Combination of incremental load and period load - Incremental load for certain business unit and ledger combinations when data needs to be current most of the time; and do period load for other business unit and ledger combinations that do not have the same immediate need and can wait until another period load request is initiated and processed either on demand or through a scheduled recurring process.

Note: Use the log file that was generated during the Create Cubes process to determine if ledger data was not loaded due to missing dimension members. You must update that metadata.