| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The OLAP Data Management package, DBMS_ODM, provides procedures for creating materialized views specific to the requirements of the OLAP API.
|
See Also:
|
This chapter includes the following topics:
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.
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.
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 theCWM2 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". |
Follow 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;
Follow 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:
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.
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.
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;
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.
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. |
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. |
See Also
"Procedure: Manually Generate the Materialized Views"
"Example: Create Materialized Views for a Sales Cube"
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. |
See Also
"Procedure: Manually Generate the Materialized Views"
"Example: Create Materialized Views for a Sales Cube"
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
See Also
"Procedure: Automatically Generate the Materialized Views"
"Procedure: Manually Generate the Materialized Views"
"Example: Create Materialized Views for a Sales Cube"
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
See Also
"Example: Create Materialized Views for a Sales Cube"
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
See Also
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|