Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
Creating Fact Materialized Views With OLAP Summary Advisor , 4 of 5
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:
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.SELECT
list contains all GROUP BY
columns.GROUPING_ID
and GROUP BY
clauses.The following example shows the basic syntax.
CREATE MATERIALIZED VIEWmvname
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 FROMdimtable1
,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.
|
![]() Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|