Skip Headers
Oracle® OLAP Application Developer's Guide
10g Release 2 (10.2)

Part Number B14349-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

8 Materialized Views for the OLAP API

This chapter explains how to create materialized views specific to the requirements of the OLAP API and OracleBI Beans. If you are using analytic workspaces, then you can skip this information because an analytic workspace generates and stores aggregate data so that materialized views are unnecessary. However, if you are developing a strictly relational application, then you must create materialized views using the methods described here. Otherwise, the SQL used to create the materialized views will not match the SQL generated by the OLAP API, and query rewrite will not use the materialized views to formulate the answer set to a query.


See Also:

  • Oracle OLAP Reference for the syntax of the DBMS_ODM package

  • Oracle Data Warehousing Guide for information on managing materialized views

  • Best Practices for Tabular Cube Aggregation and Query Operations for optimizations to the Oracle Database environment and schema design


This chapter includes the following topics:

Summary Management with Oracle OLAP

A basic feature of online analytical processing (OLAP) is the ability to analyze and view various levels of aggregate data. With Oracle OLAP, you can choose to store aggregate data within analytic workspaces or within materialized views.

Summary management for relational warehouses is managed by Oracle's query rewrite facility. Query rewrite enables a query to fetch aggregate data from materialized views rather than recomputing the aggregates at runtime.

When the OLAP API queries a warehouse stored in relational tables, it uses query rewrite whenever possible. To prepare your relational warehouse for access by the OLAP API, you need to establish materialized views according to the guidelines described in this chapter.

Overview and Requirements

The OLAP API requires a specific set of materialized views for each OLAP Catalog cube that maps to a star schema. The cube must be mapped to a single fact table, and the fact table may contain only lowest-level data.

For each cube, there must be a separate dimension materialized view for each hierarchy of each of the cube's dimensions. For the cube's fact table, there must be a single materialized view, created with GROUP BY GROUPING SETS syntax.

Use the Oracle Data Management package, DBMS_ODM, to create materialized views.


Important:

Do not use the DBMS_OLAP package to create materialized views for the OLAP API. Query rewrite will not map the SQL generated by the OLAP API to the materialized views generated by this package.

The DBMS_OLAP package is described in the Oracle Data Warehousing Guide.


Materialized Views Required for a Cube

The OLAP API requires a dimension materialized view for each hierarchy associated with a cube. For example, the SALES_CUBE cube in the Sales History (SH) schema requires seven dimension materialized views, as illustrated in Table 8-1.

Table 8-1 Number of Dimension Materialized Views for SH.SALES_CUBE

SALES_CUBE Dimensions Hierarchies Number of MVs

SH.CHANNELS_DIM

CHANNEL_ROLLUP

1

SH.CUSTOMERS_DIM

CUST_ROLLUP

GEOG_ROLLUP

2

SH.PRODUCTS_DIM

PROD_ROLLUP

1

SH.PROMOTIONS_DIM

PROMO_ROLLUP

1

SH.TIMES_DIM

CAL_ROLLUP

FIS_ROLLUP

2


For the cube's fact table, the OLAP API requires a single grouping set materialized view.

Materialized Views and OLAP Metadata

Before creating materialized views, you must create OLAP metadata for the star schema. You can use Oracle Enterprise Manager, or you can write a script using the CWM2 packages. Refer to Chapter 7 for information about the OLAP Catalog.

A Dimension Materialized View

The SQL script for creating dimension materialized views includes a CREATE MATERIALIZED VIEW statement, and statements for generating statistics and bitmap indexes.

CREATE Materialized View for a Dimension Hierarchy

The basic syntax of the CREATE MATERIALIZED VIEW statement for a dimension hierarchy is as follows.

CREATE MATERIALIZED VIEW mv_name
PARTITION BY RANGE (gid) 
   (partition values less than(1) , 
     .
     .
     partition values less than(MAXVALUE))
TABLESPACE tblspace_name
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT 
   COUNT(*) COUNT_STAR, 
   GROUPING_ID(level_columns) gid,
   MAX(attribute_column_1)
   .
   .
   MAX(attribute_column_n)
   level_cols 
FROM
   dimension_tables
GROUP BY
   hierarchy1_level1, ROLLUP(hierarchy1_level2,... hierarchy1_leveln),
   hierarchy2_level1, ROLLUP(hierarchy2_level2,... hierarchy2_leveln),
     .
     .
   hierarchyn_level1, ROLLUP(hierarchyn_level2,... hierarchyn_leveln);

In the GROUP BY clause, level columns are listed in order from most aggregate (level1) to least aggregate (leveln). The least aggregate level, or "leaf node", is also the key column. Note that level1 is excluded from the ROLLUP list.

Bitmap Indexes for a Dimension Hierarchy

The script includes statements like the following to generate bitmap indexes for the level columns and the GID column. It also calculates a bitmap index for the parent GID and parent ET key.

CREATE BITMAP INDEX index_name ON mv_name(level_column)
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

Statistics for a Dimension Hierarchy

The script includes statements like the following to generate statistics.

execute dbms_stats.gather_table_stats(mv_owner, mv_name,  
            degree=>dbms_stats.default_degree,
            estimate_percent=>dbms_stats.auto_sample_size, 
            method_opt=>'for all hidden columns size 254') ;
method_opt=>
  'for all columns size skewonly') ;
ALTER TABLE mv_name MINIMIZE RECORDS_PER_BLOCK ; 

A Fact Materialized View

The SQL script generated by the DBMS_ODM package for creating fact materialized views includes a CREATE MATERIALIZED VIEW statement and statements for generating statistics and bitmap indexes.

CREATE Fact Materialized View

The basic syntax of the CREATE MATERIALIZED VIEW statement with grouping sets for a fact table is as follows.

CREATE MATERIALIZED VIEW mv_name
PARTITION BY RANGE (gid) 
   (partition values less than(1) , 
     .
     .
     partition values less than(MAXVALUE))
PCTFREE x PCTUSED y
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT 
   GROUPING_ID(level_columns) gid,
   agg_method(measure_1),
    .
    .
   agg_method(measure_n),
   COUNT(*) COUNT_OF_STAR,
   level_columns 
FROM
   dimension_tables, fact_table
WHERE 
   (dimension_primary_key_1 = fact_foreign_key_1) AND
    .
    .
   (dimension_primary_key_n = fact_foreign_key_n) 
GROUP BY GROUPING SETS (
   (level columns in grouping set_1),
   .
   .
   (level columns in grouping set_n);

Each grouping set contains a combination of levels specified for aggregation. For example, a grouping set could specify that the cube's data be aggregated by month for all products in each region.

The SELECT clause lists the levels from the dimension tables and the measures from the fact table. The selected measures will be aggregated over each combination of these levels that has been specified for aggregation. The aggregation method is typically addition (SUM), but it may be a method such as average or weighted average. If you want to use a different aggregation operator, you must specify it in the OLAP Catalog metadata for each of the cube's dimensions.

Bitmap Indexes for Fact Materialized Views

The script includes statements like the following to generate bitmap indexes for each level chosen for inclusion in the materialized view. It also creates a bitmap index for all higher aggregate levels within the dimension. For example, if you chose to aggregate to the quarter level of a time calendar hierarchy, a bitmap index would be created for year and quarter.

CREATE BITMAP INDEX index_name ON mv_name(level_col)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

Statistics for Fact Materialized Views

The script includes statements like the following to generate statistics.

EXECUTE dbms_stats.gather_table_stats(mv_owner, mv_name,  
   degree=>dbms_stats.default_degree, estimate_percent=>
   dbms_stats.auto_sample_size, method_opt=>
  'for all columns size 1 for columns size 254 GID' , granularity=>'GLOBAL') ;
ALTER TABLE mv_name MINIMIZE RECORDS_PER_BLOCK ; 

Using the DBMS_ODM Package

The procedures in the OLAP Data Management package, DBMS_ODM, generate scripts that create dimension materialized views and fact materialized views in grouping set form. 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.


Important:

If you choose to modify the scripts, take care to generate materialized views with the same structure as those generated by DBMS_ODM. Otherwise the materialized views may not be accessible to the OLAP API.

DBMS_ODM supports several approaches to creating the grouping set materialized view for the cube's fact table. You can choose from the following options:

Procedure: Automatically Generate the Materialized Views

Follow these steps to automatically create the materialized views for a cube:

  1. Create a cube in the OLAP Catalog. You can use Enterprise Manager or you can use the CWM2 procedures. If you use the CWM2 procedures, be sure to map the cube to a star schema.

  2. Configure the database to write to files. The DBMS_ODM procedures accept either a directory object to which your user ID has been granted the appropriate access, or a directory path specified by the UTL_FILE_DIR initialization parameter for the instance.

  3. Log into SQL*Plus using the identity of the metadata owner.

  4. Delete any materialized views that currently exist for the cube. Execute DROP MATERIALIZED VIEW mv_name for each materialized view you wish to delete.

  5. Create scripts to generate the dimension materialized views. Execute DBMS_ODM.CREATEDIMMV_GS for each of the cube's dimensions.

  6. Create a script to generate the fact materialized view. Execute DBMS_ODM.CREATESTDFACTMV and choose one of the following values for the materialization level parameter:

  7. Run the scripts in SQL*Plus, using commands such as the following:

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

For an example of this process, see "Example: Automatically Generate the Materialized Views for a Price Cube".

Procedure: Manually Generate the Materialized Views

Follow these steps to create the materialized views with specific level combinations:

  1. Follow the first five steps in "Procedure: Automatically Generate the Materialized Views".

  2. Use the following three step procedure to create a script to generate the fact materialized view:

    1. Execute DBMS_ODM.CREATEDIMLEVTUPLE to create the table sys.olaptablevels. This table lists all the dimensions of the cube and all the levels of each dimension. 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 the possible combinations (grouping sets) of the levels you chose in the previous step. 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.

  3. Run the scripts in SQL*Plus, using commands such as the following:

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

For an example of this process, see "Example: Manually Generate the Materialized Views for a Sales Cube".

Example: Automatically Generate the Materialized Views for a Price Cube

This example creates materialized views for the 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.

  1. Generate the scripts for the dimension materialized views. The following statements create the scripts prodmv and timemv in the directory /users/global/scripts.

    exec dbms_odm.CreateDimmv_gs 
         ('global', 'product','prodmv','/users/global/scripts');
    exec dbms_odm.CreateDimmv_gs 
         ('global', 'time','timemv','/users/global/scripts');
    
    
  2. Run the prodmv and timemv scripts to create the dimension materialized views in the default tablespace for the GLOBAL schema.

  3. Generate the script for the fact materialized view. The following statement creates the script price_cost_mv in the same script directory.

    execute dbms_odm.CreateStdFactmv 
                ('global', 'units_cube', 
                 'price_cost_mv','/users/global/scripts', 
                  false, 'FULL');                                 
    
    
  1. Run the price_cost_mv script to create the fact materialized view.

    The materialized view is created in the default table space for the GLOBAL schema. It does not use index partitioning.

    The CREATE MATERIALIZED VIEW statement in the script specifies grouping sets for every level combination in UNITS_CUBE.

Example: Manually Generate the Materialized Views for a Sales Cube

This example creates materialized views for the DRUGSTORE cube in the DRUG_DEPOT schema. The cube contains sales, cost, quantity, and forecasting data. It is mapped to a fact table containing only lowest-level data and to dimension tables for CHANNEL, GEOGRAPHY, PRODUCT, and TIME. Each dimension has a single hierarchy.

  1. First generate the scripts for the dimension materialized views. The following statements create the scripts chanmv, prodmv, geogmv, and timemv in /dat1/scripts/drug_depot.

    EXEC DBMS_ODM.CREATEDIMMV_GS
       ('drug_depot', 'channel','chanmv','/dat1/scripts/drug_depot');
    EXEC DBMS_ODM.CREATEDIMMV_GS 
       ('drug_depot', 'product','prodmv','/dat1/scripts/drug_depot');
    EXEC DBMS_ODM.CREATEDIMMV_GS 
       ('drug_depot', 'geography','geogmv','/dat1/scripts/drug_depot');
    EXEC DBMS_ODM.CREATEDIMMV_GS 
       ('drug_depot', 'time','timemv','/dat1/scripts/drug_depot');
    
    
  2. Run the scripts to create the dimension materialized views.

  3. Next create the table of dimension levels for the fact materialized view.

    EXEC DBMS_ODM.CREATEDIMLEVTUPLE('drug_depot', 'drugstore');
    
    

    The table of levels, SYS.OLAPTABLEVELS, is a temporary table specific to your session. You can view the table as follows.

    select * from SYS.OLAPTABLEVELS;
    
    SCHEMA_NAME    DIMENSION_NAME  CUBE_NAME     LEVEL_NAME       SELECTED
    -----------    --------------  ----------    ----------       --------
    DRUG_DEPOT     CHANNEL         DRUGSTORE     TOTAL               1
    DRUG_DEPOT     CHANNEL         DRUGSTORE     CHANNEL_CLASS       1
    DRUG_DEPOT     CHANNEL         DRUGSTORE     CHANNEL_ID          1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     TOTAL               1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     REGION              1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     SUB_REGION          1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     COUNTRY             1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     STATE_PROVINCE      1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     TOTAL               1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     PROD_CATEGORY       1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     PROD_SUBCATEGORY    1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     ID                  1
    DRUG_DEPOT     TIME            DRUGSTORE     Year                1
    DRUG_DEPOT     TIME            DRUGSTORE     Quarter             1
    DRUG_DEPOT     TIME            DRUGSTORE     Month               1
    
    

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

  4. Let's assume that you want to store aggregate data for each region and sub-region, across all channels and all categories of products. You do not care about data at the month level, you only want to store quarter and year data in the materialized view.

    Edit SYS.OLAPTABLEVELS to deselect all CHANNEL levels except total, the state-province level of GEOGRAPHY, sub-categories and individual product IDs in PRODUCT, and month in TIME.

    update SYS.OLAPTABLEVELS set selected = 0 
       where LEVEL_NAME in ('CHANNEL_ID','CHANNEL_CLASS', 'STATE_PROVINCE',
                            'ID','PROD_SUBCATEGORY','Month');  
    select * from sys.olaptablevels;
    
    SCHEMA_NAME    DIMENSION_NAME  CUBE_NAME     LEVEL_NAME       SELECTED
    -----------    --------------  ----------    ----------       --------
    DRUG_DEPOT     CHANNEL         DRUGSTORE     TOTAL               1
    DRUG_DEPOT     CHANNEL         DRUGSTORE     CHANNEL_CLASS       0
    DRUG_DEPOT     CHANNEL         DRUGSTORE     CHANNEL_ID          0
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     TOTAL               1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     REGION              1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     SUB_REGION          1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     COUNTRY             1
    DRUG_DEPOT     GEOGRAPHY       DRUGSTORE     STATE_PROVINCE      0
    DRUG_DEPOT     PRODUCT         DRUGSTORE     TOTAL               1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     PROD_CATEGORY       1
    DRUG_DEPOT     PRODUCT         DRUGSTORE     PROD_SUBCATEGORY    0
    DRUG_DEPOT     PRODUCT         DRUGSTORE     ID                  0
    DRUG_DEPOT     TIME            DRUGSTORE     Year                1
    DRUG_DEPOT     TIME            DRUGSTORE     Quarter             1
    DRUG_DEPOT     TIME            DRUGSTORE     Month               0
    
    
  5. Next create the table SYS.OLAPTABLEVELTUPLES. This table, which is also a session-specific temporary table, contains all the possible combinations of the cube's levels. Each combination of four levels, or grouping set, has an identification number. The grouping sets that include the levels you selected in SYS.OLAPTABLEVELS are marked with a 1 in the SELECTED column.

exec dbms_odm.createcubeleveltuple('drug_depot','drugstore');
select * from sys.olaptableveltuples;

ID       SCHEMA_NAME CUBE_NAME  DIMENSION_NAME   LEVEL_NAME         SELECTED
--       ----------- ---------  --------------   -----------        --------
1        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY        STATE_PROVINCE         0  
1        DRUG_DEPOT  DRUGSTORE  PRODUCT          ID                     0  
1        DRUG_DEPOT  DRUGSTORE  CHANNEL          CHANNEL_ID             0  
1        DRUG_DEPOT  DRUGSTORE  TIME             Month                  0  
2        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY        COUNTRY                0  
2        DRUG_DEPOT  DRUGSTORE  PRODUCT          ID                     0  
2        DRUG_DEPOT  DRUGSTORE  CHANNEL          CHANNEL_ID             0  
2        DRUG_DEPOT  DRUGSTORE  TIME             Month                  0  
.
.
.
112        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                1  
112        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY          1  
112        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                  1  
112        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                1  
113        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      SUB_REGION             1  
113        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY          1  
113        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                  1  
113        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                1  
.
.
.
179        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                 1  
179        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                  1  
179        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                  1  
179        DRUG_DEPOT  DRUGSTORE  TIME           Year                   1  
180        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      TOTAL                  1  
180        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                  1  
180        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                  1  
180        DRUG_DEPOT  DRUGSTORE  TIME           Year                   1  

The SYS.OLAPTABLEVELTUPLES table has 720 rows, identifying 180 unique level tuples, or grouping sets. 180 is the product of the number of levels for each of the cube's dimensions, 3*5*4*3. There are 3 levels in CHANNEL, 5 levels in GEOGRAPHY, 4 levels in PRODUCT, and 3 levels in TIME

Of the 180 grouping sets, only 16 are selected for inclusion in the materialized view. You can display the 64 selected rows (16*4) with the following statement.

select * from sys.olaptableveltuples where SELECTED = 1;

ID       SCHEMA_NAME CUBE_NAME  DIMENSION_NAME   LEVEL_NAME         SELECTED
--       ----------- ---------  --------------   -----------        --------
112        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
112        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
112        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
112        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
113        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      SUB_REGION              1  
113        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
113        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
113        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
114        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
114        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
114        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
114        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
115        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      TOTAL                   1  
115        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
115        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
115        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
117        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
117        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
117        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
117        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
118        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      SUB_REGION              1  
118        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
118        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
118        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
119        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
119        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
119        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
119        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
120        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
172        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
172        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
172        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
172        DRUG_DEPOT  DRUGSTORE  TIME           Year                    1  
173        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      SUB_REGION              1  
173        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
173        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
173        DRUG_DEPOT  DRUGSTORE  TIME           Year                    1  
174        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
174        DRUG_DEPOT  DRUGSTORE  PRODUCT       PROD_CATEGORY            1  
174        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
174        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
175        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     TOTAL                    1  
175        DRUG_DEPOT  DRUGSTORE  PRODUCT       PROD_CATEGORY            1  
175        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
175        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
177        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     COUNTRY                  1  
177        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
177        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
177        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
178        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     SUB_REGION               1  
178        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
178        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
178        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
179        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     REGION                   1  
179        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
179        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
179        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
180        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  

  1. Suppose you want to store product totals by year for each sub-region. You do not want to store aggregates for any other grouping sets that contain the sub-region level.

    Grouping sets 113, 118, 173, and 178 all use the SUB_REGION level of GEOGRAPHY.

    ID      GEOGRAPHY       PRODUCT            CHANNEL      TIME
    --      ----------      -------            ------       -----
    113     SUB_REGION      PROD_CATEGORY      TOTAL        Quarter
    118     SUB_REGION      TOTAL              TOTAL        Quarter
    173     SUB_REGION      PROD_CATEGORY      TOTAL        Year
    178     SUB_REGION      TOTAL              TOTAL        Year
    
    

    You could edit the SYS.OLAPTABLEVELTUPLES table with a statement like the following.

update SYS.OLAPTABLEVELTUPLES set selected = 0 
        where ID in ('113','118', '173');  
select * from sys.olaptableveltuples where SELECTED = 1;

ID       SCHEMA_NAME CUBE_NAME  DIMENSION_NAME   LEVEL_NAME         SELECTED
--       ----------- ---------  --------------   -----------        --------
112        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
112        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
112        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
112        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
114        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
114        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
114        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
114        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
115        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      TOTAL                   1  
115        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
115        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
115        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
117        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
117        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
117        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
117        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
119        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
119        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
119        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
119        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
120        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  PRODUCT        TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
120        DRUG_DEPOT  DRUGSTORE  TIME           Quarter                 1  
172        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      COUNTRY                 1  
172        DRUG_DEPOT  DRUGSTORE  PRODUCT        PROD_CATEGORY           1  
172        DRUG_DEPOT  DRUGSTORE  CHANNEL        TOTAL                   1  
172        DRUG_DEPOT  DRUGSTORE  TIME           Year                    1  
174        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY      REGION                  1  
174        DRUG_DEPOT  DRUGSTORE  PRODUCT       PROD_CATEGORY            1  
174        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
174        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
175        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     TOTAL                    1  
175        DRUG_DEPOT  DRUGSTORE  PRODUCT       PROD_CATEGORY            1  
175        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
175        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
177        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     COUNTRY                  1  
177        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
177        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
177        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
178        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     SUB_REGION               1  
178        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
178        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
178        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
179        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     REGION                   1  
179        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
179        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
179        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  
180        DRUG_DEPOT  DRUGSTORE  GEOGRAPHY     TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  PRODUCT       TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  CHANNEL       TOTAL                    1  
180        DRUG_DEPOT  DRUGSTORE  TIME          Year                     1  

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

    exec dbms_odm.createfactmv_gs
          ('drug_depot','drugstore',
           'drugstore_mv','/dat1/scripts/drug_depot',TRUE);
    
    

    The CREATE MATERIALIZED VIEW statement in the script contains the following grouping sets in the GROUP BY GROUPING SETS clause.

    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL,
        GEOGRAPHIES.REGION, GEOGRAPHIES.SUB_REGION, GEOGRAPHIES.COUNTRY ),
    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL,
        GEOGRAPHIES.REGION), 
    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL),
    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL , GEOGRAPHIES.REGION, 
        GEOGRAPHIES.SUB_REGION, GEOGRAPHIES.COUNTRY), 
    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL, GEOGRAPHIES.REGION),
    (TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER, CHANNELS.TOTAL,
        PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, 
        PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL, GEOGRAPHIES.REGION,
        GEOGRAPHIES.SUB_REGION, GEOGRAPHIES.COUNTRY), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, 
        PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL, GEOGRAPHIES.REGION), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, 
        PRODUCTS.PROD_CATEGORY, GEOGRAPHIES.TOTAL), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL,
        GEOGRAPHIES.REGION, GEOGRAPHIES.SUB_REGION, GEOGRAPHIES.COUNTRY), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL,
        GEOGRAPHIES.REGION, GEOGRAPHIES.SUB_REGION), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL,
        GEOGRAPHIES.REGION), 
    (TIMES.CALENDAR_YEAR, CHANNELS.TOTAL, PRODUCTS.TOTAL, GEOGRAPHIES.TOTAL) 
    
    

    The following statement at the end of the script sets the MV_SUMMARY_CODE associated with the cube in the OLAP Catalog. This setting indicates that the materialized view associated with this cube is in grouping set form.

    execute cwm2_olap_cube.set_mv_summary_code
         ('DRUG_DEPOT', 'DRUGSTORE', 'GROUPINGSET') ;
    
    
  2. Run the drugstore_mv script to create the fact materialized view.