Skip Headers
Previous
Previous
 
Next
Next

When Does Aggregation Order Matter?

The OLAP engine aggregates a cube across one dimension at a time. When the aggregation operators are the same for all dimensions, the order in which they are aggregated may or may not make a difference in the calculated aggregate values, depending on the operator.

You should specify the order of aggregation when a cube uses multiple aggregation methods. The only exceptions are that you can combine Sum and Weighted Sum, or Average and Weighted Average, when the weight measure is only aggregated over the same dimension. For example, a weight measure used to calculate weighted averages across Customer is itself only aggregated across Customer.

The weight operators are incompressible for the specified dimension and all preceding dimensions. For a compressed cube, you should list the weighted operators as early as possible to minimize the number of outer joins. For example, suppose that a cube uses Weighted Sum across Customer, and Sum across all other dimensions. Performance is best if Customer is aggregated first.

Using the Same Operator for All Dimensions of a Cube

The following information provides guidelines for when you must specify the order of the dimensions as part of defining the aggregation rules for a cube.

Order Has No Effect

When these operators are used for all dimensions of a cube, the order does not affect the results:

  • Maximum

  • Minimum

  • Sum

  • Hierarchical First Member

  • Hierarchical Last Member

  • Hierarchical Average

Order Changes the Aggregation Results

Even when these operators are used for all dimensions of a cube, the order can affect the results:

  • Average

  • First Non-NA Data Value

  • Last Non-NA Data Value

  • Weighted First

  • Weighted Last

  • Hierarchical Weighted First

  • Hierarchical Weighted Last

  • Scaled Sum

Order May Be Important

When the following weighted operators are used for all dimensions of a cube, the order affects the results only if the weight measure is aggregated over multiple dimensions:

  • Weighted Average

  • Weighted Sum

  • Hierarchical Weighted Average

Example: Mixing Aggregation Operators

Even though you can use the Sum and Maximum operators alone without ordering the dimensions, you cannot use them together without specifying the order. The following figures show how they calculate different results depending on the order of aggregation. Figure: Sum Method Followed by Maximum Method shows a cube with two dimensions. Sum is calculated first across one dimension of the cube, then Maximum is calculated down the other dimension.

Sum Method Followed by Maximum Method

Description of this figure follows
Description of "Sum Method Followed by Maximum Method"

Figure: Max Method Followed by Sum Method shows the same cube, except Maximum is calculated first down one dimension of the cube, then Sum is calculated across the other dimension. The maximum value of the sums in Figure: Sum Method Followed by Maximum Method is 15, while the sum of the maximum values in Figure: Max Method Followed by Sum Method is 19.

Max Method Followed by Sum Method

Description of this figure follows
Description of "Max Method Followed by Sum Method"