PeopleSoft Enterprise General Ledger 9.1 PeopleBook

Using OLAP Tools to Analyze General Ledger Data

This chapter provides an overview of online analytical processing (OLAP) cubes and discusses how to:

Plan the OLAP database.
Prepare trees and queries.
Define the cube.
Build the cube.
Analyze cube data.

Understanding OLAP Cubes

When you analyze PeopleSoft Enterprise General Ledger data, you can look at it from several perspectives: by company, division or business unit, product line, or time. If you use the columns or rows on a typical spreadsheet to analyze PeopleSoft data, it is difficult to represent more than two dimensions (or attributes) at a time.

The OLAP tools that integrate with PeopleSoft products offer a solution for viewing PeopleSoft data with three or more dimensions. You can think of the OLAP tools as an arrangement of data in a cube, with each side of the cube representing a different dimension.

To view data from different perspectives, you can pivot or reposition the cube.

You can use different combinations of dimensions to slice and dice the data. For example, you could examine ledger balances for all travel and expense accounts in the training department of the western region for the entire year. You could slice off part of the cube or dice it to access an individual cell or perhaps to view a single business unit. These multidimensional views of financial data can provide valuable information for effective data analysis, decision making, and forecasting.

Before you set up General Ledger for OLAP tools, you should be familiar with general OLAP concepts and terminology, as well as the use of PeopleSoft Query, Tree Manager, and Cube Manager.

You use PeopleSoft Cube Manager to build multidimensional databases of information. PeopleSoft Cube Manager integrates with the following OLAP tools:

Hyperion Essbase.
Generic Star Schema.

After the General Ledger data is integrated with the third-party OLAP tool of your choice, you can use the OLAP tool to analyze and interact with data.

Note. All OLAP objects delivered with General Ledger are for demonstration purpose only. This includes queries, trees, dimensions, cube definitions, and general-ledger-specific run control pages. You must design your OLAP objects according to your business needs and should use the delivered sample objects only as a reference.

Planning the OLAP Database

Integrating OLAP tools with General Ledger data begins with a careful examination of the data that you want to report on and analyze by using OLAP. You must define specific goals and determine the results that you need from online data analysis.

Preparing Trees and Queries

After defining goals, you must design the PeopleSoft trees and queries that are appropriate for creating both the structure and data of the OLAP database (the cube) that you plan to build.

To map the ledger query result to the cube, each column of the query becomes either a dimension or a measure for the cube. For each dimension, specify how detail values roll up to higher levels. For ChartFields, such as Department ID, you can use existing department trees for the roll-up, or you can create additional trees or queries for use with OLAP. In addition, you must define a query to extract the data from the PeopleSoft database and add it to the OLAP cube.

General Ledger comes with the following queries and trees. You can use them as delivered or modify them.

General Ledger Queries

OLAP_LEDGER_ACT_01

OLAP_LEDGER_BUDG_01

OLAP_LEDGER_02

OLAP_ACCOUNT_FLIPSIGN

OLAP_ACCTREE_FLIPSIGN

General Ledger Trees

ACCTROLLUP

PRODUCT

DEPARTMENTS

ACCOUNTING PERIOD

Defining the Cube

After you define the queries and trees to use with the cube, define the basic structure of the cube itself by using PeopleSoft Cube Manager.

PeopleSoft Cube Manager links the data source (a query) and the dimensions of the OLAP cube that you are creating. You use PeopleSoft Cube Manager 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:

Define the dimensions of the cube.

Use the Dimension page and its related pages to begin building the structure of each dimension that comprises the cube. You define the sources, tree, query, (or both tree and query), 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.

PeopleSoft delivers the following sample dimensions and you can use them as delivered or modify them as needed:
- GL_ACCOUNT
- GL_ACCOUNT_RS
- DEPARTMENT_FUNC
- PRODUCT_MFG
- PERIOD
Create a cube definition that defines the basic structure of the cube.

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 definitions are platform independent and can be reused.

PeopleSoft delivers the following sample cube definitions. You can use them as delivered or modify them as needed:
- LEDGER_DETAIL
- LEDGER_VARIANCE
Create a cube instance definition.

A cube instance is the output of PeopleSoft Cube Manager. It is a physical cube that is built by the Cube Builder process in Essbase, or generic star schema OLAP. The definition of a cube instance creates the link between the platform-independent definition of the cube and the actual physical storage mechanism of the cube. This step tells PeopleSoft Cube Manager where to create the cube that you have defined.

PeopleSoft delivers the following sample cube instances. You can use them as delivered or modify them as needed:
- LEDGER_DET_EB
- LEDGER_DET_PP
- LEDGER_VAR_01

Building the Cube

This section provides an overview of cube building and discusses how to build a ledger cube.

Understanding Cube Building

To build a cube, you have a choice of two pages, which run a background process that extracts hierarchies and data from General Ledger:

The Build Cube Request page in Cube Manager (described in the Enterprise PeopleTools PeopleBook: PeopleSoft Cube Manager).
The Build Ledger Cubes Request page in General Ledger.

To build the cube from the request page, use the Build Ledger Cubes component (RUN_GL_OLAP_LED).

The Build Ledgers Cubes Request page runs the background process that creates ledger cubes. You can also use this page as a template for creating a specialized request page for a different type of cube.

Page Used to Build a General Ledger Cube

Page Name	Definition Name	Navigation	Usage
Build Ledger Cubes Request	GL_OLAP_LED_REQ	General Ledger, General Reports, Build Ledger Cubes, Build Ledger Cubes Request	Build an OLAP cube that displays ledger information or create a template for building other cubes.

Building a Ledger Cube

Access the Build Ledger Cubes Request page (General Ledger, General Reports, Build Ledger Cubes, Build Ledger Cubes Request).

Cube Instance ID

Select the cube template to build the cube.

Post-Build Script

If you use Essbase, OLAP Services, or PS/ROLAP, use this field to indicate the script that you want to run on the cube after the system builds and populates it.

Ledger

Select the ledger for which you are building this cube.

From Fiscal Year and To Fiscal Year

Enter the beginning and ending fiscal years for this cube.

From Period and To Period

Enter the beginning and ending period for this cube.

Meta-Data Action

Select the period over which an accumulator collects and accumulates amounts. Values are:

Create: Create the cube. If a cube already exists, then PeopleSoft Cube Manager recreates it, overwriting any dimensions and data that previously existed.

None: Do not change the structure of the cube or its individual dimensions. (Use to update data only.)

Update: Update the structure of the cube according to the met-data update action. This field value is linked to the Meta-Data Action field on the Essbase Cube Instance Definition page.

Data Action

Select the action that takes place when updating the cube. Values are:

Create: Completely reload the data and overwrite any existing data.

None: Not applicable in General Ledger.

Update: Update the existing data. For Essbase, this option is linked to the data load action that is on the Essbase Cube Instance Definition page.

Analyzing Cube Data

You can use a third-party OLAP tool, depending on the template that you use, to view the cube from the various dimensions that you defined. You can look at the data in numerical or graphical format.

You can pivot, reposition, and manipulate the data and create graphs and charts.

Cube Instance ID	Select the cube template to build the cube.
Post-Build Script	If you use Essbase, OLAP Services, or PS/ROLAP, use this field to indicate the script that you want to run on the cube after the system builds and populates it.
Ledger	Select the ledger for which you are building this cube.
From Fiscal Year and To Fiscal Year	Enter the beginning and ending fiscal years for this cube.
From Period and To Period	Enter the beginning and ending period for this cube.
Meta-Data Action	Select the period over which an accumulator collects and accumulates amounts. Values are: Create: Create the cube. If a cube already exists, then PeopleSoft Cube Manager recreates it, overwriting any dimensions and data that previously existed. None: Do not change the structure of the cube or its individual dimensions. (Use to update data only.) Update: Update the structure of the cube according to the met-data update action. This field value is linked to the Meta-Data Action field on the Essbase Cube Instance Definition page.
Data Action	Select the action that takes place when updating the cube. Values are: Create: Completely reload the data and overwrite any existing data. None: Not applicable in General Ledger. Update: Update the existing data. For Essbase, this option is linked to the data load action that is on the Essbase Cube Instance Definition page.