|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Creating Fact Materialized Views With DBMS_ODM, 2 of 9
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
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.
Follow these steps to create a grouping set materialized view for a cube:
CWM2cube as described in Chapter 22, "CWM2_OLAP_CUBE".
UTL_FILE_DIRparameter to a valid directory, as described in "Initialization Parameters for Oracle OLAP".
DBMS_ODM.CREATEDIMLEVTUPLEto 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.
DBMS_ODM.CREATECUBELEVELTUPLEto 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.
DBMS_ODM.CREATEFACTMV_GSto create the script.
For example, in the Sales History sample schema, you would create a script for
COST_CUBE and a script for
"Summary of DBMS_ODM Subprograms" for the syntax of the procedures in the