Skip to Content

Avoiding Exception Aggregation in Query Formulas

Dear experts,

I am facing a serious performance problem with exception aggregation in formulas. The query contains such a big number of key figures that have to be calculated as weighted average, that the query execution failes after a long time of execution. Now I am asking myself how to avoid the exception aggregation and to use alternative approaches.

Here comes a basic example. There are several countries with different quantities and values.

Country Qty Value
------- --- -----
DE 1 5
FR 2 20

To get the weighted value for the whole world depending on quantity we need to sum each country product of quantity and value (1*5 + 2*20 = 48). The result will be divided by the sum of quantities (45/3). So we get 15. Generally this works fine using exception aggregation SUM on Country.

Country Qty Value Qty*Value WeightedValue
------- --- ----- --------- -------------
DE 1 5 5
FR 2 20 40
Sum 3 45 15

Searching for alternatives I'd like to define something like a view, that is firstly doing the multiplication between quantity and value on country level. Then this view should be the data basis (like persistent ADSO or InfoCube) for a query. In that query I could avoid the exception aggregation as all products (Qty*Value) are already available and default aggregation will always devliver valid sums for the weighted value calculation independent from drill down level. But how can such a view be easily realized? What do you think? I am thinking about external HANA views for queries, so that a second query can consume this basis query. What about calculation views in the HANA layer? Are there further possibilities?

In the meantime I also thought about persisting the result of Qty*Value in the database. But as there are many different value key figures and additional several quantitiy key figures on that the weighted average calculation has to be processed, this is no way.

Do you have further ideas to avoid exception aggregations?

Best regards!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Dec 21, 2018 at 12:33 AM

    Hi Timo,

    The difficulty about persisting the country level 'Qty*Value' result is: if there are new record coming for this country, this field needs to be refreshed to get the correct result. If such data changes are frequent, the formula has to be calculated on the fly.

    So push down the query exception aggregation to HANA still takes long time?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 04 at 02:20 PM

    Hi Giselle,

    Some parts of the query will be processed on HANA layer, but not all of them. The query contains the following items:

    • Qty (basic KF)
    • Value (basic KF)
    • Product (Formula: "Qty * Value" | Exception Aggregation SUM on Country)
    • Ø Value (Formula: "Product / Qty")

    Running and explaining HANA Exception Aggregation with RSRT2 returns the result at the end of this post. Most of the elements will not be processed in HANA. I do not really understand why the push down of elements like KID 3 seems to be impossible. Looking into the process list during query processing, I see that OLAP formula exception aggregation is heavily working (CL_RSR_RRK0_FAGGR_PROCESSOR). Is it possible to push this also down to the HANA on a system with SAPK-75012INSAPBW?

    Best regards!
    ----------------------------------------------------------
    Log for Exception Aggregation in SAP HANA/BWA

    Operations in BWA/HANA = 7 Formulas calculated in SAP HANA
    Query contains formula exception aggregations for SAP HANA execution

    Structure element combinations (KIDs) whose formula exception aggregation take place in SAP HANA
    KID PURE_HANA ABAP_AGGR_NEEDED
    3 (Product) X
    4 (Ø Value)

    Structure element combinations (KIDs) whose exception aggregation take place in SAP HANA/BWA

    Structure element combinations (KIDs) whose exception aggregation cannot take place in SAP HANA/BWA
    KID WGR AGGREXC AGGREXC_REF_CHAR KYFNM CONSTANT LOOKUP REASON
    1 (Qty) 1 QTY No exception aggregation defined
    2 (Value) 2 VAL No exception aggregation defined
    3 (Product) 4 VAL Formula Exception Aggregation
    3 (Product) 3 QTY Formula Exception Aggregation
    4 (Ø Value) 4 VAL Formula Exception Aggregation
    4 (Ø Value) 1 QTY No exception aggregation defined
    4 (Ø Value) 3 QTY Formula Exception Aggregation

    Add comment
    10|10000 characters needed characters exceeded