Overview

The DBMS_AWM package provides a feature–rich set of APIs for building and maintaining analytic workspaces. These APIs use a logical cube, stored in the OLAP Catalog, to structure the workspace. The cube is mapped to a star or snowflake schema, which provides the source data for the workspace.

Applications that use the BI Beans or OLAP API can directly query any workspace created by DBMS_AWM. Other types of applications must query the workspace through relational views. These views are created by the DBMS_AWM enablement procedures.

Note:

Analytic workspaces created by the DBMS_AWM procedures are in database standard form, ensuring compatibility with related Oracle OLAP tools and utilities. See Oracle OLAP Application Developer's Guide for information about standard form.

Scripts that create and maintain analytic workspaces must identify two different logical cubes: a relational source cube and a multidimensional target cube. DBMS_AWM also supports the creation of a third optional cube, a relational target cube, which is not used by the OLAP API.

The basic flow of events is as follows:

Relational Source Cube. This cube must exist before you call any of the DBMS_AWM procedures. The cube's metadata is defined within the OLAP Catalog. Its data is unsolved (lowest level only) and stored in a star or snowflake schema.
Multidimensional Target Cube. DBMS_AWM procedures define and populate this cube from the relational source cube. The cube's standard form metadata is defined in the analytic workspace. Its data is stored in the workspace, typically with full or partial summarization.
Relational Target Cube. DBMS_AWM enablement procedures optionally define this cube from the multidimensional target cube. The cube's metadata is defined within the OLAP Catalog. Its data is stored in the analytic workspace and accessed through relational views. The views present the data as fully solved (embedded totals for all level combinations).

The basic process of building an analytic workspace with the DBMS_AWM package is illustrated in Figure 1-1.

Figure 1-1 Creating an Analytic Workspace with DBMS_AWM

Description of "Figure 1-1 Creating an Analytic Workspace with DBMS_AWM"

Creating OLAP Catalog Metadata for the Source Cube

Before you can use the DBMS_AWM procedures, you must create a cube in the OLAP Catalog and map it to the source fact table and dimension tables. The source tables must be organized as a star or snowflake schema.

You can use Oracle Enterprise Manager or Oracle Warehouse Builder to create the cube. You can also create the cube from scripts that use the CWM2 PL/SQL packages, as described in Chapter 2.

Creating Dimensions in the Analytic Workspace

For each dimension of the source cube in the OLAP Catalog, you must run a set of procedures in the DBMS_AWM package to accomplish the following general tasks:

Create a dimension load specification, which contains instructions for populating the dimension in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source dimension tables.
Create containers for the dimension in an analytic workspace.
Use the dimension load specification to populate the dimension in the analytic workspace from the source dimension tables.

Creating Cubes in the Analytic Workspace

After creating the cube's dimensions, run another set of procedures to create and populate the cube itself.

Create a cube load specification, which contains instructions for populating the cube's measures in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source fact table.
Create a composite specification, which contains instructions for ordering the cube's dimensions and storing sparse data in the analytic workspace.
Add the composite specification to the cube load specification.
Create containers for the cube in an analytic workspace.
Use the cube load specification to populate the cube's measures in the analytic workspace from the source fact table.

Aggregating the Cube's Data in the Analytic Workspace

For the workspace cube, run a set of procedures to accomplish the following:

Create an aggregation specification, which contains instructions for storing summary data in the analytic workspace.
Use the aggregation specification to aggregate the workspace cube.

Enabling Access to the Analytic Workspace

Analytic workspaces created with the current release of DBMS_AWM and Analytic Workspace Manager are automatically accessible by applications that use the OLAP API or BI Beans. You do not need to create any views or additional metadata. If you have workspaces that were created with earlier releases of the software, you can upgrade them. Refer to the upgrade instructions in "Enabling Access by the OLAP API".

To enable analytic workspaces for access by other types of SQL clients, you must create views that project the multidimensional data as logical columns and rows. The DBMS_AWM enablement procedures create and maintain a set of relational views for an analytic workspace. The enablement procedures can optionally create OLAP Catalog metadata that maps to the views. See "Enabling Relational Access" for information on the enablement process.

Viewing Metadata Created by DBMS_AWM

Two sets of views reveal metadata related to analytic workspaces. The Active Catalog views reveal metadata stored within analytic workspaces. The Analytic Workspace Maintenance views reveal metadata stored within the OLAP Catalog.

Active Catalog Views

These views use OLAP_TABLE functions to return information about logical standard form objects within analytic workspaces. For example, you could query an Active Catalog view to obtain information about the dimensionality of a workspace cube. The Active Catalog view names have the prefix ALL_OLAP2_AW. For more information, see Chapter 3.

Analytic Workspace Maintenance Views

These views return information about building and maintaining analytic workspace cubes. For example, you could query an Analytic Workspace Maintenance view to obtain information about the load specifications associated with an analytic workspace dimension or cube. The Analytic Workspace Maintenance view names have the prefix ALL_AW. For more information, see Chapter 4.