Aggregation Steps

The order in which the calculations are performed in Analytic Manager is decisive. The OLAP processor therefore always begins with the standard aggregation up to the defined level of detail before performing the exception aggregation. The following graphic gibes an overview of the order of the aggregation authorizations in the BW system.

There are two control programs (agents) in the aggregation process. One is responsible for the aggregation of the key figures and the other for the aggregation and handling of currencies and units. The system proceeds as follows when processing aggregation calculations:

1. Firstly, the standard aggregation (that is, the aggregation behavior of a basis key figure) is executed. Possible aggregation types include summation (SUM), minimum (MIN) and maximum (MAX). Currencies and units are not aggregated here.

2. Secondly, the conversion of currencies and units of the data entries to the target currency or unit by currency and unit agents takes place provided that this is defined in the query definition (see Currency Conversion).

3. After the conversion, the third step aggregation using currencies or units take place, which belong to the standard aggregation. Here there is another standard aggregation of the key figures with the special handling of currencies or units.

   Note

   All of the following aggregations (and formula calculations) execute this special handling of the currencies or units. If no unique currency or unit can be determined for the result of the aggregation, the system returns a '*' instead of the currency or unit (see Currency Aggregation).

4. The fourth step sees the aggregation of restricted or calculated key figures using one or more selected characteristics (Exception Aggregation). The available exception aggregation types include summation (default), minimum, maximum, average, first value, last value, no aggregation, counter, standard deviation, and variance.

   Note

   You can also have the exception aggregation calculated on SAP HANA database or on SAP NetWeaver Business Warehouse Accelerator indexed data in BWA (see Query Characteristics, section Operations in SAP HANA/BWA).

   As a further aggregation step is executed with the exception aggregation, the currencies and units must be aggregated again. However, this is not a separate step as with the standard aggregation, but is executed as part of the exception aggregation. The aggregation agent communicates during the aggregation with the currency and unit agents.

5. After the numbers have been aggregated completely, the Formula Calculation occurs.

   There are the following exceptions to this rule:

   • For the key figure, a calculation time was selected in which the calculation of the formula is to be done before the aggregation.

   • A formula variable is used with replacement from an attribute value in a calculated key figure.

   Note

   See Selection/Formula/Cell Properties, Tab Page Aggregation

   As part of the formula calculation (just as with the exception aggregation), the currency and unit agent calculates the currencies or units of the operands.

6. Finally, the Formula Exception Aggregation is executed, that is, the exception aggregation on the result of the formula calculation. The currency and unit agent aggregates the currencies or units as part of the formula exception aggregation.

Note that also in cases where the reference characteristic is not explicitly included in the query, an aggregation is still executed after taking the reference characteristic into consideration. In other words, the standard aggregation does aggregate using the reference characteristics of the exception aggregation that is to be executed later.

The system reads the values at the level of the reference characteristic from the underlying InfoProvider of the query. Due to this increasing level of details, particularly if you use reference characteristics with a large number of different characteristic values (instances), the system can deliver large amounts of data to the OLAP processor for further processing. Even if the data volume on the frontend were to be very small, the data volume that is read for it can be very large.

The following example illustrates how the sequence in which the values are calculated affects the result of the query.

The example is based on the following data:

The following table shows a query for this example data with four aggregated key figures:

1. Net Revenue: This is a basis key figure with standard aggregation SUM so that the sum is displayed in the sum row. With reference to the example data, the sum of all net revenue is without consideration of any characteristic value.

2. Number of Customers: This is a key figure with exception aggregation CNT. To display the right result in the sum row, the system must include the values of the customer characteristic. Since the customers "Peter" and "Andi" exist in both countries "US" and "DE", the customer column must be evaluated in order to determine the number of customers correctly.

3. Average Revenue for Each Customer (AVG): This is a calculated key figure. The formula is defined as a division of the net sales by the number of customers. It is important for this that the formula of the correct subtotal is calculated. The formula must be calculated after the aggregation at the level of the relevant country. The other totals of the input are in the total row.

4. Number of Customers in January: This is a restricted key figure. It deals with the number of customers, restricted to the month of January. The data for this must first be filtered to the month of January before aggregation and then, as with the key figure for the number of customers, evaluated including the value of the Customer characteristic.