Example: Automatically Generate the Minimum Grouping Sets for a Cube

This example shows how to automatically generate a minimum set of materialized views for the cube UNITS_CUBE in the GLOBAL schema. This cube has dimensions for Channel, Customer, Product, and Time. The Customer dimension has two hierarchies, which share the same lowest level.

The dimensions of the UNITS_CUBE are described in Table 27-1. The levels in each hierarchy are listed from lowest (the "leaf" level) to highest (the most aggregate). The position of a level in a hierarchy determines whether it is among the minimum grouping sets. For the rules for creating the minimum grouping sets, refer to "MINIMUM Grouping Sets".

To generate the materialized views:

1. Identify a scripts directory. The directory can be specified in the UTL_FILE_DIR initialization parameter, or you can define a directory object with statements like the following.

   CREATE OR REPLACE DIRECTORY GLOBALDIR AS '/users/global/scripts';
   GRANT ALL ON DIRECTORY GLOBALDIR TO PUBLIC;
   
   

2. Generate the scripts for the dimension materialized views. The following statements create the scripts chanmv.sql, custmv.sql, prodmv.sql, and timemv.sql in the /users/global/scripts directory.

   exec dbms_odm.createdimmv_gs
         ('global', 'channel', 'chanmv.sql', 'GLOBALDIR');
   exec dbms_odm.createdimmv_gs
         ('global', 'customer', 'custmv.sql', 'GLOBALDIR');
   exec dbms_odm.createdimmv_gs
         ('global', 'product', 'prodmv.sql', 'GLOBALDIR');
   exec dbms_odm.createdimmv_gs
         ('global', 'time', 'timemv.sql', 'GLOBALDIR');
   
   

3. Run these scripts to create the dimension materialized views. The scripts will create one materialized view for the CHANNEL dimension, one for the PRODUCT dimension, one for the TIME dimension, and one for each of the two hierarchies of the CUSTOMER dimension.

   @/users/global/scripts/chanmv
   @/users/global/scripts/custmv
   @/users/global/scripts/prodmv
   @/users/global/scripts/timemv
   
   

4. Once you have created the dimension materialized views, execute the following procedure to create a script for the fact materialized view.

   exec dbms_odm.createstdfactmv 
           ('global', 'units_cube', 'units_cube_mv.sql', 'GLOBALDIR', 
             false, 'MINIMUM'); 
   
   

   This statement creates a script called units_cube_mv.sql in the directory /users/global/scripts.

5. Run the script to create the fact materialized view.

   @/users/global/scripts/units_cube_mv
   
   

The script creates a materialized view with the grouping sets identified in Table 27-2.