The MV CREATE Statement With Concatenated Rollup

The following example shows the basic structure of the SQL statements generated by OLAP Summary Advisor to create a concatenated rollup style fact MV for the OLAP API. The following general characteristics apply:

• The SELECT statement contains SUM(column) and COUNT(column) function calls for all measures in the cube (that is, all aggregated columns in the fact table), and a COUNT(*) function call.
• The SELECT list contains all GROUP BY columns.
• The list of level key columns always appear in the exact same order, especially in the GROUPING_ID and GROUP BY clauses.

The following example shows the basic syntax.

CREATE MATERIALIZED VIEW mvname
partition by range (gid)
(partition values less than (1),
     .
     .
     .
partition values less than (MAXVALUE))
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT SUM(measure1) target, COUNT(measure1) target,
   SUM(measure2) target, COUNT(measure2) target,
     .
     .
     .
   COUNT(*) COUNT_OF_STAR, select_list 
 hierarch1_level1, hierarch1_level2, ..., 
 hierarch2_level1, hierarch2_level2,... 
   GROUPING_ID(hierarch1_level1, hierarch1_level2, ...,
    hierarch2_level1, hierarch2_level2,... ) gid
FROM dimtable1, dimtable2,...
WHERE (dim_key1=fact_key1) AND (dim_key2=fact_key2)...AND...
GROUPBY
 hierarch1_level1, ROLLUP(hierarch1_leveln2,... hierarch1_leveln),
 hierarch2_level1 ROLLUP(hierarch2_leveln2,... hierarch2_leveln,
     .
     .
     .
 hierarchn_level1 ROLLUP(hierarchn_level2... hierarchn_leveln)

where:

measure1, measure 2... are the measures in the fact table.

select_list are the dimension levels from hierarch1_level1 to hierarchn_leveln.

hierarch1...hierarchn are the dimension hierarchies, beginning with the hierarchy with the most levels (1) and ending with the hierarchy with the fewest levels (n). Note that this ordering is important.

level1...leveln are the columns in the related dimension tables, from the highest (1) to the lowest (n) levels of aggregation.

dim_key is the key column in the dimension table.

fact_key is the related column in the fact table.