Understanding OLAPThis chapter provides an overview of Online Analytical Processing (OLAP) and cubes, and discusses how to:
Work with PeopleSoft Cube Manager.
Use supported OLAP tools.
Note. The information presented here is not a substitute for your Cognos PowerPlay, Hyperion Essbase, or other third-party technology documentation, but should help you integrate your PeopleSoft data with one or more of these tools.
See Also
Understanding OLAPMost business software users are familiar with Online Transaction Processing (OLTP) applications, which are for creating and maintaining information about business operations . The transactions that are stored by these applications are the heart of any business software. At the database level, OLTP applications are designed to allow for speedy creation of data and to reduce redundant information. However, data structures of this design are not well suited to analysis.
The nature of OLTP databases poses a problem: how to analyze data in a database that is not designed for analysis. One solution is to use a product such as PS/nVision. Such products perform analysis on selected characteristics of the database. However, using a powerful tool, such as PS/nVision, on top of an OLTP database, takes time.
In contrast, OLAP applications are designed specifically for data analysis. The source of information for analysis is an OLTP database. To make the OLTP data available to analytical applications, data is extracted and transformed into a format that is easier to analyze. You can store the resulting OLAP database in several different formats, depending on the tools that you used to access the data.
Multidimensional OLAP (MOLAP) is a format that stores all of the data hierarchically. This type of database is mainly for small-to-medium data marts. MOLAP databases, such as Cognos PowerPlay and Hyperion Essbase, summarize and navigate data quickly. The only drawback of MOLAP systems is that as dimensional information, sizes, or numbers increase, the storage mechanism becomes less efficient.
Relational OLAP (ROLAP) is a format that stores the analytical data in relational tables. The main benefit of the ROLAP format is its ability to store vast amounts of data. However, ROLAP data storage is not as efficient in accessing aggregate information at higher levels of the hierarchy.
The structures of the data schema can be one of two types: snowflake or star schema. Snowflake schemas are designed to keep very little redundant data, whereas star schemas encourage duplicate data. This release of PeopleSoft Cube Manager supports only the star schema.
In a star schema, each dimension is represented in a single table. The fact data, data that is to be analyzed, is stored in a separate table. The fact table contains one column to represent each of the dimensions from which the data was created.
The following diagram illustrates a typical star schema:
Star schema structure
See Understanding Cubes.
Hybrid OLAP (HOLAP) is the latest type of analytical structure. As the name implies, the data is stored in a hybrid format. The base fact data is stored in a set of relational tables, whereas the summary data is stored in a multidimensional format. This strategy is powerful, but can pose problems when you are combining data from the relational tables with the multidimensional summary data.
Generally speaking, multidimensional analysis is not a technology. Rather, it is a type of analysis that enables you to easily view data from many perspectives. These tools enable you to explore, interact with, and "slice and dice" complex data, guiding you to the multidimensional information that you can't easily discover with conventional reporting tools.
Understanding CubesThe key concept of OLAP is that of a cube. This document uses the term cube when referring to any analytic data store, whether it is a MOLAP cube or a ROLAP star schema. An OLAP cube is a collection of related data—a database—that has multiple dimensions. Cube dimensions are the rough equivalent of fields in a relational database. In terms of data analysis, dimensions can be thought of as criteria—such as time, account, and salesperson—that can pinpoint a particular piece of data. These pieces of data are usually transactions from an OLTP system.
Although they are called cubes, OLAP databases can have more than three dimensions. In fact, most cubes have anywhere from three to eight dimensions. To understand the concept of OLAP cubes, start with a simple data analysis model and then expand it.
Suppose you want to analyze your company’s sales (in units). You could examine the total units sold in a particular year, but that would not help you understand much about your business. Instead, you might want to see unit sales broken down by time and by products. The matrix that you use to analyze this data might look something like the following table, which illustrates a cube with two dimensions:
|
Product |
2001 |
2002 |
2003 |
|
Widgets |
3000 |
6500 |
8200 |
|
Gadgets |
1200 |
1450 |
3000 |
|
Doohickeys |
2500 |
3400 |
2000 |
|
Whatzits |
500 |
670 |
1300 |
In OLAP terminology, the preceding table is an OLAP cube that represents units sold dimensioned by time and product. Time and product are the dimensions of the cube, and units sold is the fact data.
In the example, each dimension is subdivided into categories, called cube members, which represent individual years and products. In the time dimension, the members are 2001, 2002, and 2003. In the product dimension, the members are widgets, gadgets, doohickeys, and whatzits.
In the preceding table, the values of the most interest are not years or products. The purpose of the table is to find the number of units sold. Units sold comprise the data element that is being evaluated or measured. In OLAP terminology, the number of units sold is called the measure, or fact, of this cube. The areas of the table where members intersect with other members represent individual measure and fact values. These intersections are called cells. The italicized cell in the preceding table represents the number of widgets sold in 2002: 6500 units.
The two-dimensional cube in the preceding illustration is basic, for reporting purposes. For example, it provides no data about where any of the units were sold. You can provide this information by adding another dimension, location, to the model:
Illustration of a cube with three dimensions
The preceding three-dimensional OLAP cube represents units sold dimensioned by time, product, and location. (The location members are East, West, Central, and South.) The shaded cell represents the number of widgets sold in the East region in 2002. You could find the number of units sold for any other product in any other region at any other time by finding the cell at the intersection point of three members, one from each dimension.
Suppose you also want to factor customer accounts into the analysis. Although it is a challenge to depict four dimensions graphically, the result of this added dimension is clear. In this case, each cell of the OLAP cube represents the intersection of an account, a year, a region, and a product.
RollupsA rollup is the organization of cube data elements with their reporting structures. It represents both the hierarchy and the method of consolidation in a dimension level.
The example cube has only one level in each dimension. The time dimension consists of one level containing three members (years), the location dimension consists of one level containing four members (regions). However, the data used to build such OLAP cubes probably supports more than just one level in each dimension.
For example, when a company records a sale, that sale occurs in a particular month, which occurs in a particular quarter, and in a particular year. You can examine the time dimension at three levels: month, quarter, or year. Likewise, you could record that each sale occurs in a particular office, in a particular city, in a particular region. The location dimension might also have three levels: office, city, and region.
As mentioned, the categories found at each level of a dimension are called members. You can envision multilevel dimensions as tree diagrams, the members of which relate to each other in various parent/child relationships. Some members are parents of other members, some are children, and some are both. For example, the follow diagram represents a portion of a typical time dimension, with its various levels and members:
Illustration of a time hierarchy
Each box in the diagram represents a unique member. If you have used the PeopleSoft Tree Manager before, this diagram should look familiar. In fact, PeopleSoft trees can play an important role in defining the hierarchy of an OLAP cube.
See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Tree Manager.
Viewing a dimension’s hierarchy tells you about the organization of its members, but there is another facet of the dimension to consider. You need to know how to consolidate the values that are found under child members into the value of their parent members. For example, the children might be added together to equal the parent. This is certainly the case in a time dimension, in which the value for each member is added to its siblings to equal the value of its parent. (Three months can be consolidated into their parent quarter, four quarters can be consolidated into their parent year, and so on.)
The following table illustrates the cube example adding a second level, quarters, to the time dimension of the original example:
Consolidation example
To consolidate the data at the quarterly level into the yearly level, the quarterly data is simply added together. The 2001 rollup is Q1 2001 + Q2 2001 + Q3 2001 + Q4 2001.
However, you also might find dimensions in which certain members are to be subtracted from their siblings, such as in a profit dimension. In such a dimension, suppose there are two members at the first level, margin and total expenses, both of which are reported as positive values. To find the total profits, you would not add margin and total expenses, but would instead subtract total expenses from margin.
Understanding PeopleSoft Cube Manager
This section provides an overview of Cube Manager, and discusses:
Cube Manager architecture.
PeopleSoft metadata.
PeopleSoft Process Scheduler integration.
Designing and building OLAP cubes.
Understanding Cube ManagerIt is possible to build an OLAP database out of PeopleSoft data without using PeopleSoft Cube Manager. In fact, customers have implemented custom OLAP solutions with previous PeopleSoft releases. However, PeopleSoft Cube Manager provides several important benefits when linking between your PeopleSoft application data and your OLAP platform.
PeopleSoft Cube Manager is a PeopleTool used to build OLAP databases or cubes. It provides a framework for modeling your OLAP cubes through its design tools. This framework supports the definition of all dimensions, attributes, measures, and cubes that you might want to build from PeopleSoft sources. You can share all of these definitions across cubes to ensure that OLAP results are consistent across your enterprise.
You can also use components designed in PeopleSoft Cube Manager across all target OLAP platforms supported by PeopleSoft. PeopleSoft Cube Manager provides all these benefits while achieving a nearly platform-independent solution. You can apply the majority of a cube’s design attributes to Hyperion Essbase, Cognos PowerPlay, or Generic Star Schema. PeopleSoft Cube Manager also lets you leverage your existing PeopleSoft metadata to define the cube structure.
Cube Manager ArchitectureThe following diagram illustrates the cube building process:
PeopleSoft Cube Manager: The big picture
The cube building process includes the following steps:
PeopleSoft Cube Manager extracts data from the PeopleSoft application (OLTP) database.
A third-party reporting tool directly analyzes the OLAP data store.
PeopleSoft MetadataMetadata is data that defines data. It conveys information about how data is formatted, structured, and stored. In an OLAP cube, metadata defines the cube’s dimensions, levels, members, member attributes, and their interrelationships. PeopleSoft Cube Manager uses two types of PeopleSoft structures—trees and queries—to help define cubes.
A PeopleSoft tree defines the summarization rules for a database field. It specifies, for purposes of reporting or security access, how the field values are grouped in the system.
For example, the values of the DEPTID field might identify individual departments in your organization. You could build a tree for the DEPTID field, which defines the organizational hierarchy that specifies how each department relates to the others: departments 10700 and 10800 report to the same manager, department 20200 is part of a different division, and so on.
It is easy to see how you can use PeopleSoft trees to define a cube structure. Like cube dimensions, trees consist of levels and members. (In PeopleSoft Tree Manager, members are called nodes and leaves.)
See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Tree Manager.
PeopleSoft queries are SQL statements that are created by PeopleSoft Query. You can use these SELECT statements to return field values based on certain criteria. The standard PeopleSoft security mechanism can secure the data returned by PeopleSoft Query. Also, PeopleSoft Query can return data in any of the database-supported globalized formats.
You can use queries in a number of ways to define an OLAP cube. Use them to define the rollup structure, although this process is not as straightforward as using a tree. You can also use queries to specify member attributes. For example, you can set an attribute that automatically flips the sign of member values—changing a positive number to a negative or vice versa. (This approach is sometimes necessary for accounting purposes.) Finally, you can use queries to populate OLAP cubes with data; the query results are the rows of data that fill the cube’s cells.
See Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Query.
PeopleSoft Process Scheduler IntegrationPeopleSoft Process Scheduler includes a process type definition specifically for use with PeopleSoft Cube Manager. This is the Cube Builder process type, and you invoke it whenever you launch the process to create a cube from the standard run control page. During this process, depending on the OLAP tool that you specify, the data and metadata are translated into a format that is understood by Hyperion Essbase, Cognos PowerPlay, or another ROLAP tool that can read a star schema.
See Also
Defining a Cube Build Process Using Process Scheduler Manager
Designing and Building OLAP CubesIdentifying requirements is the most important part of any analytical application. An analytical application always produces results, regardless of whether those results are meaningful. Identifying requirements is essential to generating meaningful results.
Decide what aspects or processes of the business you want to capture. Some examples could be sales activity, claims processing, or marketing return on investment. You might want to analyze several unrelated aspects of your business. Do not try to make one cube for all of the aspects. Instead, treat each set of related information as a single cube.
Next, identify the measures that you use to quantify those results, such as sales amounts in dollars or units. This information is almost always numeric. Then, identify the criteria with which you want to view the data and the granularity of the data. These criteria form the dimensions. The most common dimensions are time, accounts, geography, products, and department.
See your Hyperion Essbase, Cognos PowerPlay, or other third-party technology documentation.
Using Supported OLAP ToolsEach PeopleSoft customer has unique reporting and analysis needs. To address these needs, PeopleSoft provides support for various OLAP databases and tools, such as Cognos PowerPlay and Hyperion Essbase.
If you have not chosen an OLAP platform, the following descriptions should help you decide which platform best suits your needs. This section discusses:
Cognos PowerPlay
Hyperion Essbase
Cognos PowerPlayCognos PowerPlay includes a product called Transformer, which builds the portable data cube. This cube is easy to attach as a file to email.
Cognos PowerPlay includes two components: a database engine component (the PowerCube) and an end-user component. This front-end component can be used not only for Cognos PowerPlay databases, but also for other OLAP databases, including Hyperion Essbase.
Several new components exist in Cognos PowerPlay 7.x (Enterprise Server version) that work together to give you more functionality and to make Cognos PowerPlay cubes available to remote users. Only the pieces that PeopleSoft Cube Manager uses are described in this section. PeopleSoft Cube Manager has been modified to work with these new pieces.
See Supported Platforms on PeopleSoft Customer Connection for exact certified versions of third-party products such as Cognos PowerPlay.
This section discusses:
Enterprise Server.
PP Enterprise Server Administrator.
PPAdmtool.
PPApplications.
How PeopleSoft Cube Manager uses the EP Server.
Enterprise Server (EP Server) is a Windows service that maintains cubes at a given location. Users from remote locations connect to this service and open the cubes in their choice of Cognos PowerPlay for Windows, Cognos PowerPlay for Excel, or Cognos PowerPlay for Web.
You can configure PeopleSoft Cube Manager to register cubes automatically with the EP Server. The EP Server needs a port number, server or machine name, user name, and password for registering a cube. However, PeopleSoft Cube Manager can only add cubes to the EP Server that is running locally, and only to the admin account. This means that users are required to only specify the port number and password for the admin account.
See Cognos PowerPlay Enterprise Server documentation.
Note. PeopleSoft Cube Manager and the EP Server must run on the same machine. When updating a cube, you should make sure that it is not being accessed by anyone prior to running PeopleSoft Cube Manager.
PP Enterprise Server Administrator
PP Enterprise Server Administrator is a Windows application that can communicate with the EP Server, either locally or remotely, and can display the cubes that the EP Server is maintaining. Depending on the security privileges, you can add or remove cubes from the EP Server by using this application. You can also change passwords from this application.
PPAdmtool is a command-line program that can communicate with the EP Server. You can add or remove cubes from the EP Server by using this program. To execute any commands using this program, you must specify the server or machine name, port number, and password.
The difference between the PP Enterprise Server Administrator and PPAdmtool is that PPAdmtool runs at the command prompt. The PP Enterprise Server Administrator is a Windows application that you must start before viewing or manipulating the cubes. You can use PPAdmtool from the prompt to connect to, view, add, or remove cubes, without performing the extra step of starting a program.
PPApplications are the actual Windows applications (Cognos PowerPlay for Windows, Cognos PowerPlay for Excel, or Cognos PowerPlay for Web) that can open the cube .mdc files. These applications need either direct access to the .mdc file (locally or on the network) or access to the EP Server that is maintaining a particular cube.
How PeopleSoft Cube Manager Uses the EP Server
This example shows how PeopleSoft Cube Manager uses the new functionality of Cognos PowerPlay 7.x to manage a cube that exists on the EP Server.
Step 1:
PPAdmtool removes the .mdc file from the EP Server
Step 2:
PeopleSoft Cube Manager uses the transformer to create the .mdc file
Step 3:
PeopleSoft Cube Manager uses PPAdmtool to add the .mdc file to the EP Server
PeopleSoft Cube Manager’s Platform Options page for Cognos PowerPlay has been modified to take the extra information needed to communicate with the EP Server.
See Also
Defining Cube Settings for Cognos PowerPlay
Hyperion EssbaseHyperion Essbase is a robust, client/server-based product, the scope of which goes beyond individual users to the level of a data mart. Although it has many powerful features, including its own scripting language, it might be somewhat more difficult to implement than Cognos PowerPlay. However, because it supports metadata updates, Hyperion Essbase enables you to keep a persistent data store, whereas you must recreate PowerCubes whenever any metadata changes.
Hyperion Essbase includes an add-in that enables you to view OLAP cubes using Microsoft Excel—much like PS/nVision. Access Hyperion Essbase cubes using Cognos PowerPlay as a front end. Hyperion Essbase is shipped separately from PeopleTools (there are separate licensing components for the integration and the end-user product).