Choosing the Right Summary Management Solution

Whether you choose to use grouping set or concatenated rollup for your fact materialized views will depend primarily on the complexity of the data in your star schema. However, the nature of your existing metadata (CWM or CWM2) may also be a factor, as well as your preference for using either Enterprise Manager or your own PL/SQL scripts.

Summary Management for CWM Metadata

If you have existing CWM metadata, you can use Enterprise Manager to create all the necessary materialized views. The fact materialized views will be generated with concatenated rollup syntax. However, you are not limited to concatenated rollup style MVs if you have CWM metadata. You can create scripts that generate materialized views for your CWM metadata by using the DBMS_ODM package.

Summary Management With a Graphical User Interface

If you would rather not create your own SQL scripts for summary management and you prefer to use Enterprise Manager, you are limited to the concatenated rollup style MVs for your fact data. Moreover, the star schema must be mapped to CWM (not CWM2) metadata.

Summary Management for Multiple Hierarchies

Unless you have a very simple data model with only single-hierarchy dimensions, grouping set MVs are generally more efficient and provide greater flexibility than concatenated rollup MVs.

Build Times

If you have single-hierarchy dimensions, concatenated rollup MVs will take less time to build than grouping set MVs. If you have multiple-hierarchy dimensions, grouping set MVs generally will take less time to build.

Partial Materialization

If you want to store partially aggregated data in your materialized views, the grouping set form provides more flexibility than the concatenated rollup form. Grouping set form supports asymmetric partial materialization. Concatenated rollup form supports only symmetric partial materialization.

With grouping set form, you could store month level summaries for specific level combinations only. For example, you could summarize month data for a certain type of product within a given geographical region, without regard for the other dimension levels associated with the data. You would do this by specifying individual level combinations before generating the script for creating the MV.

With concatenated rollup form, you could store month level summaries only, but they would be aggregated over all of the dimension hierarchies associated with the cube. You could choose to limit the MV to month data by editing the script for creating the MV.

MV Size

Although a grouping set style MV may be very large, it requires significantly less tablespace than concatenated rollup style MVs. The multiple concatenated rollup style MVs for a cube store redundant data, since each hierarchy combination is stored in a separate MV. A grouping set style MV for a cube contains all hierarchy combinations within the single MV.

Lineage (Key)

With concatenated rollup form, all the dimension key columns are populated, and data may only be accessed when its full lineage is specified. With true grouping set form, dimension key columns may contain null values, and data may be accessed simply by specifying one or more levels.

Note: In the current release, all MVs, whether generated with concatenated rollup or with grouping sets, are full lineage preserving.

Query Performance

MVs generated with concatenated rollup are more efficient for schemas that have only single-hierarchy dimensions. MVs generated with grouping sets provide better runtime query performance for schemas that have dimensions with multiple hierarchies.