Dimension Materialized Views

When creating materialized views for the OLAP API, you should create MVs for each dimension in a star schema. Dimensions may be denormalized in a single table or normalized in separate tables (snow flake schema).

The structural differences between concatenated rollup style and grouping set style apply only to materialized views for fact tables. The structure of dimension materialized views is the same whether the fact table materialized view uses concatenated rollup or grouping sets.

Creating Dimension Materialized Views

When you use OLAP Summary Advisor, dimension materialized views are automatically created along with the fact materialized views for a cube.

Alternatively, you can use the CREATEDIMMV_GS procedure in the DBMS_ODM package to create dimension materialized views.

Number of Dimension Materialized Views

The dimension MV scripts produced by OLAP Summary Advisor and DBMS_ODM create a separate MV for each hierarchy of a dimension.

Table 10-1, " SALES_CUBE Cube" lists the dimensions and hierarchies associated with the SALES_CUBE cube in the Sales History (SH) schema.

Table 10-1  SALES_CUBE Cube

SALES_CUBE Dimensions	Hierarchies	Number of MVs
SH.CHANNELS_DIM	CHANNEL_ROLLUP	1
SH.CUSTOMERS_DIM	CUST_ROLLUP GEOG_ROLLUP	2
SH.PRODUCTS_DIM	PROD_ROLLUP	1
SH.PROMOTIONS_DIM	PROMO_ROLLUP	1
SH.TIMES_DIM	CAL_ROLLUP FIS_ROLLUP	2

The total number of dimension materialized views required for SALES_CUBE is seven, the sum of the number of materialized views required for each of its dimension hierarchies.