Balancing Precalculated and Runtime Aggregation

Using AGGREGATE, all of the following strategies are possible. You can:

• Pre-aggregate all of the data. This means that all of the data for the variable will be aggregated and stored in the database. This is likely to result in relatively slow build performance and extremely fast user query performance.
• Calculate all of the data on the fly, that is, at run-time. In this case, you eliminate aggregation from the build process, which means that build performance will be very fast; it will be reduced to the time that it takes to load data into the workspace. However, user query performance will suffer greatly.
• Pre-aggregate some of the data and calculate the remainder on the fly.

Good performance is a matter of trade-offs. Therefore, one of the most effective steps you can take to achieve overall good performance is to balance the amount of the data that you aggregate and store in an analytic workspace with the amount of data that you specify for calculation on the fly.

A typical strategy is skip-level aggregation: that is, select one or two of a variable's dimensions and pre-aggregate every other level in those dimension's hierarchies. If you know which levels are queried most often by users, you should pre-calculate those levels of data.

Example 12-14 Calculating Data Using the Skip-Level Approach

Suppose you want to aggregate sales data. The sales variable is dimensioned by geography, product, channel, and time.

First, consider the hierarchy for each dimension. How many levels does each hierarchy have? What levels of data do users typically query? If you are designing a new workspace, what levels of data do your users plan to query?

Suppose you learn the following information about how users tend to query sales data for the time hierarchy:

Time Level Names	Descriptive Level Name	Examples of Dimension Values	Do users query this level often?
L1	Year	YEAR99, YEAR00	yes
L2	Quarter	Q3.99, Q3.99, Q1.00	yes
L3	Month	JAN99, DEC00	yes

The following information shows how your users tend to query sales data for the geography hierarchy:

Geography Level Names	Descriptive Level Name	Examples of Dimension Values	Do users query this level often?
L1	World	WORLD	yes
L2	Continent	EUROPE, AMERICAS	no
L3	Country	HUNGARY, SPAIN	yes
L4	City	BUDAPEST, MADRID	yes

The following information shows how your users tend to query sales data for the product dimension hierarchy:

Product Level Names	Descriptive Level Name	Examples of Dimension Values	Do users query this level often?
L1	All Products	TOTALPROD	yes
L2	Division	AUDIODIV, VIDEODIV	yes
L3	Category	TV, VCR	yes
L4	Product	TUNER, CDPLAYER	yes

Using this information about how users query data, you should use the following strategy for aggregation:

• Fully aggregate time and product because all levels are queried frequently.
• For the geography dimension, aggregate data for L1 (World) and L3 (Country) because they are queried frequently. However, L2 is queried less often and so can be calculated on the fly.

The lowest level of data was loaded into the analytic workspace. The aggregate data is calculated from this source data.

Therefore, the contents of the aggregation map might look like the following:

RELATION time.parentrel
RELATION geography.parentrel PRECOMPUTE (geog.leveldim 'L3' 'L1')
RELATION product.parentrel

Selecting Dimensions for Runtime Calculation

Use a skip-level approach for only one or two dimensions. You should use the skip-level approach for half or fewer of the dimensions in a variable definition. For example, if there are three dimensions, then you can use the skip-level approach for one dimension; if there are four or more dimensions, then you can use the skip-level approach for two dimensions.

The dimensions that are the best candidates for skip-level aggregation are the dimensions whose hierarchies have many levels.

If possible, choose a dimension that is either fastest- or intermediate-varying in the variable dimension. Performance of calculation on the fly will always be best for dimensions in this position.

Selecting Levels for Runtime Calculation

Skip every other level in a dimension hierarchy, and avoid skipping more than two levels that are adjacent to each other. For example, if a hierarchy has seven levels, you might skip L2, L4, and L6. That means you would precalculate L1, L3, and L5. (The detail-level data is at L7.) Take into consideration how frequently a level is queried, as demonstrated in Example 12-14. Users will experience the best performance if you pre-aggregate the data most frequently queried, and aggregate on the fly the data that is requested occasionally.

Do not skip adjacent levels. For example, if you skipped L2, L3, L4, and L5, then a query for L2 data would require AGGREGATE to calculate L5, then aggregate that data up to L4, then up to L3, and finally to L2. Alternatively, if you skip L2, L4, and L6, then a query for L2 data requires AGGREGATE to aggregate data only from L3.

The one exception to this rule is when each level has very few children per parent. When this is true for every adjacent level that you want to skip, then you can skip two or more adjacent levels.