VDM Public

Public information; No registration required

Maintenance Hurtles

Should we use system or user-maintained MQTs? What does it take to implement a complete system of MQT maintenance, encompassing complete refresh, incremental and recasting processes and utility scripts that are robust, maintainable and restartable? Such processes must be designed with operability in mind, i.e. they must have predictable and reasonable run times, must allow monitoring, provide regular progress status, must be idempotent and restartable, and must be adaptable for special fixes and manual corrections.

VDM Access: 
VDM Public

Database vs. Application controlled Redirection

What factors are involved in deciding if DB2 should rewrite the query redirecting to the right MQT or should we delegate it to the application? PMO stands for "Poor Man’s Optimization", a term jokingly used to describe a mechanism, implemented through union views, that redirected queries to a desired MQT level. Some BI applications such as Business Objects and Microstrategy provide ways for achieving the same goal. Should we fully rely on optimizer-induced redirection to the MQTs or should we force the level in the application? How can one achieve application-driven redirection?

VDM Access: 
VDM Public

Multi-Fact Cubes and Metric Integration

Most BI reports involve more than one fact table whose metrics need to be pulled together. Pivot union views can be used to combine metrics from multiple facts of same (or almost same) dimensionality while exploiting their MQTs. Two key considerations in building such views are:

Maintain best performance for widest range of queries
The structure of pivot union views typically entails a small number of members in a "union all" query, each of the members involving a fact joined to some dimension(s). The sources of inefficiency can stem from two situations:
  1. Queries that can be answered using a subset of the union members should not be burdened accessing the unneeded members.
VDM Access: 
VDM Public

Union/Range Partitioning and Aggregation Symmetry

It often happens that facts are not a single table, but a union of tables. Why such practice is likely to remain even after range partitioning and roll-in/roll-out functionality becomes available in DB2? How should we approach the MQT definitions over union-partitioned fact tables? How can we ensure that the optimizer will rewrite the queries to use the MQTs? How should we deal with the opposite problem, where a yearlong base table is covered by 12 period MQTs?

VDM Access: 
VDM Public

Irregular Period Aggregations

Although most aggregations are made over a fixed period hierarchy, certain applications require aggregates to be computed and maintained over irregular periods defined by a list of boundary dates. Such aggregations pose special challenges.

VDM Access: 
VDM Public

MQT Dimension Design - Options

Virtual Dimensions

Define the dimensionality of the OLAP cube the base fact belongs to, and include all of these dimensions to each of the family of MQTs that implements the cube. For the purposes of aggregation, you will find that there are logical (virtual) dimensions involved that are simple attributes in the basic dimensions, or that there are multiple hierarchies implemented in one dimension. For example, a location dimension may have store as its base level and store type, or store size be attributes. If you want to report sales at the district, region and division levels by store type or store size, then these two attributes should be viewed as independent dimensions for aggregation purposes.

VDM Access: 
VDM Public

The Double-Dipping Query Pattern and MQT Optimization in DB2

This article describes a general query pattern that helps keep aggregations to an efficient size. The pre-optimization (rewrite) step in DB2 is capable of rewriting a query using this pattern, so that it can use a materialized query table (MQT) even if some of the columns needed are not included in the MQT.

VDM Access: 
VDM Public

Aggregations

We start with a framework for reasoning about aggregations given the technology at hand, including methodology and standards for designing, maintaining and automating the deployment and maintenance of redundant tables, and the automation of the process. We address these and more questions as we approach real problems in aggregation design. Although the motivation for the approaches discussed comes from specific experiences, we try to evaluate a variety of options available and propose pros and cons.

VDM Access: 
VDM Public

The Aggregation Lattice Framework

A successful performance strategy for very large databases (VLDB) often involves aggregations. This is why major DBMS vendors offer mature system support for aggregation maintenance and optimization. DB2 offers Materialized Query Tables (MQT), Teradata offers Aggregate Join Indexes (AJI), Oracle offers Materialized Views (MV). BI tool vendors support aggregate awareness and dynamic sourcing, while OLAP technologies offer cost-effective, in-memory retention alternatives.
VDM Access: 
VDM Public

Pages