Fact Materialized Views

When creating MVs for the OLAP API, you should create materialized views for each OLAP Catalog cube that represents a star schema. The cube must be mapped to a single fact table, and the fact table may contain only lowest-level data. For more information, see "Materialized Views and OLAP Metadata".

Number of Fact Materialized Views

The number of fact materialized views for a cube depends on whether you using concatenated rollup style MVs or grouping set MVs.

If you use OLAP Summary Advisor, you will generate a separate concatenated rollup style MV for each combination of hierarchies in the cube. If you use DBMS_ODM, you will generate a single grouping set style MV for the cube.

For example, the SALES_CUBE cube in the Sales History (SH) schema, described in Table 10-1, would have either one materialized view generated with grouping sets or four materialized views generated with concatenated rollup.

For SALES_CUBE, there would be a separate concatenated rollup materialized view for the each of the following dimension hierarchy combinations.

• (CHANNEL, PRODUCT, PROMOTIONS, CUSTOMERS_CUST_ROLLUP, TIMES_CAL_ROLLUP)
• (CHANNEL, PRODUCT, PROMOTIONS, CUSTOMERS_CUST_ROLLUP, TIMES_FIS_ROLLUP)
• (CHANNEL, PRODUCT, PROMOTIONS, CUSTOMERS_GEOG_ROLLUP, TIMES_CAL_ROLLUP)
• (CHANNEL, PRODUCT, PROMOTIONS, CUSTOMERS_GEOG_ROLLUP, TIMES_FIS_ROLLUP)

  See Also: Chapter 30, "Creating Fact Materialized Views With DBMS_ODM" Chapter 31, "Creating Fact Materialized Views With OLAP Summary Advisor".