| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
This chapter explains how to create materialized views specific to the requirements of the OLAP API and the BI 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 Data Warehousing Guide for information on managing materialized views. |
This chapter includes the following topics:
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 is 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 13-1.
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 5 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,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 procedures in the DBMS_ODM package use two tables, SYS.OLAPTABLEVELS and SYS.OLAPTABLEVELTUPLES, to construct the level combinations in each grouping set. For information on generating and editing these tables, see "Procedure: Create Grouping Set Materialized Views".
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. The aggregation method associated with each measure is specified in the OLAP Catalog metadata for the measure.
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. |
Follow these steps to create grouping set 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.
Enable your database to write scripts to a file by setting the UTL_FILE_DIR parameter to a valid directory.
Log in to SQL*Plus using the identity of the metadata owner.
Delete any materialized views that currently exist for the cube.
Create scripts to generate the dimension materialized views. Execute DBMS.CREATEDIMMV_GS for each of the cube's dimensions.
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.OLAPTABLEVELTUPLES. 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.
Optionally, edit the scripts using any text editor.
Run the scripts in SQL*Plus, using commands such as the following:
@/users/oracle/OraHome1/olap/mvscript.sql;
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.
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.
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|