Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

7.3. Management of Aggregates

An aggregate table is an unusual database construct, designed and developed like a table, but functioning like an index. Table designs are generally managed by data modelers and governed by formal projects; indexes are usually the responsibility of a DBA and subject to less formal change control processes. This can create confusion and uncertainty surrounding the maintenance of aggregates. Who is responsible for managing the aggregates, and how are changes handled?

The primary maintenance responsibilities associated with aggregates must be defined and assigned to individuals to ensure the proper day-to-day management of aggregates. Ad hoc changes to the aggregate portfolio are generally to be avoided in favor of project-centered adjustment. Changes to the aggregate schema are likely to be required more often than changes to the base schema, so smaller, aggregate-focused projects may be necessary.

7.3.1. Maintenance Responsibilities

Specific responsibilities related to aggregates must be acknowledged to ensure the proper maintenance of aggregate tables. These key responsibilities will be incorporated into one or more existing project roles. By assigning these responsibilities to existing roles, any ambiguities are eliminated.

The primary maintenance responsibilities for aggregates include:

• Monitoring of aggregate refresh process

• Correction of aggregate processing errors

• Management of the aggregate navigator

The individual responsible for monitoring ...