Skip to end of metadata
Go to start of metadata

Calculation Before Aggregation

Introduction

In general the OLAP Engine calculates formulas only after the standard aggregation, the currency translation and the exception aggregation have been executed. Since BW7.x, the feature "Formula Exception Aggregation" provides the possibilty to aggregate over certain infoobjects after the calculation of formulas (see aslo SAP note 1151957).

Before the BW Release BW7x, such a 'calculation before aggregation' was only possible with restrictions (and, in general, it wasn't defined consistently). In the BW 3x Querydesigner there is a setting called 'Time of calculation' which is available for 'simple' (see SAP notes 460255 and 152638) Calculated Key Figuers:

If indicator 'Time of calculation' is set to 'Before Aggregation" the formula is calculated before all other aggregations are carried out. These formulas may lead to inconsistent query results if the data of the underlying InfoProvider are not completely preaggregated for the characteristics combination. This is e.g. in general the case for InfoCubes that are not completely compressed. In addition this setting often leads to performance problems.

BW 73 Releases

In the new BW7x QueryDesigner, this indicator still exists(for Calculated Key Figures, but not for Formulas) and is called 'Calculation after Aggregation':

Because of the mentioned problems we recommend to use the new feature "Formula Exception Aggregation" (FAGGR) instead of deactivating the flag 'Calculation after Aggregation' (there is also the warning BRAIN 447).

  • If the calculation operation commutates with the aggregation, the flag "calculation after aggregation" should be active. In this case the calculated values do not depend on the setting 'Calculation after Aggregation'! Examples of calculations that commutate with aggregation:
    • keyfigure_1 + key_figure_2   with aggregation SUM
    • keyfigure_1 - key_figure_2   with aggregation SUM
    • keyfigure * const              with aggregation SUM
    • keyfigure / const              with aggregation SUM
  • If the operation has to be calculated on a certain granularity, use formula exception aggregation (FAGGR) to specify the granularity on which the formula is calculated. This completely avoids the problems described above, please see the following simple Example:

For technical reasons (in order to avoid inconsistent query results) the OLAP processor cannot calculate formulas before the aggregation in some cases even though they have been set accordingly. If a query with such a formula is generated the error messages BRAIN 031,545 or 546 is isssued (and the query cannot be executed). Please review note 1738522 for further details and change the query definition. Hence, it might happen that a query which was defined with the BW3x frontend runs into this problem when executed on a BW7x system.

BW 74 Release

As of the release BW74 this feature isn't supported any longer! Affected queries display the error message BRAIN_DEV 649. Please review note 1911469 and use the report SAP_QUERY_CHECKER_740 to search for queries containing problematic key figures. See also note 2205107 and the following example.

Query Property 'Calculate Commutative Formulas After Aggregation'

This setting in RSRT shoudn't be confused with the similar named setting in the Querydesigner.
As described in 1817631, commutative formulas (that can be calculated before or after aggregation with the same result)can be calculated before aggregation without risking inconsistent query results. In some cases this can improve the performance. In RSRT there is a new setting which you can use to switch on/off the usage of this feature for a certain query. Please note that, in contrast to the QueryDesigner setting 'Calculation after Aggrgation', this function does not switch off any data base aggregation and hence does not increase the data volume transfered from the data base to the OLAP engine.

SAP Online Documentation

help.sap.com: OLAP Engine: Aggregation

SAP Consulting Notes:

Top Notes
  • 1817631 Calculation of commutative formulas after aggregation
  • 1911469  Check report for discontinued query features in 7.40
  • 1738522 Behavior of obsolete feature formulas before aggregation
  • 1882656 Calculation Before Aggregation
  • 1464974 Property 'Calculation Before Aggregation'
  • 379832 Calculating w/ variables w/ subst. from attrib. w/o char.
  • 1151957 Interesting facts about the OLAP Engine
  • 1882656 Calculation Before Aggregation
 

Remarks to the term 'Calculation before Aggregation' CBA

basically this term is used in three different contexts, this is also a reason why it leads often to misunderstandings. Please don't confuse them:

  • The old obsolete feature CBA switches off data base aggregation and hence shouldn't be used any longer - see above
  • The 'automatic' CBA is used for commutative formulas in order to improve the query peformance. In this case the data base aggregation IS NOT switched off. There is a query property with which you can control the usage of this feature in a query - see above.
  • CBA can be used for a formula to carry out the calculation before the aggregation regarding ONE (or more: nested FAGGR) CERTAIN infoobject - see FAGGR