Creating Materialized Views for the OLAP API, 3 of 7

Materialized View Formats

The database provides you with several tools for generating materialized views for the OLAP API. These tools produce materialized views for dimensions and fact tables. Fact materialized views may be built with concatenated rollup syntax or with grouping set syntax.

The choices you make in establishing materialized views will be based primarily on the structure of the data in the star schema and on the query requirements of OLAP clients.

Important:

You must be sure to create materialized views that are specifically for use by the OLAP API. Query rewrite will not map the SQL generated by the OLAP API to the materialized views generated by the DBMS_OLAP PL/SQL package, which is described in the Oracle9i Data Warehousing Guide. Do not use the DBMS_OLAP package for the OLAP API.

Grouping Sets

The OLAP API supports fact table materialized views that use explicit grouping set syntax. This type of materialized view uses the GROUP BY GROUPING SETS syntax to aggregate the data for each level combination in the summary.

Materialized views generated with grouping set syntax can support asymmetric partial summarization. A single materialized view of this type holds all the summary information for a cube.

To generate this type of materialized view, use the Oracle Data Management PL/SQL package, DBMS_ODM.

Concatenated Rollup

The OLAP API also supports fact table materialized views that use concatenated rollup syntax. This type of materialized view uses the GROUP BY ROLLUP syntax to aggregate the data for each level combination in the summary.

Materialized views generated with concatenated rollup syntax can support symmetric partial summarization. A single materialized view of this type holds the summary information for one hierarchy combination of a cube.

To generate this type of materialized view, use the OLAP Summary Advisor within Oracle Enterprise Manager.