Designing Cube Metadata

This chapter provides an overviews of cubes, metadata types, Oracle Essbase properties, PeopleSoft Process Scheduler Integration, and discusses how to:

• Use trees

• Create queries

See Also

Using PeopleSoft Tree Manager

Creating and Running Simple Queries

Understanding Cubes

The key concept of OLAP is that of a cube. This document uses the term cube when referring to any analytic data store. 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 sales (in units) of your company. You can examine the total units that were sold in a particular year, but that total units would not help you understand much about your business. Instead, you might want to see unit sales broken down by time and by product. The matrix that you use to analyze this data might look like the following table, which illustrates a cube with two dimensions:

Dimensions and Members

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 preceding table, 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.

Measures and Cells

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 that were sold. Sold units make up 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.

Multiple Dimensions

The two-dimensional cube in the preceding table is basic, for reporting purposes. For example, it does not provide data about where any of the units were sold. You can provide this information by adding another dimension, location, to the model as shown in this diagram:

Diagram 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 depicting four dimensions graphically is a challenge, 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.

Hierarchy

A Hierarchy 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), and 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 one of three levels: month, quarter, or year. Likewise, you can record that each sale occurs in a particular office, in a particular city, or 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, this diagram represents a portion of a typical time dimension, with its various levels and members:

Diagram of a time hierarchy

Each box in the diagram represents a unique member. This diagram is familiar for Oracle PeopleSoft Tree Manager users. In fact, PeopleSoft trees can play an important role in defining the hierarchy of an OLAP cube.

See Enterprise PeopleTools 8.50 PeopleBook: PeopleSoft Tree Manager.

Consolidation

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

This 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 two members are 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.

PeopleSoft Metadata

Metadata is data that defines data. Metadata conveys information about how data is formatted, structured, and stored. In an OLAP cube, metadata defines dimensions, levels, members, member attributes, and interrelationships of the cube. PeopleSoft Cube Manager uses two types of PeopleSoft structures—trees and queries—to define cubes.

PeopleSoft Trees

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.

You can easily 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.50 PeopleBook: PeopleSoft Tree Manager.

PeopleSoft Queries

PeopleSoft queries are SQL statements that are created by Oracle 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. Finally, you can use queries to populate OLAP cubes with data; the query results are the rows of data that fill the cells of the cube.

See Enterprise PeopleTools 8.50 PeopleBook: PeopleSoft Query.

Understanding Types of Metadata

Three types of metadata are available that define an Online Analytical Processing (OLAP) cube:

• Dimensional metadata (hierarchy and members).

• Member property metadata (consolidation method, sign flip, and label).

• Cube metadata (dimensions and measures).

You use Oracle PeopleSoft Tree Manager and Oracle PeopleSoft Query to describe all of this metadata to Oracle PeopleSoft Cube Manager.

Oracle Essbase Properties

Oracle Essbase has the following valid property types:

• Data storage.

  Data storage enables Essbase to recognize what type of storage to allocate for the member. Valid values are 0 or blank (store data), 1 (never share), 2 (label only), 3 (shared member), 4 (dynamic calculation and store), and 5 (dynamic calculation, no store).

  PeopleSoft Cube Manager sets the default value as store data for all members in the first rollup and the non-detail nodes of all other rollups. Detail nodes in secondary rollups are set to shared members.

• Expense item.

  Expense item applies to account dimensions only. Essbase has certain built-in formulas that can take advantage of the knowledge that an item is an expense. To pass this knowledge on to Essbase, you should use this property. Valid values are Blank (set) and non-Blank (do not set).

• Time balance.

  Time balance affects how the parent time value is calculated. Valid values are 0, 1, 2, and 3, which correspond to none, first, last, and average, respectively.

• Consolidation.

  This property enables you to define the mathematical operator used for rolling up members. Most often, you expect that data is added (using the + operator) when rolled up. However, you might occasionally need to specify other operators, such as those listed in the following table:

See Also

Oracle Essbase and Cognos PowerPlay Services documentation.

PeopleSoft Process Scheduler Integration

Oracle PeopleSoft Process Scheduler includes a process type definition specifically for use with PeopleSoft Cube Builder. This process type 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, the data and metadata are translated into a format that is understood by Oracle Essbase.

See Also

Defining a Cube Build Process Using Process Scheduler Manager

Using Trees

Metadata that exists in PeopleSoft trees can be particularly useful when you design cube dimensions. The main reason to use an existing tree is to leverage the rules that are associated with the outline that the tree represents. Because trees are used to validate information that is stored in the Online Transaction Processing (OLTP) database, all of that tree information is already related to the transactional data. Using effective-dated trees in a outline generates the automatic evolution of your data that is used for data analysis.

PeopleSoft Cube Manager leverages the information that is already stored in your PeopleSoft trees as outlines upon which to build each dimension. Using the Dimension page in Essbase Cube Builder, you map a tree to a dimension so that the rollup of the resulting cube dimension is the same as that of the specified tree.

By default, data is summarized exactly as the tree is defined. Each node and detail value becomes a member of the cube hierarchy for that dimension. The descriptions of the nodes and details become the labels, or aliases, of the members.

You might want to use existing trees for your dimensions, or you might need to create new trees. If you have an existing tree that is close to what you want the dimension to look like, make a copy of the tree and modify that copy.

If the hierarchy that you want to use is a subset of an existing tree, you do not have to create a whole new tree. PeopleSoft Cube Builder enables you to use a subset of an existing tree by specifying a starting node—Top Node—and the number of levels below the top node of the tree to include in the hierarchy. You can use more than one tree belonging to the same business unit to define a single hierarchy.

In addition, if a tree does not provide the structure that you need for a dimension, you can add members, attributes, and generations by using one or more queries to provide the additional metadata.

PeopleSoft Cube Manager treats uppercase and lowercase characters as distinct, so the names ABC, Abc, and abc are all considered unique member names. However, Oracle Essbase offers an option to change all member names to uppercase. If you enable this option, you create problems in PeopleSoft Essbase Cube Builder with members that are identical except for their letter casing.

Note. PeopleSoft Cube Manager permits duplicate node names if you cannot avoid the duplication.

In Essbase, a dimension can have multiple rollups. The resulting total for the first rollup is calculated differently from the resulting totals for subsequent rollups. For example, a dimension exists with two rollups. Two different trees are used for these two rollups. The first tree is set up as A, B, C, while the second tree is A, D, C.

Assume that A, B, C, and D have the following fact data values: 2, 5, 10, and 20 respectively. The total for the first rollup A, B, C is 17 because the total equals 2 + 5 + 10. The total for the second rollup A, D, C is 10 because each parent gets its total from its children. Because C has the fact value 10, the total for C is 10. Because D is the parent of C, D gets its total from its children. Therefore, D gets 10 from C. A gets its total from its children, which is D. Therefore, A has the total value of 10.

See Also

Designing a Cube

Creating Queries

This section provides an overview of query types and discusses:

• Dimension queries.

• Oracle Essbase properties.

• Data source queries.

Understanding Query Types

You can create several types of queries to use with PeopleSoft Cube Builder, all of which you must define as user (ad hoc) queries, as opposed to role queries, or database agent queries.

See Dimension Queries.

See Data Source Queries.

Dimension Queries

Dimension queries enable you to define the dimension structure using query results instead of, or in addition to, a tree. However, remember that you are using queries to create a tree-like structure.

You can convey hierarchical information by parent/child relationship, or a narrow query.

In PeopleSoft Cube Builder, dimension queries can be dynamic or static. Dynamic query indicates that any incremental change on the tables that the query is using would be reflected in the next run of PS2Essbase. Static query indicates that further changes to the tables used to create the first hierarchy will not be reflected, unless the hierarchy is manually reloaded.

PeopleSoft Cube Builder uses dynamic queries to populate members at a leaf levels of a hierarchy and under the same parent.

Data Source Queries

Data source queries define the data that you bring into the cube. Writing a data source query is straightforward; the query must return one column for each dimension and one column for each measure. Assume that you want to build a data source query for a cube containing amounts that are dimensioned by account, department, and period.

The output of your query has four columns, as shown in this table:

You can use several queries as the data source for a single cube, thus defining multiple measures. Every data source query that is used must include an output column for every dimension that is used and for at least one measure. However, you are not required to provide an output column for every measure in every data source query.

The following tables show examples of how you can use two separate queries as a data source for a cube. Note that both queries return columns for every dimension, as required, and that they differ only in which measure they include.

Results of Query 1:

Results of Query 2:

Resulting cube using Query 1 and Query 2 as data sources: