Aggregation for Data Warehouse, Part 4
Posted by Dylan Wan on May 30, 2007
I have briefly mentioned the various approaches of providing aggregation for data warehouse. This post describes the third approach of using the Materialized View (Oracle) or Automated Summary Table (AST, DB2) from the database system. I will describe the Pro’s and Con’s of using this approach.
A materialized view, like a table or a view, is a database object. A materialized view can contain the results of a query. It enables you to have a very efficient data access to the pre-aggregated data. It also helps you to maintain such aggregated data by simply defining the query, like creating a database view.
Materialized view is a major architecture component of Oracle Daily Business Intelligence. The beauty of the materialized view comes with the features of automated refresh and query rewrite. Theoretically, you do not need to be aware of it. You can access the underlying tables in your query from any client tool. The database system will handle the rest for you. The optimizer (the engine handles your SQL request) can automatically determine whether the MV exists and should be used.
Here are the Pro’s and Con’s of using Materialized View approach:
- Significant improvement in response time for online query.
- View easy to implement
- Once enabled within the database, accessible everywhere
- Delay of the data: The data available from a materialized view needs to be refreshed periodically. So how current the data is, is dependent on how often you refresh the data.
- Storage: The volume of data can grow very fast. You need to manage the space very carefully.
- Refresh Performance: You can refresh materialized view using different options, which affects the performance of refresh significantly. Normally a materialized view is refreshed based on the materialized view logs. Only the rows changed on the master tables are sent to the materialized. However, in some cases, such as changing the view definitions, you may need to do a complete refresh of the data.