Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

6.6. Materialized Views and Materialized Query Tables

Back-end database technologies, such as Oracle's materialized views and DB2's materialized query tables, are frequently used to implement aggregate navigation functionality. As discussed in Chapter 4, these technologies can also be used to build and maintain the aggregates.

These capabilities eliminate the need to design an ETL process for the construction of the aggregate tables. As discussed in Chapter 5, it is necessary only to define the aggregate, properly establish the rules that govern when the aggregate will participate in query rewrite, and set up an automated call to the database to refresh the aggregate after base table loads have completed.

While the RDBMS will handle most of the remaining additional work, there are cases where a tweak to the base schema can go a long way toward optimizing performance and keeping down the size of the aggregate table.

6.6.1. Defining Attributes for Aggregate Dimensions

Oracle and IBM provide the capability to define aggregates that partially summarize a dimension, without the need to construct the aggregate dimension table itself. Both products enable this capability by allowing the developer to define hierarchies that exist within the dimension. As discussed in Chapter 4, this is done in Oracle via the declaration of a dimension. In DB2, this is done by establishing a cube view.

Each level of a hierarchy must include a defining attribute or set of attributes. The hierarchy may ...