on 06-04-2013 2:41 AM
Hi all,
I have an issue where exception aggregation (MAX) on a Calculated Key Figure (CKF) is not delegated down to HANA, but instead still executed in the OLAP engine (a.k.a. Analytic Engine) in BW.
We want to push the execution of exception aggregation down to HANA to leverage the power of HANA and perform less calculations in BW.
This document says that Maximum Exception aggregation is supported in HANA:
http://help.sap.com/saphelp_nw73ehp1/helpdata/en/f7/8e93e8850b244085f2c4a39a7d73d5/frameset.htm
http://help.sap.com/saphelp_nw73/helpdata/en/4b/bda228a8b43c22e10000000a42189b/frameset.htm
To simplify the testing and analysing I have created a simple BEX query with:
- Two RKF (restricted key figures) RKF1 and RKF2 and one CKF1 with the calculation of RKF1 - RKF2
- CKF1 has Max Exception Aggregation with reference characteristic HREADDATE
In RSRT, the properties of the query are set to run with setting "Ops in BWA / SAP HANA = 6 - Exception Aggregation"
However, when I run "Execute and Explain" in RSRT2 for "Exception Aggregation in SAP HANA / BWA" the explanation shows:
However, all CKF are created with the option "Calculation After Aggregation" selected.
Also, when I analyse the Query Statistics in RSTT, we can see that the number of rows returned from the Db to the OLAP engine tells us that the exception aggregation is performed in the OLAP engine and NOT in SAP HANA.
The number of records returned from Db to OLAP is 16 and then once exception aggregaiton is applied in the OLAP engine, only 1 row is returned to the client. If the exception aggregation would have been performed in HANA, the number of rows returned to OLAP would have been 1.
My questions are:
Your ideas are most welcome!
Thanks,
Stefan Beekman
Hi everyone,
Thanks for you replies.
I figured out why the exception aggregation is not pushed down to HANA.
This is because any calculation will happen before aggregation in case of a formula or a calculated key figure and it will therefore not be pushed down.
So in my case where CKF1 = RKF1 - RKF2, I will need to store this result in the database as a key figure. Then I can use this key figure in the query and as a result the exception aggregation will be pushed down to HANA.
Regards,
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stefan,
were you able to reduce the DBTRANS value to the OLAP processor, so that only the aggregated value is transferred? I am confronted with the same issue, we need to reduce the amount of records transferred to the OLAP processor.
I have changed the query Settings to '6'
How exactly did you define/change the exception aggregation on your newly added basis key figure?
Thanks a lot for your reply,
Christoph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Christoph,
In order to confirm that Exception Aggregation is pushed down to HANA, I used the "Execute and Explain" option in RSRT. When you use this option you can use it to test whether Exception Aggregation is executed in HANA (last checkbox).
I have also tested the different HANA ops options (0, 3 and 6) and I must admit that the numbers I saw for DBTRANS event, were not the numbers I was expecting...
However, I haven't proceeded with this analysis.
I just used the "Execute and explain" to assure that it was pushed down to HANA.
Regards,
Stefan
Hi Stefan,
I'm curious.
Did you try to apply SP09 (or even SP10) to see if you'd have this pushed down?
Thanks,
Henrique.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stefan,
as far as i recall there is an OSS Note in SP9 regarding Exception aggregation for COUNT.
Please have a look here:
https://service.sap.com/sap/support/notes/1750249
regards, Johannes
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.