| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
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:
|
This chapter includes the following topics:
Example: Automatically Generate the Materialized Views for a Price Cube
Example: Manually Generate the Materialized Views for a Sales Cube
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.
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 theDBMS_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 |
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 |
|---|---|---|
|
|
|
1 |
|
|
|
2 |
|
|
|
1 |
|
|
|
1 |
|
|
|
2 |
For the cube's fact table, the OLAP API requires a single grouping set materialized view.
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.
The SQL script for creating dimension materialized views includes a CREATE MATERIALIZED VIEW statement, and statements for generating statistics and bitmap indexes.
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.
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;
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 ;
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.
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.
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;
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 ;
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 byDBMS_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:
Automatically generate a materialized view that includes 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 includes some of the level combinations in the cube.
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 includes 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.
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. See "Example: Automatically Generate the Materialized Views for a Price Cube".
MINIMUM — Minimally materialize the cube's data. Include a subset of the level combinations in the materialized view. See Oracle OLAP Reference for more information.
PERCENT — Materialize the cube's data based on a percentage of the cube's level combinations. See Oracle OLAP Reference for more information.
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".
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/mvscript_fact.sql;
For an example of this process, see "Example: Manually Generate the Materialized Views for a Sales 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.
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 prodmv and timemv scripts to create the dimension materialized views in the default tablespace for the GLOBAL schema.
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');
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.
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.
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');
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('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.
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
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
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
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') ;
Run the drugstore_mv script to create the fact materialized view.
