Using the DBMS_ODM Package

The procedures in the OLAP Data Management package, DBMS_ODM, generate scripts that create materialized views in grouping set form for fact tables. Each script generates a single MV containing all hierarchy combinations for a CWM2 cube.

The procedures in DBMS_ODM generate scripts that create materialized views, bitmap indexes, and partitions. You can run these scripts in their original form, modify the scripts before executing them, or use them simply as models for writing your own SQL scripts.

Procedure: Create and Run Scripts to Generate Grouping Set Materialized Views

Follow these steps to create a grouping set materialized view for a cube:

1. Create and map a valid CWM2 cube as described in Chapter 17, "CWM2_OLAP_CUBE".
1. Enable your database to write the scripts to a file by setting the UTL_FILE_DIR parameter to a valid directory, as described in "Initialization Parameters for Oracle OLAP".
2. Log into SQL*Plus using the identity of the metadata owner.
3. Delete any materialized views that currently exist for the cube.
4. Use the following three step procedure to create a script to generate a grouping set materialized view for the cube:
   1. Execute DBMS_ODM.CREATEDIMLEVTUPLE to create the table sys.olaptablevels. This table lists all the dimensions of the cube and all of the levels of each dimension.

      By default, all the levels of all the dimensions are selected for inclusion in the materialized view. You can edit the table to deselect any levels that you do not want to include.

   2. Execute DBMS_ODM.CREATECUBELEVELTUPLE to create the table sys.olaptableveltuples. This table lists all of the level combinations that will be included in the materialized view. This table is derived from the table created in the previous step.

      By default, all the levels combinations are selected for inclusion in the materialized view. You can edit the table to deselect any level combinations that you do not want to include.

   3. Execute DBMS_ODM.CREATEFACTMV_GS to create the script.

   For example, in the Sales History sample schema, you would create a script for COST_CUBE and a script for SALES_CUBE.

5. Optionally, edit the script using any text editor.
6. Run the scripts in SQL*Plus, using commands such as the following:

   @/users/oracle/OraHome1/olap/mvscript.sql;
   

   See Also: "Summary of DBMS_ODM Subprograms" for the syntax of the procedures in the DMBS_ODM package.