Business Intelligence Tutorial

Lesson 18. About multidimensional analysis

In this lesson, you will explore the concepts of creating applications you can use to analyze relational data using Online Analytical Processing (OLAP) techniques. You will use the DB2 OLAP Starter Kit, a scaled-down version of the full-function DB2 OLAP Server product. Both the DB2 OLAP Starter Kit and DB2 OLAP Server are based on OLAP technology from Hyperion Solutions.

Within the DB2 OLAP Starter Kit, your primary tool for creating OLAP applications is the DB2 OLAP Integration Server, which runs on top of the Essbase multidimensional server. With these applications, users can analyze DB2 data using Lotus 1-2-3 or Microsoft Excel.

What is multidimensional analysis?

Tools that employ OLAP technology, such as the DB2 OLAP Starter Kit, empower users to ask intuitive and complex ad hoc questions about their business, such as, "What is my profitability for the third quarter across the southeast region for my focus products?" Such a question requires multiple perspectives on the data, such as time, regions, and products. Each of these perspectives are called dimensions. The DB2 OLAP Starter Kit lets you organize your data into multiple dimensions for analysis.

Relational data can be considered two-dimensional because each piece of data, which you can also call a fact, correlates to one row and one column, each of which can be considered a dimension. The dimensions in a multidimensional database are higher-level perspectives of the data that represents the core components of your business plan, such as Accounts, Time, Products, and Markets. In an OLAP application, these dimensions tend not to change over time.

Each dimension has individual components called members. For example, the quarters of the year can be members of the Time dimension, and individual products can be members of the Products dimension. You can have hierarchies of members in dimensions, such as months within the quarters of the Time dimension. Members tend to change over time, for example, as your business grows and new products and customers are added.

Lesson overview

In this tutorial, you will:

• Create an OLAP model, which is a logical structure that describes your overall business plan. The model takes the form of a star schema which represents the relationships between its components with a star-like structure. At the center of the star schema is a fact table, which contains the actual data that you want to analyze, such as product sales figures. Radiating from the fact table are the dimension tables which contain data that define the OLAP dimensions, such as account numbers, months, product names, and so on. One or just a few OLAP models can represent most or all aspects of your business. In the scenario of this tutorial, you will create an OLAP model that covers most of your business, The Beverage Company (TBC).
• Create an OLAP metaoutline, which is generally a subset of the model that you use to create an OLAP application. The idea is to create one or a few OLAP models, from which you can create many metaoutlines, each of which can look at a specific aspect of your business. Metaoutlines describe how the multidimensional database outline will look to the OLAP user. You can tailor the scope of a metaoutline by selecting which dimensions will be visible to OLAP users, and by setting filters that determine what data is retrieved. In the tutorial, you will create a metaoutline that is specific to the sales data in the Central states region of the TBC company..
• Load and calculate your data to create an OLAP application. An OLAP application contains data structured by an Essbase outline, or template, that is based on the metaoutline.
• Take a brief look at the other components of the DB2 OLAP Integration Server.

After you have finished the tutorial and created the OLAP application, you can analyze the TBC sales data from the Central states region using either the Microsoft Excel or Lotus 1-2-3 spreadsheet programs. See the OLAP Spreadsheet Add-in User's Guide for 1-2-3 or OLAP Spreadsheet Add-in User's Guide for Excel for more information.

The DB2 OLAP Integration Server desktop contains the following components:

• The OLAP Model interface is a full-function tool for creating OLAP models. The DB2 OLAP Integration Server also includes an OLAP Model Assistant, which guides you to create a simple OLAP model. The OLAP Model Assistant does not have all the function of the full OLAP Model interface. The lessons in this tutorial show how to use the Assistant.
• The OLAP Metaoutline interface is a full-function tool for creating OLAP metaoutlines. The DB2 OLAP Integration Server also includes an OLAP Metaoutline Assistant, which guides you to create a simple OLAP metaoutline. The OLAP Metaoutline Assistant does not have all the function of the full OLAP Metaoutline interface. The lessons in this tutorial show how to use the Assistant.
• With the Administration Manager tool you can perform simple OLAP database administration tasks such as exporting data to the Data Warehouse, managing storage, and creating new users and granting them access to applications. See the OLAP Administrator's Guide for more information.