In order to understand query results it is very important to know the order of calculations and Aggregations carried out by the Analytic Engine. It always starts with the standard aggregation at the defined level of detail and then executes the exception aggregation. The following list gives an overview of the order of the most important steps. Please see also the SAP Online documentation Aggregation Steps and the SAP note 1151957 for further details.
- the OLAP Engine gets data from the data base
- standard aggregation is carried out for the infoobjects in the drilldown
- if Exception Aggregation is used, the aggregation regarding the Reference Characteristic (invisibly drilled down) is done
- formulas/CKF(Calculated Key Figures) are processed
- if Exception Aggregation for formulas is used, the aggregation regarding the Reference Characteristic is carried out after the calculations
- 'List Calculations' like e.g. 'Calculate Result as...' are carried out
Exception Aggregation can be defined for a basic key figure (transaction RSD1) and for calculated key figures (CKF) and formulas in the Query Designer. If there is no formula defined (e.g. a CKF with only a basic key figure), the Exception Aggregation is carried out before formulas are calculated (see steps above). Please be aware of the following points:
- The Reference Characteristic is automatically added to the 'Drilldown Characteristics' (not visible to the users)
- The Exception Aggregation is always carried out last - after all other necessary aggregations !
Hence, if the reference characteristic isn't part of the query drilldown, the system adds it to the drilldown and carries out the exception aggregation in the background(invisible to the user). This additional drilldown may lead to a high data volume transferred from the Datamanger to the OLAP Engine which in turn could have an negative impact on the query performance.
If BW is based on HANA, the calculation of the exception aggregation can be 'pushed down' to the HANA database, for details see Exception Aggregation in Hana Database.
Nested Exception Aggregation
'Nested Exception Aggregation' means that a basis key figure (formula which only contains a basic key figure) uses different exception aggregations regarding more than one infoobject. This is only possible if a formula with exception aggregation is used in another formula with exception aggregation. Please see the following example:
Basis Key Figure with Exception Aggregation (defined in RSD1)
Formula Exception Aggregation (FAGGR)
If there is a formula involved the Exception Aggregation is done after the calculation of the formula (see steps above).
Nested Formula Exception Aggregation
'Nested Formula Exception Aggregation' means that a formula uses different exception aggregations regarding more than one infoobject. This is only possible if a formula with exception aggregation is used in another formula with exception aggregation. Please see the following example:
Multidimensional Exception Aggregation
As of BW74, the QueryDesigner offers the possibilty to create an exception aggregation regarding more than one characteristic. This simplifies the modeling (in cases where a certain exception aggregation like e.g. LAST should be done regarding more characteristics) since you do not need to create such nested key figures as explained above. But, if you need different exception aggregations regarding different characteristics, you still have to proceed as explained under 'Nested Exception Aggregation ' and 'Nested FAGGR'. In addition you need to be aware of the following point: the aggregation over the reference characteristics is done 'in one go'. Hence, in case the aggregation rule is an interchangeable one (SUM, MIN, MAX, NOP), this procedure improves the performance of the query. But in case the aggregation rule is NOT an interchangeable one (e.g. Average), the calculated numbers are different in comparison to the usage of nested exception aggregation. Please see the following example.
(Formula) Exception Aggregation in SAP HANA Database
If the database is HANA, exception aggregation can be 'pushed down' to the HANA database with the query setting 'Operations in HANA' (TREXOPS) = 6. See the following Wiki page in HANA for more information: Exception Aggregation in SAP HANA Database. As of BW75 (SP4), exception aggregations on formulas are supported to be calculated in the SAP HANA DB in case the query setting 'Operations in HANA' = 7, see Formulas calculated in Hana.
FAGGR pushdown in combination with feature Constant Selection (CS)
FAGGR pushdown of a formula in combination with constant selection feature (CS) in the query is possible only when either:
- The formula itself only contains operands that do not contain CS.
- The formula itself only contains operands that all have the very same CS defined.
- The formula itself contains operands having different CS defined, but each such operand containing CS is MULTIPLIED with an operand that has no CS defined.
SAP Online Documentation
SAP Consulting Notes
- 1151957 - Interesting facts about the OLAP Processor/Analytic Engine
- 1812637 - Formulas with Exception Aggregation and Constants
- 1861667 - Exception Aggregation' versus 'Local Calculations'
- 1858456 - How does NHA and NGA exception aggregation work?
- 310791 - Key figures with exception aggregation LAST
- 1863175 - Formula Variables
There is an excellent possibility to get familiar with many important OLAP features by analyzing corresponding test queries. SAP delivers special demo content for BI 70x which you can activate in transaction RSFC (see also note 1133936). There you can find queries to various OLAP features as Exception Aggregation which can immediately be executed in RSRT. For all these queries documentation is available.