Using OLAP Tools to Analyze Data

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

Prepare trees and queries.
Define cubes.
Process inventory turns.
View and analyze cube data.

Understanding OLAP Tools

Multidimensional analysis, called OLAP, refers to online information access that you use to view data from different perspectives. You identify the dimensions (attributes) of the data and then combine those dimensions in various ways. For example, when you analyze inventory data, you probably want to look at it from several perspectives, such as time period, business unit, or item group. You might think of a dimension as the columns or rows on a spreadsheet. However, because a spreadsheet is only two dimensional, you would have a hard time representing business unit, item status, and demand frequency using a single spreadsheet. Therefore, when data has three (or more) dimensions, PeopleSoft refers to it as being arranged in a cube, with each side representing a dimension.

After you define an OLAP cube, you can pivot your data to view it from different perspectives, or “slice and dice” the data based on combinations of these dimensions. For example, you could examine the inventory balances and historical usage over the last 18 months. When you analyze the data, you “slice” off part of the cube or “dice” it to get to an individual cell. These multidimensional views of the data provide you with valuable information for effective data analysis, decision making, and forecasting.

You use the PeopleSoft Cube Manager to build multidimensional databases of information. PeopleSoft Cube Manager integrates with OLAP tools, which you use to analyze multidimensional data. The Inventory Turns report shipped with PeopleSoft Inventory uses Cognos PowerPlay.

The following steps for creating cubes assume that you are familiar with general OLAP concepts and terminology, as well as with the use of PeopleSoft Query and the PeopleTools Tree Manager. You should also be familiar with Cognos PowerPlay. After you integrate PeopleSoft Inventory data with PowerPlay, use PowerPlay to analyze and interact with the data.

To extract information from PeopleSoft Inventory that creates multidimensional hierarchies and cubes:

Plan the OLAP database.

Integrating OLAP tools with PeopleSoft Inventory data begins with examining the data that you want to report and analyze it through OLAP.

Define the specific goals and results that you expect from online data analysis.
Prepare trees and queries.

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.
Define the cube.

Use the PeopleSoft Cube Manager to create the link between the data source (a query) and the dimensions of the OLAP cube that you create. Specify how dimension data should roll up using queries, trees, or both. With PeopleSoft Cube Manager, you can reuse and easily modify the design of the dimensions, analysis model, and cube templates.
Build the cube.

After running the background process to extract the hierarchies and data from PeopleSoft Inventory, verify that the resulting cube contains the information that you want.
Analyze cube data using the new cube.

See Also

Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Cube Manager

Preparing Trees and Queries

After determining dimensions for the cube, specify how detail values roll up to higher levels for each dimension. For ChartFields, such as department ID, you can use existing department trees for the roll-up. Alternatively, you can create additional trees or queries for use with OLAP. You must also define a query to extract the data from the PeopleSoft database to the OLAP cube.

PeopleSoft provides sample queries that you can use or modify. The following list describes the queries and trees.

Query	Name	Function
OLAP_INV_DATA	Inventory turns data query	Main data query that contains the periodic inventory item extracts of quantity on hand, demand quantity, and unit cost. Use these values to analyze inventory movement and inventory turns.
OLAP_INV_BU_DIM	Business unit dimensional query	Defines the relationship between the PeopleSoft Inventory business unit and the items within that unit.
OLAP_INV_ITEMGROUP_DIM	Item group dimensional query	Defines the relationship between the item group and the items within the item group. Queries the item group to view inventory movement by item group.
OLAP_INV_ITEMSTATUS_DIM	Item status dimensional query	Defines valid item status values. Queries the business unit item status to view inventory movement by item status.

See Also

Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Cube Manager

Defining Cubes

After defining queries and trees used in conjunction with your cube, define the cube itself. Using this process, you can:

Map queries and trees to dimensions.
Define data-extraction query.
Determine how the cube is built.
Define platform-specific PowerPlay options.

Dimensions

Use the Dimension page and its associated pages to define the sources, tree, query (or tree and query), and associated settings for dimensions. There is one entry for each dimension in your cube. You are beginning to build the structure of each dimension of your cube but not defining any data for the cube.

Sample dimensions delivered with PeopleSoft Inventory include:

Business Unit (OLAP_INV_BU_DIM)
Item Group (OLAP_INV_ITEMGROUP_DIM)
Item Status (OLAP_INV_ITEMSTATUS_DIM)

Analysis Models

After creating data source queries and defining dimensions, define the analysis model by mapping fields from the data source queries to the dimensions that you defined. For each dimension, specify which field in the data source query is the source field for the particular dimension. You can also define measures that were used in the cube on this page.

This table lists the sample analysis model that PeopleSoft delivers:

No	Description	Type	Field
1	All IBUs	Dimension	IBU
2	All item groups	Dimension	Item Group
3	All item statuses	Dimension	Item Status (Active, Inactive, Hold, Discontinue, Under Initialization, Pending Approval, Denied Approval)
4	Quantity on hand	Measure	Quantity on hand when extract is taken
5	Monetary value on hand	Measure	Expression: on-hand qty x unit cost
6	Demand quantity	Measure	Summed demand qty over reporting period
7	Demand quantity monetary value	Measure	Expression: demand qty x unit cost
8	Item cost	Measure	Unit cost

Cube Templates

A cube template simplifies your work in managing updates to the cube structure, data loads, and reloads and in setting platform-specific options for PowerPlay. PeopleSoft delivers a sample cube template with PeopleSoft Inventory called INVTURNS.MDC. Using the Inventory Movement and Turns cube template, you can view inventory movement by item, item group, and item status.

By adding four dimensions to the cube, you can view the inventory movement performance by slicing and dicing inventory balance and movement information in multiple ways. The cube template provides a ready-made tool to empower management to obsolete, expedite, or increase the order quantities for inventory items, helping to reduce inventory carrying costs and improve service levels.

See Also

Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Cube Manager

Processing Inventory Turns

The Inventory Turns Load process (INVTURNS) pulls all of the item movement, quantity on hand, and cost data into one table, where you can use it for cube building. You can run the process periodically to ensure that quantity on hand and demand quantity data snapshots are available for analysis.

PeopleSoft Inventory also provides a sample page to help you build cubes. Use the Inventory Turns Cube Build page to run the background process that creates the cube. You can use the Build Request page in PeopleSoft Cube Manager to build cubes, but you will probably use specialized request pages for different kinds of cubes. After you run this process, you can access PowerPlay to view the cube that you created.

This section discusses how to:

Run the Inventory Turns Load process.
Run the Inventory Turns Cube Build process.

Pages Used to Process Inventory Turns

Page Name	Object Name	Navigation	Usage
Inventory Turns Load	RUN_INV_TURNOVER	Inventory, Manage Inventory, Analyze Inventory, Load Inventory Turns, Inventory Turns Load	Set up parameters for the Inventory Turns Load process.
Inventory Turns Cube Build	IN_OLAP_TRN_REQ	Inventory, Manage Inventory, Analyze Inventory, Build Inventory Turns Cube, Inventory Turns Cube Build	Define a run control ID to use with PeopleSoft Process Scheduler.

Running the Inventory Turns Cube Build Process

Access the Inventory Turns Cube Build page.

Cube Instance ID	Specify the instance that you want to use as the basis for the cube.
Open Cube Instance Definition	Click the button next to the Cube Instance ID field to access the Cub Inst Def (cube instance definition) page.
Business Unit	Enter the PeopleSoft Inventory unit that you want to use for the cube data.
As of Date	The cube building process pulls all activity up until the date that you enter.
Post-Build Script	You can run a Cognos PowerPlay MDL script at the end of the build process. This feature extends the capabilities of PeopleSoft Cube Manager. For example, you can run a script to set up Cognos PowerPlay security for users who need to access the cube directly through Cognos PowerPlay rather than PeopleSoft Inventory. Then, rather than having to configure security manually each time that you build a cube, write a script once and use the Post-Build Script to run it for each build.
Actions	Define the actions that you want. Values include: Create: Select in Meta-Data Action group box to create the cube. If the cube already exists, the build process recreates it, overwriting any existing dimensions and data. Select in the Data Action group box to reload data completely, overwriting all existing data. Update: To build the cube on Cognos PowerPlay, Update has the same effect as Create. None: Select in the Meta-Data Action group box to prevent changes to the structure of the cube or its individual dimensions (you might want to update the data only). Select in the Data Action group box to prevent changes to the data in the cube (you might want to update the structure only).

See Also

Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Cube Manager

Viewing and Analyzing Cube Data

After building a cube, use PowerPlay to view it. You can pivot, reposition, and manipulate data and create graphs and charts. To use Cognos PowerPlay, see the PowerPlay documentation. Here is an example of a typical cube viewed in PowerPlay:

Sample cube viewed in PowerPlay: analyze inventory balances and historical usage using OLAP reporting