Using the DBMS_ODM Package - OLAP

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.

Procedure: Create Grouping Set Materialized Views

Follow these steps to create grouping set 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. Enable your database to write scripts to a file by setting the UTL_FILE_DIR parameter to a valid directory.
  3. Log in to SQL*Plus using the identity of the metadata owner.
  4. Delete any materialized views that currently exist for the cube.
  5. Create scripts to generate the dimension materialized views. Execute DBMS.CREATEDIMMV_GS for each of the cube's dimensions.
  6. Use the following three step procedure to create a script to generate a grouping set materialized view for the cube's fact table:
    • 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.
      By default, all the levels of all the dimensions are selected for inclusion in the materialized view. If you know that you will not need to store aggregate data for some levels, you can edit the table to deselect those levels.
    • Execute DBMS_ODM.CREATECUBELEVELTUPLE to create the table SYS.OLAP TABLE VELTUPLES. This table lists all the possible combinations (grouping sets) of the cube's levels. Only the grouping sets that include the levels selected in SYS.OLAPTABLEVELS are selected for inclusion in the materialized view. If you know that you will not need to store aggregate data for some of these level combinations, you can edit the table to deselect those combinations
    • Execute DBMS_ODM.CREATEFACTMV_GS to create the script.
  7. Optionally, edit the scripts using any text editor.
  8. Run the scripts in SQL*Plus, using commands such as the following:
    @/users/oracle/OraHome1/olap/mvscript.sql;

Example: Create Grouping Set Materialized Views for a Sales Cube

Let's assume that you want to create 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.
  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.
    Example: Create Grouping Set Materialized Views for a Sales CubeExample: Create Grouping Set Materialized Views for a Sales Cube
    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.
    Using the DBMS_ODM Package
  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.
    Using the DBMS_ODM Package
    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; Using the DBMS_ODM Package
    Using the DBMS_ODM Package
  6. 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.

    You could edit the SYS.OLAPTABLEVELTUPLES table with a statement like the following.
    SYS.OLAPTABLEVELTUPLES table with a statement
    SYS.OLAPTABLEVELTUPLES table with a statement
  7. To create the script that will generate the fact materialized view, run the CREATEFACTMV_GS procedure.
    The CREATE MATERIALIZED VIEW statement in the script contains the following grouping sets in the GROUP BY GROUPING SETS clause.
    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.
  8. Run the drugstore_mv script to create the fact materialized view.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics