cancel
Showing results for 
Search instead for 
Did you mean: 

Avoiding Exception Aggregation in Query Formulas

TimoScharmann
Participant
0 Kudos

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!

Accepted Solutions (0)

Answers (4)

Answers (4)

TimoScharmann
Participant
0 Kudos

Hi Lee,

Unfortunately we couldn't find a solution regarding this topic. Let me know, when you've find a way.

Best regards!

leewlewis
Participant
0 Kudos

Hello Timo,

I see this post is a year old, but I face exact same. Hoping you might provide update to your experience with this. I would offer more details about our case, but it would be no different or better than the info your provided here.

Regards

TimoScharmann
Participant
0 Kudos

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

giselle_lin
Active Participant
0 Kudos

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?