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 and build the cube.

Understanding 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 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 Builder.

See PeopleSoft PeopleTools PeopleBook: PeopleSoft Cube Builder

Note. PeopleSoft no longer supports the Cube Manager functionality. PeopleTools Cube Builder, which is available with the latest PeopleTools release, is the recommended solution for creating and maintaining analytic data stores with PeopleSoft. You can, however, continue using this functionality by altering the Content Ref Administration page to show the Build Ledger Cubes menu item.

For more information:

See PeopleSoft Application Fundamentals 9.1 PeopleBook: Preface, "Using OLAP to Analyze PeopleSoft Financials/Supply Chain Management Data."

After the General Ledger data is integrated with Hyperion Essbase, 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, and other cube definitions. 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 rollup, 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, modify them, or create your own.

Defining and Building the Essbase Cube

The following process flow illustrates the Cube Builder business processes:

Note. Oracle Essbase, SmartView, and other Oracle Hyperion tools are not delivered as part of the PeopleSoft application; therefore, the PeopleSoft Cube Builder PeopleBook does not include documentation about 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 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. Access the Connections page (PeopleTools, Cube Builder, Connections, Connections):

   See PeopleTools 8.5x PeopleBook: PeopleSoft Cube Builder, Defining Essbase Cube Builder Connection Definitions.

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.

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

   • GL_ACCOUNT

   • ACCOUNTING_PERIOD

   • DEPARTMENT

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

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 following sample cube outlines to review:

   • LEDGER_ACTUALS

   • LEDGER_BUDGETS

   • LEDGER_ACCTDEPT

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

4. Create a Dynamic Dimension Template. A dynamic dimension template is a set of default values to be used automatically for dynamic query and tree members. Because dynamic objects are not stored within the dimension, using a template enables the dimension builder to define what properties would be applied to the majority of members within the dynamic query and tree.

   See PeopleTools PeopleBook: PeopleSoft Cube Builder, Creating Dynamic Dimension Templates.

5. Build an Essbase Cube using the Create Cube page (PeopleTools, Cube Builder, Essbase Cube Builder, Create Cube). Use the Create Cube page to tie the cube components together and run the process that sends all of the defined information to the Essbase database to build the Essbase cube.

   See PeopleTools PeopleBook: PeopleSoft Cube Builder, Building Essbase Cubes.