Summary Management Options

To enhance the performance of queries generated by the OLAP API, you should create materialized views for frequently-aggregated data that is stored at detail level in a star or snowflake schema.

You do not need to create materialized views for data stored in embedded-total tables or analytic workspaces. Relational tables with embedded totals contain all the summary information within the tables. Analytic workspaces provide summary management based on a native multidimensional model.

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.

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.