Fact Materialized Views

When creating MVs for the OLAP API, you should create materialized views for each cube that represent a star schema. The cube must be mapped to a single fact table, and the fact table may contain only lowest-level data.

For CWM metadata, you can choose to create fact materialized views using concatenated rollup syntax or grouping set syntax. For CWM2 metadata, only grouping set style fact MVs are supported.

Creating Fact Materialized Views

When you use OLAP Summary Advisor, fact materialized views are created automatically for each hierarchy combination of a CWM cube. The materialized views are created with concatenated rollup syntax.

Alternatively, you can use the CREATEDIMLEVTUPLE, CREATECUBELEVELTUPLE, and CREATEFACTMV_GS procedures in the DBMS_ODM package to create a fact materialized view for a CWM or CWM2 cube. The materialized view is created with grouping set syntax.

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 28-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".