Using OLAP Tools to Analyze General Ledger Data

This section provides an overview of using online analytical processing (OLAP) cubes with PeopleSoft General Ledger and discusses how to:

See Also

PeopleTools PeopleBook: PeopleSoft Cube Manager

Click to jump to top of pageClick to jump to parent topicUnderstanding OLAP Cubes

When you analyze PeopleSoft 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, 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 PeopleSoft 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. Cube Manager integrates with the following OLAP tools:

PeopleTools comes with Cognos PowerPlay, which provides a standard OLAP cube template for use in PeopleSoft General Ledger. This makes it possible to design and build OLAP databases (referred to as cubes) for multidimensional analysis and reporting of general ledger data.

After the PeopleSoft 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 PeopleSoft General Ledger are for demonstration purpose only. This includes queries, trees, dimensions, cube definitions, and GL 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.

Click to jump to top of pageClick to jump to parent topicPlanning the OLAP Database

Integrating OLAP tools with PeopleSoft 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.

Click to jump to top of pageClick to jump to parent topicPreparing 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, you 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.

PeopleSoft 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

Click to jump to top of pageClick to jump to parent topicDefining 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.

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

  1. 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:

  2. 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.

  3. Create a cube instance definition.

    A cube instance is the output of Cube Manager. It is a physical cube that is built by the Cube Builder process in PowerPlay, Essbase, OLAP Services, or PS/ROLAP. 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 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..

Click to jump to top of pageClick to jump to parent topicBuilding the Cube

This section provides an overview of cube building and discusses how to build the aging analysis cube.

Click to jump to top of pageClick to jump to parent topicUnderstanding Cube Building

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

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.

Click to jump to top of pageClick to jump to parent topicPage Used to Build a General Ledger Cube

Page Name

Object 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.

Click to jump to top of pageClick to jump to parent topicBuilding a Ledger Cube

Access the Build Ledger Cubes Request page.

Cube Instance ID

Select the cube template to build the cube.

Post-Build Script

If you use Essbase, PowerPlay, 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 and To Fiscal Year

The beginning and ending fiscal years for this cube.

From and To Period

The beginning and ending period for this cube.

Meta-Data Action

The period over which an accumulator collects and accumulates amounts. Select one of the following values:

Create: Create the cube. If a cube already exists, then 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 metadata update action. For PowerPlay, this has the same effect as Create. This field value is linked to the Meta-Data Update Action field on the Essbase Cube Instance Definition page.

Data Action

The action that takes place when updating the cube. Select one of the following values:

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

None: Not applicable in PeopleSoft 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. This option does not apply to PowerPlay.

Click to jump to top of pageClick to jump to parent topicAnalyzing Cube Data

You can use PowerPlay (or another 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.