Multidimensional Concepts

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

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:

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:

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.

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. Essbase does not store the data for attribute dimensions, 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. 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, 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 Essbase database development begins with creating a database outline. A database outline accomplishes the following:

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 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 included in your shipment, for example, the Year dimension consists of five members: the Qtr1, Qtr2, Qtr3, 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 uses a hierarchical structure to represent the data consolidations and relationships in Qtr1.

Figure 3: Hierarchical Structure

Some dimensions consist of relatively few members, while others may have hundreds or even thousands of members. 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 Essbase Terminology

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

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 4 in several ways.

Figure 4: Member Generation and Level Numbers

Parents, Children, and Siblings

Figure 4 illustrates the following parent, child, and sibling relationships:

Descendants and Ancestors

Figure 4 illustrates the following descendant and ancestral relationships:

Roots and Leaves

Figure 4 illustrates the following root and leaf member relationships:

Generations and Levels

Figure 4 illustrates the following generations and branch levels:

Note: You can assign a name to a generation or level and then use the name as a shorthand for all the members in that generation or level.

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 shown in Figure 7.

Figure 7: A Multidimensional Database Outline

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

Each intersection of members (one member from each dimension) represents a data value. The example in Figure 8 has three dimensions; thus, the dimensions and data values in the database can be represented in a cube.

Figure 8: Three-Dimensional Database

The shaded cells in Figure 9 called a slice illustrate that, when you refer to Sales, you are referring to the portion of the database containing eight Sales values.

Figure 9: Sales Slice of the Database

Slicing a database amounts to fixing one or more dimensions at a constant value while allowing the other dimensions to vary.

When you refer to Actual Sales, you are referring to the four Sales values where Actual and Sales intersect as shown by the shaded area in Figure 10.

Figure 10: 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 11, the cell containing the data value for Sales, Jan, Actual is shaded. The data value can also be expressed using the cross-dimensional operator (->) as Sales -> Actual -> Jan.

Figure 11: Sales ->  Jan ->  Actual Slice of the Database

Looking at Data from Different Perspectives

Slicing the database in different ways gives you different perspectives of the data. The slice of January in Figure 12, for example, examines all data values for which the Year dimension is fixed at Jan.

Figure 12: Data for January

The slice in Figure 13 shows data for the month of February:

Figure 13: Data for February

The slice in Figure 14 shows data for profit margin:

Figure 14: Data for Profit Margin

Designing and Creating a Simple Application

Use this section to build and analyze a sample application called Simple. This application is for a company called The Car Company (TCC). TCC manufactures, markets, and distributes cars and trucks across the United States. This is not a sample application supplied with the software but is an example used to illustrate how you can quickly create your own first application.

These steps are similar to those you would use to create any Essbase application:

Analyzing the Application Requirements

Before you design an application, you must analyze the company data and determine requirements. 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, rekeying, 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 its origin. To accommodate their needs, TCC chooses Essbase.

Organizing Multidimensional Data

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

This section contains information to help you create and understand the outline created for TCC:

Understanding the Outline

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.

Determining a Logical Structure

To determine a logical structure for the data, remember that standard dimensions often parallel a company's organization. In Figure 15 the TCC Simple database has four standard dimensions: Time, Product, Market, and Measures.

Figure 15: TCC Simple Database Showing Four Standard Dimensions

Give each dimension two members as shown in Figure 16.

Figure 16: TCC Simple Database with Standard Dimensions and Members

To appreciate the power of the multidimensional database, you need to understand how 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 is the intersection of a certain row and a certain column; for instance, row 3 and column 4. An 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 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.

Using the Logical Structure to Answer Questions

Look at a subset of the 81 data values for the TCC database to determine how 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. Table 1 contains all of the values that constitutes the Spring list, with the consolidated members in bold.

Table 1: Data Values and Consolidation Points for TCC  

Time
Product
Market
Measures
Data Value

Spring

Cars

Chicago

Sales

800

Spring

Cars

Chicago

Expenses

600

Spring

Cars

Chicago

Measures

200

Spring

Cars

New York

Sales

500

Spring

Cars

New York

Expenses

200

Spring

Cars

New York

Measures

300

Spring

Cars

Market

Sales

1300

Spring

Cars

Market

Expenses

800

Spring

Cars

Market

Measures

500

Spring

Trucks

Chicago

Sales

700

Spring

Trucks

Chicago

Expenses

400

Spring

Trucks

Chicago

Measures

300

Spring

Trucks

New York

Sales

550

Spring

Trucks

New York

Expenses

150

Spring

Trucks

New York

Measures

400

Spring

Trucks

Market

Sales

1250

Spring

Trucks

Market

Expenses

550

Spring

Trucks

Market

Measures

700

Spring

Product

Chicago

Sales

1500

Spring

Product

Chicago

Expenses

1000

Spring

Product

Chicago

Measures

500

Spring

Product

New York

Sales

1050

Spring

Product

New York

Expenses

350

Spring

Product

New York

Measures

700

Spring

Product

Market

Sales

2550

Spring

Product

Market

Expenses

1350

Spring

Product

Market

Measures

1200



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

A typical database contains associated formulas and a Essbase script to analyze the data. For more information on developing formulas and Essbase 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 append additional stored members to the database under each standard dimension. Add two seasons under Time; one product, Motorcycles; one market, LA; and three members, Profits, Inventory, and Ratios, under the Measures dimension. Refer to Figure 17. For more information on how to build an outline, see Creating and Changing Database Outlines.

Figure 17: Adding Stored Members to the Outline

If you tried to create a table similar to Table 1, your table would look like the one shown in Table 2.

Table 2: Data Values and Consolidation Points of Stored Members  

Time
Product
Market
Measures
Data Value

Winter

Cars

Chicago

Sales

1000

Winter

Cars

Chicago

Expenses

600

Winter

Cars

Chicago

Profits

.

Winter

Cars

Chicago

Inventory

1600

and so on

...

...

...

...



The new database is going to be 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

Add a new standard dimension called Distribution Channel to the database outline. Give the new dimension two stored members, Retail and Wholesale as shown in Figure 18. For more information, see Adding Dimensions and Members to Outlines.

Figure 18: Adding a Standard Dimension to the Outline

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

The TCC company wants the Simple database to represent the LA market only, so there is no need for a Market dimension. Delete the Market dimension from the database outline. The changed outline is shown in Figure 19. For more information, see Creating and Changing Database Outlines.

Figure 19: Deleting a Standard Dimension and Members

This decision impacts the database in that 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.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com