Calculation Before Aggregation
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 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 notes 460255 and 152638) Calculated Key Figures:
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 pre-aggregated 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
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 issued (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 should not be confused with the similar named setting in the Query Designer.
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 Aggregation', this function does not switch off any data base aggregation and hence does not increase the data volume transferred from the data base to the OLAP engine.
SAP Online Documentation
help.sap.com: OLAP Engine: Aggregation
SAP Consulting Notes:
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 performance. 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