Materialized Views for the OLAP API

Summary management for relational warehouses is managed by the query rewrite facility in the Oracle Database. Query rewrite enables a query to fetch aggregate data from materialized views rather than recomputing the aggregates at runtime.

When the OLAP API queries a warehouse stored in relational tables, it uses query rewrite whenever possible. However, the OLAP API can only use query rewrite when the materialized views have a specific format. The procedures in the DBMS_ODM package create materialized views that satisfy the requirements of the OLAP API.

When the source data is stored in an analytic workspace, materialized views are not used. The native multidimensional structures within analytic workspaces support both stored summarization and run-time aggregation. You can use the DBMS_AWM package, Analytic Workspace Manager, or the OLAP Analytic Workspace Java API to move your data from a star schema to an analytic workspace.

Materialized Views Created by DBMS_OMDM

The DBMS_ODM package creates a set of materialized views based on a cube defined in the OLAP Catalog. The cube must be mapped to a star schema with a single fact table containing only lowest level data.

Scripts generated by DBMS_ODM procedures create the following materialized views:

A dimension materialized view for each hierarchy of each of the cube's dimensions
A single fact materialized view, created with GROUP BY GROUPING SETS syntax, for the cube's measures

Generating the Grouping Sets

A grouping set identifies a unique combination of levels. With grouping sets, you can summarize your data symmetrically, for example sales at the month level across all levels of geography, or you can summarize it asymmetrically, for example sales at the month level for cities and at the quarter level for states.

The DBMS_ODM package provides two ways of calculating the grouping sets included in the fact materialized view. You can execute a single procedure that automatically calculates the grouping sets. Or you can manually choose the grouping sets.

Automatically Calculate the Grouping Sets

To automatically calculate the grouping sets, execute the CREATESTDFACTMV procedure with one of the following options:

Fully materialize the cube. Include all level combinations in the materialized view.
Partially materialize the cube. Include a subset of the level combinations in the materialized view.
Materialize the cube using a percentage of the cube's level combinations.

The first two options summarize the data symmetrically. The third option typically produces asymmetrical summarization.

Manually Calculate the Grouping Sets

To manually calculate the grouping sets:

Execute the CREATEDIMLEVTUPLE procedure to list all the levels in the cube. Choose the levels to include in the grouping sets.
Execute the CREATECUBELEVELTUPLE procedure to create a table containing all the combinations of the levels you chose in the previous step. Edit the table to choose the level combinations (grouping sets) to include in the fact materialized view.

When you manually choose the grouping sets, you can specify either symmetrical or asymmetrical summarization.

Aggregation Operators

Addition is the default aggregation method used in the materialized views. If you want to use a different aggregation method, you must specify it in the OLAP Catalog metadata for each of the cube's dimensions. The same aggregation method must be specified for each dimension, otherwise DBMS_ODM uses addition.

You can use Enterprise Manager or the CWM2_OLAP_CUBE package to specify the aggregation method. See "SET_AGGREGATION_OPERATOR Procedure".