Life Cycle of Aggregate Tables

The following diagram summarizes the stages in the life cycle of an aggregate table.

Defined
System-Defined Tables
The engine determines that an aggregate table is needed. The determination is based on predicted need or on query history, statistics, and other criteria. The engine creates the definition of the aggregate table.

User-Defined Tables
You define an aggregate table that conforms to one of the three use cases discussed in When to Define Your Own Aggregate Tables.

Built
System-Defined Tables
The next build process for the corresponding cube builds the first instance of the aggregate table.

User-Defined Tables
You publish the definition of an aggregate table after you create that definition. After you do this, the next build process for the cube builds the first instance of the new table.

Used
The engine directs queries to the aggregate table, if those queries can be satisfied by it. The engine also continuously evaluates the quality of the aggregate table for such queries.

Rebuilt
Over time, as more data is ingested into the cluster and existing data is updated, the current instance of an aggregate table becomes out of date. Rebuilding the table brings it up to date. Rebuilding is done per cube, so all of the aggregate tables for a cube are rebuilt (or built, if a table is new) in one batch.

Superseded
System-Defined Tables
Demand-Defined Tables
The engine eventually determines, based on criteria such as query history and statistics, that a different system-defined aggregate table could better satisfy queries. The new table could be entirely new or based on the current aggregate table. The engine creates a definition for the new aggregate table and, after the first instance of this table is built, stops using the most recent instance of the old aggregate table.

The engine could determine that removing an aggregate table would help reduce the overhead of managing the aggregate tables for a cube. There are two situations in which the engine might determine this:

• The measures in the aggregate table are a subset of the measures that are in a different aggregate table that also has the same dimension columns. In this situation, there is no need to manage the smaller aggregate table.
• Two aggregate tables contain the same dimension columns, but the each contain different measures. In this situation, the engine might create a new table that is a union of both, replacing the two original tables.

Prediction-Defined Tables
If a project is republished, changes in the project's cubes might cause the engine to generate different prediction-defined aggregates for those cubes. Changes to the measures in a fact dataset could result in a different all-member aggregate being defined for that table. Changes to a dimension could result in a different dimension-only aggregate being defined on the dimension; if no dimension-only aggregate is defined on a dimension that has changed, the engine might define one.

User-Defined Tables
The engine never supersedes user-defined aggregate tables.

Deleted
System-Defined Tables
The engine retains the definition and the instances of the old aggregate table for comparisons and for usage statistics. Definitions are retained indefinitely because they have statistics associated with them that are useful, and there is negligible cost involved in retaining them. Eventually, however, the engine deletes instances according to two sets of criteria:

First set of criteria
Desired Number of Instances: This number sets an upper bound on the set of current and inactive instances for the engine to retain. You can change this number. By default, it's 100.

Maximum Number of Excess Instances: Rather than prune every time it exceeds the desired number by building a new instance, the engine can wait until there are a number of instances that are candidates for deletion. You can also change this number. By default, it's 10.

Second set of criteria
When the maximum number of excess instances is reached, the engine uses two criteria to rank all of the instances: which instances were the least recently used and which instances are the least frequently used.

To put all of this together, suppose that the desired number of instances is set to 100 and the maximum number of excess instances is set to 10. When the total number of instances reaches 110, the engine ranks the instances by the two sets of criteria. It then deletes the instances that are ranked 101 through 110.

User-Defined Tables
If you no longer want instances of a particular user-defined aggregate table to be built, you use the Design Center to delete the definition of that table. After doing so, you republish the project that contains the corresponding cube to remove the definition from the list of aggregate-table definitions for the cube. You can do this even if instances of the definition still exist on the cluster.

If you want to delete instances of a particular user-defined aggregate table, you must delete them manually, not through the Design Center.