Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

2.3. Assessing the Value of Potential Aggregates

After identifying a pool of potential aggregates, the next step is to sort through them and determine which ones to build. Three major factors will come into play. First, the resources available in your data warehouse environment will dictate how many aggregates can be built. Second, the aggregates must be compared to the base table (and each other) to determine what real performance benefit is provided. Last, each aggregate must be gauged by how many users it serves, and potentially the relative importance of different users.

2.3.1. Number of Aggregates

The production data warehouse environment works to limit the number of aggregates that is practical to deploy in two ways. First, the technology available to perform aggregate navigation may limit the number of aggregates that it makes sense to build. Second, a portfolio of aggregates gets very large, very fast. The available computing resources will place a maximum cap on the amount of space you are willing to reserve for aggregates.

2.3.1.1. Presence of an Aggregate Navigator

The number of aggregates that can reasonably be managed is largely determined by the presence or absence of an aggregate navigator, and by the capability of the navigator. Recall from Chapter 1 that the aggregate navigator's function is to transform base-level SQL into aggregate-aware SQL.

If there is no aggregate navigator, end users are forced to make choices about which aggregate to use. Most end users ...