Example: Fact Materialized View - OLAP

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.

CREATE Fact Materialized View

The basic syntax of the CREATE MATERIALIZED VIEW statement with grouping sets for a fact table is as follows.

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.

Bitmap Indexes for Fact Materialized Views

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.

Statistics for Fact Materialized Views

The script includes statements like the following to generate statistics.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics