Skip Headers

Oracle® OLAP Application Developer's Guide
10g Release 1 (10.1)

Part Number B10333-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

1 Overview

This chapter introduces the powerful analytic resources available in an Oracle Database installed with the OLAP option. It consists of the following topics:

1.1 OLAP Technology Within the Oracle Database

Multidimensional technology is now available within the Oracle Database. Organizations no longer need to choose between a multidimensional OLAP database and a relational database. By integrating multidimensional tables and an analytic engine into the database, Oracle provides the power of multidimensional analysis along with the manageability, scalability, and reliability of the Oracle Database.

1.1.1 Problems Maintaining Two Distinct Systems

The integration of multidimensional technology in a relational database is important because maintaining a standalone multidimensional database is costly. It requires additional hardware and DBAs who are skilled at using the specialized administrative tools of the multidimensional database. Moreover, standalone multidimensional databases require applications that use proprietary APIs. This severely limits the number of applications that can be run against them, not only because fewer applications are available in these APIs, but because all the data that they run on must be transferred from the relational database to the multidimensional database. These requirements often force enterprises into supporting two sets of query and reporting tools, one for the relational database and the other for the multidimensional database.

1.1.2 Full Integration of Multidimensional Technology

In contrast, the OLAP option is fully integrated into the Oracle Database. DBAs use the same tools to administer this option as they use to administer all other components of the database. The DBA can decide the best location for storing and calculating the data as part of optimizing the operations of the database. A single application can access both relational and multidimensional data.

SQL-based applications can now use pure SQL against information-rich relational views of multidimensional data provided by an OLAP-enabled Oracle Database. OLAP calculations can be queried using SQL, enabling application developers to leverage their investment in SQL while expanding the analytic sophistication of their software to include modeling, forecasting, and what-if analysis. Standard reporting applications can present the results of complex multidimensional calculations, while ad-hoc querying tools such as custom aggregate members and custom measures can expand the analyst's range of calculation functions.

1.2 Using OLAP to Answer Business Questions

Relational databases provide the online transactional processing (OLTP) that is essential for businesses to keep track of their affairs. Designed for efficient selection, storage, and retrieval of data, relational databases are ideal for housing gigabytes of detailed data.

The success of relational databases is apparent in their use to store information about an increasingly wide scope of activities. As a result, they contain a wealth of data that can yield critical information about a business. This information can provide a significant edge in an increasingly competitive marketplace.

The challenge is in deriving answers to business questions from the available data, so that decision makers at all levels can respond quickly to changes in the business climate.

A standard transactional query might ask, "When did order 84305 ship?" This query reflects the basic mechanics of doing business. It involves simple data selection and retrieval of one record (or, at most, several related records) identified by a unique order number. Any follow-up questions, such as which postal carrier was used and where was the order shipped to, can probably be answered by the same record. This record has a useful life span in the transactional world: it begins when a customer places the order and ends when the order is shipped and paid for. At this point, the record can be rolled off to an archive.

In contrast, a typical series of analytical queries might ask, "How do sales in the Pacific Rim for this quarter compare with sales a year ago? What can we predict for sales next quarter? What factors can we alter to improve the sales forecast? What happens if I change this number?"

These are not questions about doing business transactions, but about analyzing past performance and making decisions that will improve future performance, provide a more competitive edge, and thus enhance profitability. The analytic database is a "crystal ball" for decision makers whose ability to make sound decisions today is dependent on how well they can predict the future. Getting the answers to these questions involves single-row calculations, time series analysis, and access to aggregated historical and current data. This requires OLAP -- online analytical processing.

1.3 Common Analytical Applications

Here are a few examples of common applications that can use the OLAP option to realize valuable gains in functionality and performance:

As this discussion highlights, the data processing required to answer analytical questions is fundamentally different from the data processing required to answer transactional questions. The users are different, their goals are different, their queries are different, and the type of data that they need is different. A relational data warehouse enhanced with the OLAP option provides the best environment for data analysis.

1.4 Deciding When to Use Analytic Workspaces

The types of analyses performed by applications that run against your data warehouse will help you decide whether to store the data entirely in analytic workspaces or distributed between analytic workspaces and relational tables.

Analytic workspaces provide an alternative to materialized views for generating and storing aggregate data. They provide complex aggregation methods that are not available in materialized views, such as weighted calculations, non-additive methods, and models. You might also choose analytic workspaces when you have storage issues concerning aggregate data. Analytic workspaces always present fully solved data to the application, regardless of whether the data is entirely pre-aggregated, partially pre-aggregated, or entirely aggregated on demand. The flexibility of the OLAP aggregation system enables you to pre-aggregate within the limitations of your data refresh window without compromising run-time response time. Moreover, analytic workspaces can store pre-aggregated data very efficiently.

You may also prefer to use analytic workspaces for applications that support predictive analysis functions, such as models, forecasts, and what-if scenarios. Moreover, analytic workspaces are highly optimized for performing single-row calculations, which they can compute at run-time to support custom measures.

A distributed solution may be optimal for query and reporting applications that use the advanced calculation capabilities of analytic workspaces less frequently. For these types of applications, you can create and populate analytic workspaces at run-time for more intensive analysis; the results can be sent directly to the analyst or written to relational tables. The implementation of a distributed model can, of course, vary widely since it encompasses solutions that range from storing all data in relational tables to storing all data in analytic workspaces.

The BI Beans can run against analytic workspaces or relational tables. If you do not plan to use analytic workspaces, then refer to the information in "Managing Data Sources for the BI Beans and OLAP API".

1.5 Working With Oracle OLAP

There are several levels at which you can work with analytic workspaces:

Installation of the OLAP option with the Oracle Database includes the following components:

OLAP Analytic Engine
Analytic Workspaces
Analytic Workspace Manager
OLAP Worksheet
SQL Interface to OLAP
OLAP Catalog
Analytic Workspace Java APIs

The following applications can provide important functionality when working in OLAP, and are available online at the Oracle Web site:

Oracle Warehouse Builder
Oracle Enterprise Manager

All of these components and applications are described in the following paragraphs. The relationships among them are described throughout this guide.

1.5.1 OLAP Analytic Engine

The OLAP analytic engine supports the selection and rapid calculation of multidimensional data. The status of an individual session persists to support a series of queries, which is typical of analytical applications; the output from one query is easily used as input to the next query. A comprehensive set of data manipulation tools supports modeling, aggregation, allocation, forecasting, and what-if analysis. The OLAP engine runs within the Oracle kernel.

1.5.2 Analytic Workspaces

Analytic workspaces store data in a multidimensional format where it can be manipulated by the OLAP engine. An analytic workspace is stored as a LOB table in a relational schema. Within a single database, many analytic workspaces can be created and shared among users. Like a relational schema, an analytic workspace is owned by a particular user ID, and other users can be granted access to it. Because individual users can save a personal copy of their alterations to a workspace, the workspace environment is particularly conducive to planning applications.

1.5.3 Analytic Workspace Manager

Analytic Workspace Manager provides a user interface for creating an analytic workspace in database standard form. This form enables the analytic workspace to be used with various tools that aggregate, refresh, and enable the data so that it is accessible to OLAP applications. These tools are also provided by Analytic Workspace Manager.

For more information about Analytic Workspace Manager, refer to Chapter 6, " Creating an Analytic Workspace ".

1.5.4 OLAP Worksheet

OLAP Worksheet is an interactive environment for working with analytic workspaces, similar to SQL*Plus Worksheet. It provides easy access to the OLAP DML, and enables you to perform sophisticated business analysis, such as modeling, forecasting, and allocation. You can switch between two different modes, one for working with analytic workspaces in the OLAP DML, and the other for working with relational tables and views in SQL. It is available through Analytic Workspace Manager or as a separate executable.

For more information about OLAP Worksheet, refer to Chapter 9.

1.5.5 SQL Interface to OLAP

The SQL interface to OLAP provides access to analytic workspaces from SQL. The SQL interface is implemented in PL/SQL packages. These are the primary ones:

  • CWM2 is a large collection of packages for defining OLAP Catalog metadata. These packages support the BI Beans enabler in Analytic Workspace Manager.

  • DBMS_AW contains procedures for executing OLAP DML commands. This package supports OLAP Worksheet, and the property sheets and dialogs in Analytic Workspace Manager. Using the procedures and functions in the DBMS_AW package, SQL programmers can issue OLAP DML commands directly against analytic workspace data. They can move data from relational tables into an analytic workspace, perform advanced analysis of the data (for example, forecasting), and copy the results of that analysis into relational tables.

  • DBMS_AWM contains procedures for creating analytic workspaces. It supports the Create Analytic Workspace wizard in Analytic Workspace Manager.

  • DBMS_AW_UTILITIES contains procedures for creating and managing custom measures in a standard form analytic workspace. Custom measures are defined at run-time, and are calculated from stored measures.

For more information about these PL/SQL packages, refer to Chapter 7, " SQL Access to Analytic Workspaces " and the Oracle OLAP Reference.

1.5.6 OLAP DML

OLAP DML is a mature low-level language that is native to analytic workspaces. It is the data definition and manipulation language for creating analytic workspaces, defining data containers, and manipulating the data stored in these containers. All other levels of operation (GUIs, Java, and SQL) resolve to the OLAP DML. It offers the maximum power and flexibility in acquiring, manipulating, and analyzing data.

If you are upgrading from Oracle Express, or if your data is stored in formats not supported by the higher level tools, then you may work directly in the OLAP DML at an early stage. Otherwise, you may use the OLAP DML directly only to enhance the functionality of your workspaces.

1.5.7 OLAP Catalog

OLAP Catalog is the metadata repository provided for the OLAP option. It consists of write APIs, which are a set of PL/SQL procedures, and read APIs, which are relational views within the Oracle Database. The metadata describes data, which is presented as a star schema, in multidimensional terms such as cubes, measures, dimensions, and attributes. The OLAP Catalog is used to perform two distinct functions:

  • To create an analytic workspace from a star or snowflake schema.

  • To provide a Java application, which uses the BI Beans, with access to data stored in either an analytic workspace or relational tables. The BI Beans requires OLAP Catalog metadata. If data is not defined in the OLAP Catalog, then it is not available to applications that use the BI Beans.

The OLAP Catalog read APIs make the metadata that you have defined available to applications. They are useful to any application that uses SQL SELECT statements to run against views of analytic workspace data.

SQL applications do not require the use of the OLAP Catalog, but may benefit from using it. They can run against the logical objects that are defined in the OLAP catalog, without an awareness of where the underlying data resides.

1.5.8 Analytic Workspace Java APIs

The Analytic Workspace Java APIs provide a Java interface for the creation and maintenance of analytic workspaces. These APIs are an alternative to using the OLAP Catalog for defining an analytic workspace build.

See Also:

Oracle OLAP Analytic Workspace Java API Reference

1.5.9 OLAP API

The OLAP API is the Java-based programming interface for OLAP applications, and supports the BI Beans. The BI Beans are building blocks for developing analytic applications in Java, and are available for use with JDeveloper. If you are a Java developer, then you should consider using the BI Beans for your analytic applications. Note that the BI Beans are not included with the OLAP option, but they require an OLAP-enabled Oracle Database.

1.5.10 Oracle Enterprise Manager

Oracle Enterprise Manager is a system management tool that provides you with an integrated solution for managing Oracle products without formulating complex SQL commands. You can use Enterprise Manager to set up user accounts, define tablespaces, monitor performance, and do other administrative tasks associated with your database, including the OLAP option.

The OLAP Management tool is part of the Enterprise Manager support for data warehouses. Using a graphical user interface, you can define logical metadata dimensions, measures, and cubes in the OLAP Catalog for the dimension tables and fact tables of a star or snowflake schema that complies with the database requirements for creating a dimension.

For more information about the OLAP Management tool, refer to Chapter 5, " Defining a Logical Multidimensional Model".

1.5.11 Oracle Warehouse Builder

Oracle Warehouse Builder can extract data from many different sources, transform it into a star schema in the relational database, generate OLAP Catalog metadata, and create an analytic workspace. Warehouse Builder provides an alternative to using the OLAP Management tool in Enterprise Manager, and the Create Analytic Workspace wizard in Analytic Workspace Manager. The resulting analytic workspace is in database standard form, so you can then use Analytic Workspace Manager to aggregate, enhance, and enable your data.

If your data requires transformation, then Oracle Warehouse Builder provides the best method for generating an analytic workspace. Once you have created a logical model for your data warehouse, Oracle Warehouse Builder requires only a few extra steps to generate an analytic workspace in addition to a star schema.

See Also:

Oracle Warehouse Builder User's Guide

1.6 Process Overview: Creating and Maintaining Analytic Workspaces

Analytic workspaces can be created in a variety of ways, depending on the characteristics of the data source and your own personal preference. However, the basic process is the same for all of them.

These are the basic stages:

  1. Define a logical multidimensional model in the metadata, and map the logical objects to physical data sources. See Chapter 5.

  2. Create and populate an analytic workspace. See Chapter 6.

  3. Generate information-rich data using aggregation, allocation, modeling, forecasting, and other analytic methods. See Chapter 6, Chapter 9, and Chapter 10.

  4. Generate relational views of the analytic workspace. See Chapter 6.

  5. Define metadata specifically for use by particular applications. See Chapter 6.

  6. Periodically refresh the analytic workspace with new data. See Chapter 6.

  7. Calculate custom measures and dimension members. See Chapter 7 and Chapter 9.

Table 1-1 identifies the tools available for performing each stage. Using these tools to perform the various stages of creating and managing analytic workspaces is the topic of this guide.

Table 1-1 Tools for Working With Analytic Workspaces

Stage Tools
Design a logical model and map it to data sources
Oracle Enterprise Manager
CWM2 Write APIs
Oracle Warehouse Builder
Analytic Workspace Java APIs
Create and populate an analytic workspace
Analytic Workspace Manager wizards
Oracle Warehouse Builder
Analytic Workspace Java APIs
Generate information
Analytic Workspace Manager wizards
Analytic Workspace Java APIs
Create views
Analytic Workspace Manager enablers
OLAP_TABLE function
Generate metadata for views
Analytic Workspace Manager enablers
CWM2 Write APIs
Generate custom measures
DBMS_AW PL/SQL package
OLAP_TABLE function
Analytic Workspace Java APIs
Refresh the data
Analytic Workspace Manager wizards
Analytic Workspace Java APIs