Choosing a Summary Management Strategy

A basic feature of online analytical processing (OLAP) is the ability to analyze and view various levels of aggregate data. With Oracle OLAP, you can choose to manage aggregation within analytic workspaces or you can use Oracle's query rewrite facility.

Summary Management with Analytic Workspaces

Multidimensional processing within analytic workspaces provides an efficient means of managing summary data. Summaries may be precalculated or calculated on the fly. See "The Oracle9i Integrated Relational-Multidimensional Database" for more information about multidimensional processing.

You can move your warehouse data from relational tables to analytic workspaces using the AW_CREATE package. See Chapter 9, "Creating an Analytic Workspace From Relational Tables".

Summary Management with Materialized Views

Summary management for relational warehouses is managed by Oracle's query rewrite facility. 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. To prepare your relational warehouse for access by the OLAP API, you need to establish materialized views according to the guidelines described in this chapter.

About Materialized Views

Materialized views store data that has been calculated from detail tables. When data in the detail tables changes, you can refresh materialized views with the new data. While a view only stores the query, a materialized view actually stores the results of a query. Thus, you will need to allocate sufficient tablespace to store the required materialized views.

The OLAP API requires a very specific set of materialized views. For query rewrite to recognize that a materialized view contains the query results, the materialized view must have been created using basically the same type of SQL commands that are 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.

Do not 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.