Example: Manually Choose the Grouping Sets for a Cube

This example creates materialized views for the cube PRICE_CUBE in the GLOBAL schema.

This cube contains unit costs and unit prices for different products over time. The dimensions are PRODUCT, with levels for products, families of products, classes of products, and totals, and TIME with levels for months, quarters, and years.

You want to summarize product families by month and product classes by quarter.

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 a statement 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 prodmv.sql and timemv.sql in the /users/global/scripts directory.

   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 PRODUCT dimension and one for the TIME dimension.

4. Create the table of dimension levels for the fact materialized view.

   exec dbms_odm.createdimlevtuple('global', 'price_cube');
   
   

   The table of levels, sys.olaptablevels, is a temporary table specific to your session. It lists all the levels in PRICE_CUBE. You can view the table as follows.

   select * from sys.olaptablevels;
   
   SCHEMA_NAME DIMENSION_NAME DIMENSION_OWNER CUBE_NAME    LEVEL_NAME SELECTED
   ----------- -------------- --------------- ----------   ---------- --------
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   YEAR            1
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   QUARTER         1
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   MONTH           1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   TOTAL_PRODUCT   1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   CLASS           1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   FAMILY          1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   ITEM            1
   
   

   All the levels are initially selected with "1" in the SELECTED column.

5. Since you want the materialized view to include only product families by month and product classes by quarter, you can deselect all other levels. You could edit the table with a statement like the following.

   update SYS.OLAPTABLEVELS set selected = 0 
      where LEVEL_NAME in ('ITEM','TOTAL_PRODUCT', 'YEAR');
   select * from sys.olaptablevels;
   
   SCHEMA_NAME DIMENSION_NAME DIMENSION_OWNER CUBE_NAME    LEVEL_NAME SELECTED
   ----------- -------------- --------------- ----------   ---------- --------
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   YEAR            0
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   QUARTER         1
   GLOBAL      TIME           GLOBAL          PRICE_CUBE   MONTH           1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   TOTAL_PRODUCT   0
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   CLASS           1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   FAMILY          1
   GLOBAL      PRODUCT        GLOBAL          PRICE_CUBE   ITEM            0
   
   

6. Next create the table sys.olaptableveltuples. This table, which is also a session-specific temporary table, contains all the possible combinations of the levels that you selected in the previous step. Each combination of levels, or grouping set, has an identification number. All the grouping sets are initially selected with "1" in the SELECTED column.

   exec dbms_odm.createcubeleveltuple('global','price_cube');
   select ID, SCHEMA_NAME, CUBE_NAME, DIMENSION_NAME, DIMENSION_OWNER, 
          LEVEL_NAME, SELECTED
          from sys.olaptableveltuples;
   
   ID SCHEMA_NAME CUBE_NAME   DIMENSION_NAME DIMENSION_OWNER LEVEL_NAME SELECTED
   -- ----------- ---------   -------------- --------------- ---------- --------
   1   GLOBAL     PRICE_CUBE  PRODUCT        GLOBAL          FAMILY      1
   2   GLOBAL     PRICE_CUBE  PRODUCT        GLOBAL          CLASS       1
   3   GLOBAL     PRICE_CUBE  PRODUCT        GLOBAL          FAMILY      1
   4   GLOBAL     PRICE_CUBE  PRODUCT        GLOBAL          CLASS       1
   1   GLOBAL     PRICE_CUBE  TIME           GLOBAL          MONTH       1
   2   GLOBAL     PRICE_CUBE  TIME           GLOBAL          MONTH       1
   3   GLOBAL     PRICE_CUBE  TIME           GLOBAL          QUARTER     1
   4   GLOBAL     PRICE_CUBE  TIME           GLOBAL          QUARTER     1
   
   

   There are four grouping sets numbered 1, 2, 3, and 4. Each grouping set identifies a unique combination of the levels Quarter and Month in the TIME dimension and CLASS and FAMILY in the PRODUCT dimension.

7. Since you want the materialized view to include only product families by month and product classes by quarter, you can deselect the other level combinations. You could edit the sys.olaptableveltuples table with a statement like the following.

   update sys.olaptableveltuples set selected = 0    where ID in ('2', '3');
   
   select ID, SCHEMA_NAME, CUBE_NAME, DIMENSION_NAME, DIMENSION_OWNER, 
          LEVEL_NAME, SELECTED
          from sys.olaptableveltuples where SELECTED = '1';
   
   ID SCHEMA_NAME CUBE_NAME  DIMENSION_NAME  DIMENSION_OWNER LEVEL_NAME SELECTED
   -- ----------- ---------  --------------  --------------- ---------- --------
   1 GLOBAL       PRICE_CUBE PRODUCT         GLOBAL           FAMILY      1
   4 GLOBAL       PRICE_CUBE PRODUCT         GLOBAL           CLASS       1
   1 GLOBAL       PRICE_CUBE TIME            GLOBAL           MONTH       1
   4 GLOBAL       PRICE_CUBE TIME            GLOBAL           QUARTER     1
   
   

8. To create the script that will generate the fact materialized view, run the CREATEFACTMV_GS procedure.

   exec dbms_odm.createfactmv_gs
         ('global','price_cube',
          'price_cost_mv.sql','GLOBALDIR',TRUE);
   
   

   The grouping sets specified in the CREATE MATERIALIZED VIEW statement for the cube are:

   GROUP BY GROUPING SETS (
   
   (TIME_DIM.YEAR_ID, TIME_DIM.QUARTER_ID, TIME_DIM.MONTH_ID, 
    PRODUCT_DIM.TOTAL_PRODUCT_ID, PRODUCT_DIM.CLASS_ID, PRODUCT_DIM.FAMILY_ID),
    
   (TIME_DIM.YEAR_ID, TIME_DIM.QUARTER_ID, 
    PRODUCT_DIM.TOTAL_PRODUCT_ID, PRODUCT_DIM.CLASS_ID) )
   
   

9. Go to the users/global/scripts directory and run the price_cost_mv script to create the fact materialized view.