Using Online Analytical Processing

When you analyze sales order data, you probably want to look at it from several perspectives, by customer group, product group, sales region, and so on. You might think of a dimension as a column or row on a spreadsheet; however, since a spreadsheet is only two-dimensional, you would have a hard time representing customers, products, sales regions, and time using a single spreadsheet. Therefore, when data has three or more dimensions, we refer to it as being arranged in a cube, with each side representing a dimension. Once you have defined 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. 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 your financial data provide you with valuable information for effective data analysis, decision-making, and forecasting.

Preparing Trees and Queries

After you have determined the dimensions for the cube, you need to set up the way each of the dimensions will roll up. You also need to specify how detail values roll up to higher levels for each dimension. You can use existing trees or queries with OLAP. In addition, you need to define a query to extract the data from your PeopleSoft database to the OLAP cube.

PeopleSoft Order Management provides sample queries and trees. You can use them as delivered or modify them as you like. This list describes the queries and trees delivered with PeopleSoft Order Management. There is one main data query input file used for margin analysis, and three dimension queries. A tree, Sales Regions, is used as a fourth dimension.

Tree/Query	Name	Function
OLAP_OM_MARGIN_DATA	Margin analysis data query	Main data query that pulls information from six key tables: sales order header, sales order line, customer, customer group, product group, and product costing. It selects order lines that are in open or closed status to estimate the revenue and cost information for the Inventory product-costing file.
OLAP_OM_CUSTOMER_DIM	Customer dimensional query	Query over the Customer Group Link table to view margin information by customer or customer groups.
OLAP_OM_ORDER_DIM	Order number dimensional query	Query over the Order Group Table to view the margin information by order group or order number.
OLAP_OM_PRODUCT_DIM	Product dimensional query	Query over the Product Group Link table to view margin information by product group or product.
Sales Region Tree	Hierarchy of Sales Regions	Hierarchy of Sales Regions to view margin level at different sales region levels.

Designing Dimensions

The Dimension page and its component pages enable you to define the sources, tree, query, or tree and query combination, and associated settings for dimensions. You will have one entry for each dimension in your cube. At this point, you are only beginning to build the structure of each dimension that will comprise your cube. You are not yet defining any data for the cube.

Oracle delivers some sample dimensions with PeopleSoft Order Management. You can use the dimensions as delivered or modify them as you like. The list details the dimensions provided:

Customer Group (OLAP_OM_CUSTOMER_DIM).
Order Group (OLAP_OM_ORDER_DIM).
Product Group (OLAP_OM_PRODUCT_DIM).
Sales Regions (SALES_PERSONS).

Designing Analysis Models

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

We deliver a sample Analysis Model with PeopleSoft Order Management. You can use the Analysis Model as delivered or modify it as you like.

No.	Description	Type	Field
1	All Customers	Dimension	Sold To Customer
2	All Orders	Dimension	Order Number
3	All Products	Dimension	Product ID
4	All Sales Regions	Dimension	Sales Person
5	Profit	Measure	Expression: Net PriceQty Ordered - Cost Qty Ordered (list price quantity ordered - cost quantity ordered)
6	Total Net	Measure	Expression: Total Net/Cost*Qty Ordered (total net or cost quantity ordered)
7	List Price	Measure	List Price
8	Total List	Measure	Expression: List Price* Qty Ordered
9	Net Price	Measure	Price on Order Line
10	Quantity Ordered	Measure	Quantity Ordered on Order Line
11	This Level Cost	Measure	Cost from Product Cost Table
12	Total Cost	Measure	Expression: Cost* Qty Ordered

Designing 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 Essbase and PowerPlay.

The Customer and Product Profitability Cube Template that is delivered as a sample Cube Template with PeopleSoft Order Management enables you to see profitability levels by product, customer, and market. By adding four dimensions to the cube, the Cube Template enables managers to quickly and easily see the profit margins by sales regions, customer groups, order groups, and products by slicing and dicing sales order information multiple ways. In addition, the Cube Template has eight different measurements so that you can view profitability from the overall profit of the product, down to the cost of the product. The Cube Template provides a ready-made tool to empower management, as well as the sales force, to make pricing and sales decisions based on the direct profit contribution in order to maximize profitability.

You can use the template as delivered or modify it as you like.

Viewing and Analyzing Cube Data

Once you have built your cube, you can use PowerPlay to view the cube. You can pivot, reposition, and manipulate your data, as well as create graphs and charts. You can view margin information for customers, customer groups, sales regions, products, and product groups, and rank your most profitable products.

Page Name	Definition Name	Usage
Build Margin Analysis Cube	OM_OLAP_MAR_REQ	Create the cube for analysis.

See the product documentation for