Working With Oracle OLAP - OLAP

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

  • Graphical user interfaces (GUIs) provide wizards and property sheets for performing the basic tasks for creating and managing analytic workspaces. This topmost level formulates calls to the underlying SQL packages. Your introduction to developing and maintaining analytic workspaces is learning to use these GUIs.
  • SQL packages perform all the tasks needed to create, maintain, and expose analytic workspaces for use by applications. Some SQL packages work directly with workspaces and execute the underlying OLAP DML. Other SQL packages work with relational tables and views, and execute SQL.
  • Java packages can build and query analytic workspaces.
  • OLAP DML is the native language of analytic workspaces and implements all operations initiated at the other levels.

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.

OLAP Analytic Engine

The OLAP analytic engine supports the selection and rapid calculation of multi dimensional 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.

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.

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.

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.

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.


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.

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.

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.


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.

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.

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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics