Page 178 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes /*create materialized view join_sales_cust_time as fast refreshable at
COMMIT time */

CREATE MATERIALIZED VIEW join_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
To create a nested materialized view on the table join_sales_cust_time, you would have to create
a materialized view log on the table. Because this will be a single-table aggregate materialized
view on join_sales_cust_time, you need to log all the necessary columns and use the INCLUDING
NEW VALUES clause.
/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)

INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time on
join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week

FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;

nesting Materialized views with Joins and aggregates

Some types of nested materialized views cannot be fast refreshed. Use EXPLAIN_MVIEW to
identify those types of materialized views. You can refresh a tree of nested materialized views in
the appropriate dependency order by specifying the nested = TRUE parameter with the DBMS_
MVIEW. REFRESH parameter. For example, if you call DBMS_MVIEW.REFRESH (‘SUM_SALES_
CUST_TIME’, nested => TRUE), the REFRESH procedure will first refresh the join_sales_cust_time
materialized view, and then refresh the sum_sales_cust_time materialized view.

9.3 towards a Quality-oriented refreshment process

Data warehousing is a new technology which provides software infrastructure for decision
support systems and OLAP applications. Data warehouses collect data from heterogeneous and
distributed sources. This data is aggregated and then customized with respect to organizational
criteria defined by OLAP applications. The data warehouse can be defined as a hierarchy of data
stores which goes from source data to the highly aggregated data (data marts). Between these
two extreme data stores, we can find different other stores depending on the requirements of
OLAP applications. One of these stores is the operational data store which reflects source data
in a uniform and clean representation. The corporate data warehouse (CDW) contains highly

172 LoveLy professionaL university