Measures Property Sheet: Summarize To tab

For each dimension, select the levels that you want to precalculate. The base level should always be selected.

For measures in compressed cubes, the aggregation subsystem determines the optimal calculation strategy. Sparse dimensions are dimmed, and you can select levels only for dense dimensions, if any exist.

Aggregate data can be calculated at two distinct times:

At run-time when needed. The cells for the aggregate values are NA (that is, they are empty) until a query requests the aggregate values. The aggregates are then computed in response to the query. This type of aggregation is referred to as on-the-fly or run-time aggregation. Run-time aggregation slows querying time since the data must be calculated instead of just retrieved, but it does not require storage for aggregate values in a permanent tablespace.
As a data maintenance procedure. Aggregate values are calculated and stored in the analytic workspace. This type of aggregate data is sometimes call presummarized, precomputed, or stored aggregates. Presummarized aggregates support the fastest querying time, but increase the size of the analytic workspace and therefore the size of the relational database. The amount of presummarized data may also be limited by the amount of time available for data maintenance, which is typically limited to a batch window.

If your dimensions have multiple hierarchies or if the hierarchies have many levels, then fully aggregating the measures can increase the size of your analytic workspace (and thus your database) geometrically. At the same time, much of the intermediate level data may be accessed infrequently or not at all.

A typical strategy is to combine these methods by presummarizing some of the data as a data maintenance procedure, and the rest of the data on demand. The data cube is presented to the application fully solved, with no detectable difference between the values that were retrieved from storage and the values that were calculated for the query.

Dimension

Select each dimension to view its levels.

Levels

Select the levels you wish to calculate and store as part of the build process.

The best method for identifying levels for stored data is to determine the ratio of dimension members at each level, and to keep the ratio of members to be calculated on the fly at less than 10:1. This method assures that all answer sets can be returned quickly. Either the data is stored in the analytic workspace, or it can be calculated by rolling up 10 or fewer values into a single number. The time needed to roll up 10 values is trivial, and a well designed application will limit return sets to an amount of data that an analyst can scrutinize easily. You can modify this ratio using your judgment on how frequently a level is accessed.

Slower varying dimensions take longer to aggregate because the data is scattered throughout its storage space. If you are optimizing for data maintenance, then fully aggregate the faster varying dimensions and use skip-level aggregation on the slower varying dimensions.