Multidimensional Concepts

Hyperion Essbase OLAP Server contains multidimensional databases that support analysis and management reporting applications that are described as online analytical processing (OLAP) applications. This chapter discusses multidimensional concepts and terminology. This chapter contains the following sections:

• Introducing OLAP
• Introducing Dimensions and Members
• Defining Hyperion Essbase Terminology
• Identifying Values in a Multidimensional Database
• Looking at Data from Different Perspectives
• Designing and Creating a Simple Application

Introducing OLAP

In 1993, E. F. Codd, who set the seminal rules describing relational databases, published twelve rules for the analytical functions and performance characteristics that are essential to enterprise-scale planning and analysis applications. He called the new technology online analytical processing (OLAP) to reflect its analytical functionality and to differentiate it from online transaction processing (OLTP).

A multidimensional database supports multiple views of data sets for users who need to analyze the relationships between data categories. For example, a marketing analyst might want answers to the following questions:

• How did Product A sell last month? How does this figure compare to sales in the same month over the last five years? How did the product sell by branch, region, and territory?
• Did this product sell better in particular regions? Are there regional trends?
• Did customers return Product A last year? Were the returns due to product defects? Did the company manufacture the products in a specific plant?
• Did commissions and pricing affect how salespeople sold the product? Did particular salespeople do a better job of selling the product?

Multidimensional databases consolidate and calculate data to provide different views. Only the database outline, the structure that defines all elements of the database, limits the number of views. With a multidimensional database, users can pivot the data to see information from a different viewpoint, drill down to find more detailed information, or drill up to see an overview.

Codd's twelve rules cover most user aspects of OLAP, including defining the conceptual view of the data (multidimensional), defining user needs (consistent reporting performance), and defining the platform (client-server). Codd also covers the kind of database operations a multidimensional database should support. These operations include the following:

• Unrestricted cross-dimensional operations
• Intuitive data manipulation
• Flexible reporting
• Unlimited dimensions and consolidation levels

Because of Codd's research, the multidimensional database is a standard in today's computing environment. In fact, OLTP and OLAP databases often coexist; many companies implement an OLAP database in tandem with an OLTP database.

Introducing Dimensions and Members

If you understand dimensions and members, you are well on your way to understanding the power of a multidimensional database.

Dimensions. Hyperion Essbase has two types of dimensions: standard dimensions and attribute dimensions.

Standard dimensions represent the core components of a business plan and often relate to departmental functions. Typical standard dimensions are Time, Accounts, Product Line, Market, and Division. Dimensions are static in most databases; database dimensions rarely change over the life of the application.

Attribute dimensions are a special type of dimension and are associated with standard dimensions. Through attribute dimensions, you group and analyze members of your standard dimensions. Your analyses are based on the members' attributes (characteristics). For example, you can compare the profitability of your non-caffeinated products that are packaged in glass to the profitability of your non-caffeinated products that are packaged in cans.

Attribute dimensions must be associated with a base standard dimension. Hyperion Essbase does not store the data for attribute dimensions, Hyperion Essbase dynamically calculates the data when a user retrieves it. For more information about attribute dimensions, see Working with Attributes

Members are the individual components of a dimension. For example, Product A, Product B, and Product C might be members of the Product dimension. Each member has a unique name. A dimension can contain an unlimited number of members. Hyperion Essbase can store the data associated with a member (referred to as a stored member in this chapter) or it can dynamically calculate the data when a user retrieves it. For more information, see Dynamically Calculating Data Values

A dimension represents the highest consolidation level in the database outline. The database outline indents members below one another to indicate a consolidation relationship. For example, in Figure 3-1, Time is a dimension and Qtr1 is a member. You will learn in later chapters how the hierarchy of members in the outline governs how users drill and pivot data.

Arranging Dimensions into Hierarchies

All Hyperion Essbase database development begins with creating a database outline. A database outline accomplishes the following:

• Defines the structural relationships between members in a Hyperion Essbase database
• Organizes all the data in the database
• Defines the consolidations and mathematical relationships between items

Hyperion Essbase uses the concept of members to represent data hierarchies. Each dimension consists of one or more members. The members, in turn, may consist of other members. When you create a dimension, you tell Hyperion Essbase how to consolidate the values of its individual members. Within the tree structure of the database outline, a consolidation is a group of members in a branch of the tree.

For example, many businesses summarize their data monthly, then roll up the monthly data to obtain quarterly figures, and roll up the quarterly data to obtain annual figures. Businesses may also summarize data by zip code, then by city, state, and country. Any dimension can be used to consolidate data for reporting purposes.

In the Sample Basic database, for example, the Year dimension consists of five members: the Qtr1, Qtr2, Qt3, and Qtr4 members, each storing data for an individual quarter, plus Year, storing summary data for the entire year. Qtr1 consists of four members: the Jan, Feb, and Mar members, each storing data for an individual month, plus Qtr1, storing summary data for the entire quarter. Likewise, Qtr2, Qtr3, and Qtr4 consist of the members that represent the individual months plus the member that stores the quarterly totals.

The database outline in Figure 3-1 uses a hierarchical structure to represent the data consolidations and relationships in Qtr1.

Figure 3-1: Hierarchical Structure

Some dimensions consist of relatively few members, while others may have hundreds or even thousands of members. Hyperion Essbase does not limit the number of members within a dimension and allows you to add new members as needed.

For information on creating a database outline, see Creating and Changing Database Outlines

Defining Hyperion Essbase Terminology

Hyperion Essbase uses the terms defined in the following sections to describe a database outline. These terms are used throughout this manual.

Member Relationships, Generations, and Levels

Hyperion Essbase uses hierarchical and family history terms to describe the roles and relationships of the members in an outline. You can describe the position of the members of the branches in Figure 3-2 in several ways.

Figure 3-2: Member Generation and Level Numbers

Parents, Children, and Siblings

Figure 3-2 illustrates the following parent, child, and sibling relationships:

Parents: A parent is a member that has a branch below it. For example, Margin is a parent member for Sales and Cost of Goods Sold.

Children: A child is a member that has a parent above it. For example, Sales and Cost of Goods Sold are children of the parent Margin.

Siblings: A sibling is a child member with the same parent and at the same branch level as another member. For example, Sales and Cost of Goods Sold are siblings (they both have the parent Margin), but Marketing (at the same branch level) is not a sibling because its parent is Total Expenses.

Descendants and Ancestors

Figure 3-2 illustrates the following descendant and ancestral relationships:

Descendants: Descendants are all the members in branches below a parent. For example, Profit, Inventory, and Ratios are descendants of Measures. The children of Profit, Inventory, and Ratios are also descendants of Measures.

Ancestors: Ancestors are all the members in branches above a member. For example, Margin, Profit, and Measures are ancestors of Sales.

Roots and Leaves

Figure 3-2 illustrates the following root and leaf member relationships:

Root: The root is the top member in a branch. Measures is the root for Profit, Inventory, Ratios, and the children of Profit, Inventory, and Ratios.

Leaves: Leaf members have no children; they are also referred to as detail members, level 0 members, and leaf nodes. For example, Opening Inventory, Additions, and Ending Inventory are leaf members.

Generations and Levels

Figure 3-2 illustrates the following generations and branch levels:

Generations: Generation numbers refer to consolidation levels within a dimension. A root branch of the tree is generation 1. Generation numbers increase as you count from the root toward the leaf member. In Figure 3-2, Measures is generation 1, Profit is generation 2, and Margin is generation 3. All siblings of each level belong to the same generation; for example, Inventory and Ratios are also generation 2.

Figure 3-3 shows part of the Product dimension with its generations numbered:

Figure 3-3: Generations

Levels: Levels also refer to the branches within a dimension; however, levels reverse the numerical ordering that Hyperion Essbase uses for generations. The levels count up from the leaf member toward the root. The root level number varies depending on the depth of the branch. In the example in Figure 3-2, Sales and Cost of Goods Sold are level 0. All other leaf members are also level 0. Margin is level 1 and Profit is level 2. Notice that the level number of Measures varies depending on the branch. For the Ratios branch, Measures is level 2. For the Total Expenses branch, Measures is level 3.

Figure 3-4 shows part of the Product dimension with its levels numbered:

Figure 3-4: Levels

Identifying Values in a Multidimensional Database

This section describes how data is stored in a multidimensional database--a cube of cells containing data values. Each data value is stored in a single cell in the database. You refer to a particular data value by specifying its coordinates along each standard dimension.

Consider the simplified database in Figure 3-5:

Figure 3-5: A Multidimensional Database Outline

This database has three dimensions: Accounts, Time, and Scenario.

• The Accounts dimension has four members: Sales, COGS, Margin, Margin%.
• The Time dimension has four quarter members. Figure 3-6 shows only the members in Qtr1.
• The Scenario dimension has two child members: Budget for budget values and Actual for actual values.

An intersection of members (one member from each dimension) represents a data value. The example in Figure 3-6 has three dimensions; thus, in Figure 3-6 the dimensions and data values in the database can be represented in a cube, as shown in Figure 3-6:

Figure 3-6: Three Dimensional Database

The shaded cells in Figure 3-7 illustrate that, when you refer to Sales, you are referring to a slice of the database containing eight Sales values:

Figure 3-7: Sales Slice of the Database

When you refer to Actual Sales, you are referring to four Sales values:

Figure 3-8: Actual, Sales Slice of the Database

A data value is stored in a single cell in the database. To refer to a specific data value in a multidimensional database, you specify its member on each dimension. In Figure 3-9, the cell containing the data value for Sales, Jan, Actual is shaded:

Figure 3-9: Sales, Jan, Actual Slice of the Database

The data value can also be expressed using the cross-dimensional operator (->) as Sales->Actual->Jan.

Looking at Data from Different Perspectives

Slicing the database in different ways gives you different perspectives of the data. The slice in Figure 3-10, for example, shows data about the month of January:

Figure 3-10: Data for January

The slice in Figure 3-11 shows data for the month of February:

Figure 3-11: Data for February

The slice in Figure 3-12 shows data for profit margin:

Figure 3-12: Data for Profit Margin

Slicing a database amounts to fixing one or more dimensions at a constant value while allowing the other dimensions to vary. The slice of January in Figure 3-10, for example, examines all data values for which the Year dimension is fixed at Jan.

Designing and Creating a Simple Application

In this section, you'll learn how to think multidimensionally by building and analyzing a sample application called Simple. This section uses the same process to analyze data that you use when building a typical multidimensional database.

First, analyze a typical company called The Car Company (TCC). TCC manufactures, markets, and distributes cars and trucks across the United States. Analysts at TCC prepare budget forecasts and track performance on a monthly basis.

Because TCC plans and tracks a variety of products over several markets, the process of deriving and analyzing data is quite tedious. Last month, analysts spent the majority of their time entering, re-keying, and preparing reports.

TCC needs a centralized repository for financial data that allows administrators to load data from different sources. The data repository should reside on a server accessible to analysts throughout the organization. Because all users have access to the server, they can retrieve data at will, regardless of the data's origin. To accommodate their needs, TCC chooses Hyperion Essbase.

Organizing Multidimensional Data

First, you will create a database outline for TCC. The outline defines the structure of the database, including the dimensions and members that it contains. It is important to remember that Hyperion Essbase stores the database outline separately from the data in the database. Each time you make a significant change to the database outline, Hyperion Essbase restructures the data to support the change. For more information on how to build an outline, see Creating and Changing Database Outlines

A database outline contains standard dimensions, attribute dimensions, and members. The members can be stored or they can be dynamically calculated upon retrieval. The following example uses only standard dimensions and stored members. For more information about attribute dimensions and dynamically calculated members, see Introducing Dimensions and Members. Your first job is to determine a logical structure for the data. Remember, standard dimensions often parallel a company's organization. TCC has four standard dimensions: Time, Product, Market, and Measures.

Figure 3-13: TCC Simple with Four Standard Dimensions

Give each dimension two members as shown in Figure 3-14:

Figure 3-14: TCC Simple with Standard Dimensions and Members

To appreciate the power of the multidimensional database, you need to understand how Hyperion Essbase organizes members and standard dimensions. Consider a traditional spreadsheet, where a cell is at the intersection of a row and column. In a multidimensional database, a cell, or data value, is defined by the intersection of all the standard dimensions in the database.

For example, a spreadsheet cell can be the intersection of row 3 and column 4. A Hyperion Essbase data value is defined by the intersection of one member on each of the standard dimensions. For example, a data value can be at the intersection of Spring, Cars, Chicago, and Sales. See Basic Architectural Elements for more information on how Hyperion Essbase stores data values.

The database size is defined by its standard dimensions and members. For example, TCC is a four-dimensional database with three members in each dimension, including the root member (the member that is the name of the dimension). To determine the maximum number of values in the database, multiply the number of members in each dimension. For TCC, the maximum number of values is 3 x 3 x 3 x 3, so TCC has 81 potential data values. It is easy to see from this example how fast a multidimensional database can grow.

Look at a subset of the 81 data values for the TCC database to determine how Hyperion Essbase structures data. A typical query might be: How many cars and trucks did TCC sell in the spring? Begin with the member Spring, and list combinations of standard dimensions and members from the database outline for Spring. Remember, a data value must be defined by one member from each standard dimension. The following table contains all of the values that make up the Spring list, with the consolidated members in bold:

Here are just a few of the questions you can answer using this data:

• How many cars did TCC sell in New York in the spring?
• Did TCC lose money on truck sales in New York in the spring?
• How many trucks did TCC sell in Chicago in the spring? What were the associated profits and revenues during that period?

A typical database contains associated formulas and a calc script to analyze the data. For example, you might want to calculate the variance between budget and actual expenses values. You calculate the variance by defining the appropriate formula on a Variance member. For more information on developing formulas and calc scripts, see Introduction to Database Calculations

Adding and Deleting Standard Dimensions and Stored Members

In this section, you will apply a few hypothetical situations to the TCC database. You will consider what happens to the multidimensional database when you add and delete members and standard dimensions.

Adding Stored Members

In this example, you will add several stored members to the database under each standard dimension. Add two seasons under Time; one product called Motorcycles; a market, LA; and three members to the Measures dimension: Profits, Inventory, and Ratios. For more information on how to build an outline, see Creating and Changing Database Outlines

Figure 3-15: Adding a Stored Member

Create a table similar to the one in the first example.

The new database is potentially much larger than the old one. There are now five members in the Time dimension, four members in each of the Product and Market dimensions, and six members in the Measures dimension.

To determine the maximum potential number of values in the database, multiply the number of stored members in each standard dimension: 5 x 4 x 4 x 6 = 480. So, by adding seven new members, the Simple multidimensional database has grown to a potential 480 values.

Adding a Standard Dimension

Now you will add a new standard dimension called Distribution Channel to the database. In addition, you will give the new dimension two stored members, Retail and Wholesale. For more information, see Adding Dimensions and Members to Outlines.

Figure 3-16: Adding a Standard Dimension

Because the Simple database now has 5 standard dimensions and 17 stored members, the maximum potential number of values is 5 x 4 x 4 x 6 x 3 = 1,440.

When you add a new standard dimension to an outline, you must associate any data in the database with one of the members of the new dimension. For example, in the Simple database, you would have to specify whether the existing data represented Retail or Wholesale. You would then need to load data and calculate the database. For more information, see Introducing Data Loading and Introduction to Database Calculations

Removing a Standard Dimension

In this example, you will delete the Market dimension from the database. The TCC company wants the Simple database to represent the LA market only, so there is no need for a Market dimension. For more information, see Creating and Changing Database Outlines

What impact does this decision have on the database? One less standard dimension diminishes the overall size of the database. However, data for all members in the Market dimension still exists. If you delete a standard dimension from a database outline, the data associated with one member of the deleted standard dimension is retained. You must choose which member's data to retain.

For example, removing the Market dimension from the outline implies that you want to retain data for one member of the Market dimension. In this case, you choose to retain the LA data.

When you delete a standard dimension, you need to recalculate data to reflect changes to the relationships.

Figure 3-17: Deleting a Standard Dimension and Members

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.