Page 175 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 9: Data Warehouse Refreshment – II

requirements for using Materialized views with aggregates notes

Table 9.1 Illustrates the aggregate requirements for materialized views.

table 9.1: requirements for Materialized views with aggregates

if aggregate x is present, aggregate y is required and aggregate Z is optional
X Y Z
COUNT(expr) - -
SUM(expr) COUNT(expr) -
AVG(expr) COUNT(expr) SUM(expr)
STDDEV(expr) COUNT(expr) SUM(expr) SUM(expr * expr)
VARIANCE(expr) COUNT(expr) SUM(expr) SUM(expr * expr)

Note COUNT(*) must always be present to guarantee all types of fast refresh.
Otherwise, you may be limited to fast refresh after inserts only. Oracle recommends that
you include the optional aggregates in column Z in the materialized view in order to
obtain the most efficient and accurate fast refresh of the aggregates.

Task Suppose that a data warehouse for a university consists of the following
for dimension: Student, course, Semester and Instructor, and two measures count and
avg _grade. When at the lowest conceptual level (e.g. for a given student, course, semester
and instructor combination), the avg_grade measure stores the actual course grade of the
student at the higher conceptual levels, avg_grade stores the average grade for the given
combination.
1. Draw a snowflake schema diagram for the data warehouse.
2. Starting with base cuboids, what specific OLAP operations should one perform in
order to list the average grade of CS courses for each student.
3. If each dimension has five levels (including all), such as student <major <status<
university<all, how many cuboids will this cube contain (including the base and
appes cuboids)

9.2.2 Materialized views containing only Joins

Some materialized views contain only joins and no aggregates, such as in example, where a
materialized view is created that joins the sales table to the times and customers tables.
The advantage of creating this type of materialized view is that expensive joins will be
precalculated.

Fast refresh for a materialized view containing only joins is possible after any type of DML to the
base tables (direct-path or conventional INSERT, UPDATE, or DELETE).
A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON
DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that
does DML on the materialized view’s detail table.

LoveLy professionaL university 169