24 DBMS_ODM

The OLAP Data Management package, DBMS_ODM, provides procedures for creating materialized views specific to the requirements of the OLAP API.

See Also:

Oracle Data Warehousing Guide for information on creating and managing materialized views
Oracle OLAP Application Developer's Guide for information on summary management for Oracle OLAP

This chapter includes the following topics:

Summary Management with Materialized Views
Summarizing the Fact Table
Example: Create Materialized Views for a Sales Cube
Summary of DBMS_ODM Subprograms

24.1 Summary Management with Materialized Views

Summary management for relational warehouses is managed by the query rewrite facility in the database. 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. However, the OLAP API can only use query rewrite when the materialized views have a specific format. The procedures in the DBMS_ODM package create materialized views that satisfy the requirements of the OLAP API.

When the source data is stored in an analytic workspace, materialized views are not needed. The native multidimensional structures within analytic workspaces support both stored summarization and run-time aggregation. You can move your data from a star schema to an analytic workspace with the DBMS_AWM package or with Analytic Workspace Manager.

24.1.1 Grouping Sets

The DBMS_ODM package creates a set of materialized views based on a cube defined in the OLAP Catalog. The cube must be mapped to a star schema with a single fact table containing only lowest level data.

Scripts generated by DBMS_ODM procedures create the following materialized views:

A dimension materialized view for each hierarchy of each of the cube's dimensions
A fact materialized view, created with GROUP BY GROUPING SETS syntax, for the cube's measures

Each grouping set generated by the CREATE MATERIALIZED VIEW statement identifies a unique combination of levels. With grouping sets, you can summarize your data symmetrically, for example sales at the month level across all levels of geography, or you can summarize it asymmetrically, for example sales at the month level for cities and at the quarter level for states.

24.2 Summarizing the Fact Table

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:

Automatically generate a materialized view that defines the summaries for every level combination in the cube.

This option may potentially generate a very large materialized view, depending on the size of the fact table. In general, you should use this option only if disk space is plentiful.
Automatically generate a materialized view that defines minimal summarization for the cube. The materialized view will include only the most aggregate level and one level above the least aggregate level for each dimension.

This option will generate a materialized view of moderate size, depending on the size of the fact table. The summarization will be symmetric.
Automatically generate a materialized view that defines summarization for a percentage of the level combinations in the cube.

This option may generate a materialized view of moderate size, depending on the size of the fact table and the percentage that you specify. The level combinations included in the materialized view will be random. The summarization will typically be asymmetric.
Manually choose the level combinations to be included in the materialized view for the cube.

With this option, you can finely tune both the content and the size of the materialized view. The summarization may be symmetric or asymmetric.

Note:

If you have specified the same aggregation operator for each of the cube's dimensions, this operator will be used to aggregate the data for the fact materialized view. You can set an aggregation operator for a cube in Enterprise Manager, or you can use the CWM2 procedure, SET_AGGREGATION_OPERATOR Procedure, described.

If you have specified an aggregation operator for some or none of the cube's dimensions, the data will by summarized by addition.

For a list of aggregation operators supported by the OLAP Catalog, see Table 1-10, "Aggregation Operators".

24.2.1 Procedure: Automatically Generate the Materialized Views

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

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.
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.
Log into SQL*Plus using the identity of the metadata owner.
Delete any materialized views that currently exist for the cube. Execute DROP MATERIALIZED VIEW mv_name for each materialized view you wish to delete.
Create scripts to generate the dimension materialized views. Execute DBMS_ODM.CREATEDIMMV_GS for each of the cube's dimensions.
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:
- FULL — Fully materialize the cube's data. Include every level combination in the materialized view.
- MINIMUM — Minimally materialize the cube's data. Include the level above the leaf level and the most aggregate level for each dimension in the materialized view.
- PERCENT — Materialize the cube's data based on a percentage of the cube's level combinations.
Run the scripts in SQL*Plus, using commands such as the following:
```
@/users/oracle/OraHome1/olap.101/mvscript.sql;
```

24.2.2 Procedure: Manually Generate the Materialized Views

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

Follow the first five steps in "Procedure: Automatically Generate the Materialized Views".
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.
Run the scripts in SQL*Plus, using commands such as the following:
```
@/users/oracle/OraHome1/olap.101/mvscript_fact.sql;
```

24.3 Example: Create Materialized Views for a Sales Cube

Let's assume that you want to create 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.

You want to summarize product families by month and product classes by quarter and make that data available in a materialized view.

First 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');

Run the scripts to create the dimension materialized views.

Next 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. 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.

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

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 * from sys.olaptableveltuples;

ID SCHEMA_NAME CUBE_NAME  DIMENSION_NAME DIMENSION_OWNER LEVEL_NAME SELECTED
-- ----------- ---------  -------------- --------------- ---------- --------
7   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          CLASS         1
7   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Quarter       1
6   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          FAMILY        1
6   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Quarter       1
3   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          CLASS         1
3   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Month         1
2   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          FAMILY        1
2   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Month         1

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 ('6', '3');  
select * from sys.olaptableveltuples where SELECTED = 1;

ID SCHEMA_NAME CUBE_NAME  DIMENSION_NAME DIMENSION_OWNER LEVEL_NAME SELECTED
-- ----------- ---------  -------------- --------------- ---------- --------
7   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          CLASS         1
7   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Quarter       1
2   GLOBAL     PRICE_CUBE   PRODUCT      GLOBAL          FAMILY        1
2   GLOBAL     PRICE_CUBE   TIME         GLOBAL          Month         1

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','/users/global/scripts',TRUE);

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

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) )

The final statement in 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
     ('GLOBAL', 'PRICE_CUBE', 'GROUPINGSET') ;

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

Summary of DBMS_ODM Subprograms

Table 24-1 DBMS_ODM Subprograms

Subprogram	Description
CREATECUBELEVELTUPLE Procedure	Creates a table of level combinations to be included in the materialized view for a cube.
CREATEDIMLEVTUPLE Procedure	Creates a table of levels to be included in the materialized view for a cube.
CREATEDIMMV_GS Procedure	Generates a script that creates a materialized view for each hierarchy of a dimension.
CREATEFACTMV_GS Procedure	Generates a script that creates a materialized view for the fact table associated with a cube. The materialized view includes individual level combinations that you have previously specified.
CREATESTDFACTMV Procedure	Generates a script that creates a materialized view for the fact table associated with a cube. The materialized view is automatically constructed according to general instructions that you provide.

CREATECUBELEVELTUPLE Procedure

This procedure creates the table sys.olaptableveltuples, which lists all the level combinations to be included in the materialized view for the cube. By default, all level 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.

Before calling this procedure, call CREATEDIMLEVTUPLE to create the table of levels for the cube.

Syntax

CREATECUBELEVELTUPLE (
               cube_owner     IN   VARCHAR2,
               cube_name      IN   VARCHAR2);

Parameters

Table 24-2 CREATECUBELEVELTUPLE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.

"Example: Create Materialized Views for a Sales Cube"

CREATEDIMLEVTUPLE Procedure

This procedure creates the table sys.olaptablevels, which lists all the levels of all the dimensions of the cube. By default, all levels are selected for inclusion in the materialized view. You can edit the table to deselect any levels that you do not want to include.

After calling this procedure, call CREATECUBELEVELTUPLE to create the table of level combinations (level tuples) for the cube.

Syntax

CREATEDIMLEVTUPLE (
               cube_owner    IN varchar2,
               cube_name     IN varchar2);

Parameters

Table 24-3 CREATEDIMLEVTUPLE Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.

"Example: Create Materialized Views for a Sales Cube"

CREATEDIMMV_GS Procedure

This procedure generates a script that creates a materialized view for each hierarchy of a dimension. You must call this procedure for each dimension of a cube.

The process of creating the dimension materialized views is the same whether you generate the fact materialized view automatically or manually.

Syntax

CREATEDIMMV_GS (
               dimension_owner    IN   VARCHAR2,
               dimension_name     IN   VARCHAR2,
               output_file        IN   VARCHAR2,
               output_path        IN   VARCHAR2,
               tablespace_mv      IN   VARCHAR2 DEFAULT NULL,
               tablespace_index   IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 24-4 CREATEDIMMV_GS Procedure Parameters

Parameter	Description
dimension_owner	Owner of the dimension.
`dimension_name`	Name of the dimension.
`output_file`	File name for the output script.
`output_path`	Directory path where `output_file` will be created. This may be either a directory object or a path set by the `UTL_FILE_DIR` parameter.
`tablespace_mv`	The name of the tablespace in which the materialized view will be created. When this parameter is omitted, the materialized view is created in the user's default tablespace.
`tablespace_index`	The name of the tablespace in which the index for the materialized view will be created. When this parameter is omitted, the index is created in the user's default tablespace.

"Procedure: Manually Generate the Materialized Views"

"Example: Create Materialized Views for a Sales Cube"

CREATEFACTMV_GS Procedure

This procedure generates a script that creates a materialized view for the fact table associated with a cube.

Prior to calling this procedure, you must call CREATEDIMLEVTUPLE and CREATECUBELEVELTUPLE to create the sys.olaptableveltuples table. The materialized view will include all level combinations selected in the sys.olaptableveltuples table.

Syntax

CREATEFACTMV_GS (
               cube_owner              IN   VARCHAR2,
               cube_name               IN   VARCHAR2,
               outfile                 IN   VARCHAR2,
               outfile_path            IN   VARCHAR2,
               partitioning            IN   BOOLEAN,
               tablespace_mv           IN   VARCHAR2 DEFAULT NULL,
               tablespace_index        IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 24-5 CREATEFACTMV_GS Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`output_file`	File name for the output script.
`output_path`	Directory path where `output_file` will be created.This may be either a directory object or a path set by the `UTL_FILE_DIR` parameter.
`partitioning`	`TRUE` turns on index partitioning; `FALSE` turns it off.
`tablespace_mv`	The name of the tablespace in which the materialized view will be created. When this parameter is omitted, the materialized view is created in the user's default tablespace.
`tablespace_index`	The name of the tablespace in which the index for the materialized view will be created. When this parameter is omitted, the index is created in the user's default tablespace.

See Also

"Summarizing the Fact Table"

"Example: Create Materialized Views for a Sales Cube"

CREATESTDFACTMV Procedure

This procedure generates a script that creates a materialized view for the fact table associated with a cube.

This procedure automatically generates and updates the tables of levels and level tuples. If you want to edit these tables yourself, you must use the CREATEDIMLEVTUPLE, CREATECUBELEVELTUPLE, and CREATEFACTMV_GS procedures.

Syntax

CREATESTDFACTMV (
               cube_owner              IN   VARCHAR2,
               cube_name               IN   VARCHAR2,
               outfile                 IN   VARCHAR2,
               outfile_path            IN   VARCHAR2,
               partitioning            IN   BOOLEAN,
               materialization_level   IN   VARCHAR2,
               tablespace_mv           IN   VARCHAR2 DEFAULT NULL,
               tablespace_index        IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 24-6 CREATESTDFACTMV Procedure Parameters

Parameter	Description
`cube_owner`	Owner of the cube.
`cube_name`	Name of the cube.
`output_file`	File name for the output script.
`output_path`	Directory path where `output_file` will be created.This may be either a directory object or a path set by the `UTL_FILE_DIR` parameter.
`partitioning`	`TRUE` turns on index partitioning; `FALSE` turns it off.
`tablespace_mv`	The name of the tablespace in which the materialized view will be created. When this parameter is omitted, the materialized view is created in the user's default tablespace.
`materialization_level`	The level of materialization. This parameter identifies the level combinations that will be included in the materialized view. Specify one of the following values: `FULL` — Fully materialize the cube's data. Include every level combination in the materialized view. `MINIMUM` — Minimally materialize the cube's data. Include the level above the leaf level for each dimension and the most aggregate level for each dimension in the materialized view. `PERCENT` — Materialize the cube's data based on a percentage of the cube's level combinations. For example, consider a cube that has two dimensions with three levels and one dimension with four levels. This cube has 36 possible level combinations (334). If you choose to materialize the cube by 30%, then 12 level combinations will be included in the materialized view.
`tablespace_index`	The name of the tablespace in which the index for the materialized view will be created. When this parameter is omitted, the index is created in the user's default tablespace.

See Also

"Summarizing the Fact Table"