Skip to Content
Author's profile photo Ravindra Channe

Exception aggregation modeling with Graphical Calc view

Usual disclaimer:

Please note that the following model has been implemented in SPS06 (revision 60 and onwards). I am not sure about the feasibility of this approach on the earlier revisions. The observations on the below model are based on my personal experience and opinion.

The business scenario is quite common in the BI reporting. Consider a KPI, required to be calculated at a low level of granularity, but the reporting is required at the higher level of granularity. With the default aggregation behavior of the calc view, the constituents of the Calculated KPI might also be aggregated at the reporting granularity level, resulting in incorrect value for the Calculated KPI.

To elaborate more on this, consider the following data model for the Sales Transaction. The transaction data is captured for Product Sold on a given date in a store. The table structure can be defined as follows:

( STORE_ID    nvarchar(10),  — Store Id

  PROD_ID    nvarchar(10),  — Product Id

  PROD_CAT    nvarchar(10),  — Product Category

  PROD_PRICE  decimal(10,2), — Product Price

  SOLD_QTY    integer,      — Product Quantity Sold in a Sales transaction

  TRANS_DATE  nvarchar(8),  — Sales Transaction Date

  STORE_REG  nvarchar(10)  — Region in which the Store is located


The business rule could be:

The product price may vary each day, hence the Net Sales need to be calculated for the Product Price on the transaction date and Quantity Sold on the day.

*** In ideal scenario, the Net Sales should be calculated at the time of data loading and should be persisted in the data model. This can have positive impact on the performance and is also a recommended modeling option. The following modeling option explained below should be implemented, when the calculation persistence is not feasible due to more complex requirements or different sources / data stores for Product price and Sales Transaction.

The sample Sales transaction data is as follows:


In the above example, consider the Net Sales in the Sales transaction data with calculation as Product Price * Quantity Sold. The Net sales need to be calculated at the transaction level granularity.

The standard Calc view model, based on a standard Analytic view using the transaction table is as follows:


Please note that the Multidimensional Reporting property is set to True which adds default aggregation node to the Calc view. The default behavior of all the base KFs is set to SUM. The Calculation of the Net Sales is defined as below hence executed as “Calculate After aggregation” resulting in the following output.


The output of the query is as follows:


The Product Price is shown for the explanation purpose. The Product price at Product category level does not really make any business sense.

The expected correct output for the above query is as follows:


To achieve the above output, we need to make the following changes to the model.

  1. Create the model with the Multidimensional property set to False, hence adding the default Projection node to the model.
  2. Add an aggregation node on the Analytic view to define the aggregation behavior.
  3. Set the Keep Flag property for all the attributes which defines the Exception aggregation criteria. In this case, the aggregation is expected to happen at Store, Product and Transaction date irrespective of the reporting granularity. The Keep flag will always add these columns in the group by clause even if they are not selected in the query. Please note that the added columns in aggregation will impact the performance as each column join is added to the resultset.
  4. Define the Calculated KF for Net Sales in the Aggregation node. The calculation remains same as shown in the earlier screenshot.



The query executed on this model will result in the required output.


Well, now that works as expected.. 🙂

Performance impact:

Please note that this implementation has performance impacts. If we look into the visual execution plans for both the queries, then we can see that the second query includes all the columns in the SELECT statement along with all the columns with Keep flag property set to true.

Execution for the first query on ZGCV_PROD_SALES looks something like:


The execution plan shows only those columns required in the SELECT statement.

Execution for the second query on ZGVC_PROD_SALES_EXCP_AGGR (with Exception aggregation) looks like:


As we can see from the execution plan above, the performance gets impacted due to additional column joins resulting in higher data volume, 20 rows, being passed from one engine to another, as compared to 3 rows in the earlier execution. The generated resultset depends upon the granularity specified with the Keep flag setting.

/wp-content/uploads/2013/11/plan_2_2_327014.jpgAs you can see from the screenshot above, all the columns in the SELECT statement and with Keep flag set to true are used in column joins to generate the result set.

As mentioned earlier, the better option is to have such values persisted in the database. In case, due to complex business or technical requirements such persistence is not feasible, then the above option should work fine. But at the cost of some performance impact.

All the comments, suggestions, discussions are most welcome. 🙂

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      Hey Ravi

      meanwhile I've to say "as usual" you provide content of exceptional quality!

      Really well done. I wish that every content would be so carefully researched and produced!

      Double thumbs-up!

      - Lars

      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Thanks for the kind words Lars. It means a lot to me.. 🙂



      Author's profile photo Nikunj Thakur
      Nikunj Thakur

      Hi Ravindra,

      Now in newer version of SAP HANA, while creating a calculated columns similar to what you described above we have option of doing calculation before aggregation. So we can just select that check box and need to set Keep Flag true for Attribute which we always want engine to consider while presenting a value?

      Is my understanding correct?

      Author's profile photo Former Member
      Former Member

      Hey Ravi, Very Informative article.

      Just wanted to confirm that my understanding is true. All the key figures passed on from the aggregation node upwards will have this exception aggregation behaviour. So if we want to have exception aggregation for only a few key figures in a analytic view than we should include two instances of the analytic view in a calculation view. Use your methoed to bring in the exception aggregation KFs. For regular Key figures we use the other instance of the Anlytic view (with the multi dimensional reporting on).  Does this make sense?

      Author's profile photo Former Member
      Former Member

      Many thanks, Ravi! Very helpful and informative.

      To follow-up on Sidddarth's comment, I would think one might want to consider NET_SALES in the Analytic View with the option "Calculate Before Aggregation". This would lead to correct behavior for this particular measure, without impacting granularity of other measures (and without requiring two base Analytic Views).

      Your thoughts?

      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Hi Jody,

      Sure we can have Calculate before Aggregation setting in Analytic view. But in case you need currency conversion (most likely with Sales KPIs) or you have Calculated columns in the Analytic view, then the Calculate before aggregation setting is ignored.

      You may get the following message when you try to set it in the Analytic view:

      The check box “Calculate before aggregation” has been unchecked, because the definition of the calculated column contains measures with currency conversion, restricted measures or operands with input parameters. For such a calculated column the calculation is always done after the aggregation.



      Author's profile photo Former Member
      Former Member

      I didn't know this. Thanks!

      Author's profile photo Abhay Mhatre
      Abhay Mhatre

      Hi Ravi,

      Very good informative article.

      I am trying to calculate growth over two period which needs to use Calculate before Aggregation. However the measure (Growth) requires currency conversation, so in this case, can HANA (Calculation View) handle the Calculate before Aggregation? What configuration do I need to set?


      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Hi Siddharth,

      The Keep flag setting is applicable for all the KFs of the aggregation node. So in case you have different KFs with different level of aggregation, then you may have to add multiple instances. I haven't tried this yet myself.

      Frankly in such scenarios, I'd recommend to persist the calculated data. The performance impact might be quite much to model such complex scenario.



      Author's profile photo Vivek Singh Bhoj
      Vivek Singh Bhoj

      Another great document Ravi

      Very well explained 🙂

      I am on HANA Rev 66 but using HANA Studio Ver 60 and this "Keep Flag" property is not available there

      I guess I need to upgrade my HANA Studio



      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Hi Vivek,

      Thanks for the comment. Actually I tried this on revision 66 only 🙂 .

      Among all the people, you'd be the last one whom I'd say "Please ensure that the HANA server and client are of the same revision". 🙂



      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Another informative blog Ravindra, this is one of the important scenario.



      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Thanks Kumar. 🙂



      Author's profile photo Rama Shankar
      Rama Shankar

      Great Blog Ravi - Thanks! 🙂

      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Thanks Rama.. you are always the one who appreciates 🙂



      Author's profile photo SIVA P
      SIVA P

      Good one..

      Author's profile photo Former Member
      Former Member

      Exceptionally well Ravi... Always love to read ur blogs ....Keep doing it 🙂



      Author's profile photo Raj Kumar S
      Raj Kumar S

      Once again excellent content from you Ravi.

      Author's profile photo Marcel Scherbinek
      Marcel Scherbinek

      Hey Ravi,

      thanks for sharing this golden piece of information. I tried exception aggregation modeling as well but not as SQL but as calculation view to compare it with the exception aggregation in BEx directly 😉 Performance of the exception aggregation in-memory is awesome but struggles with structures in BEx... 🙁 I just try to sum up a conclusion and to prepare these information for sharing.



      Author's profile photo Shashidhar Garimella
      Shashidhar Garimella

      Hey Ravi,

      This is a much awaited solution for someone who tries to compare BW Features with HANA. I really appreciate your interest on posting on this subject.

      I would like to understand something very basic about the solution you propose. You're essentially trying to setup "Calculate Before Aggregation" here. Why not have the Projection node(with Calculated Column inside) at the bottom and Aggregated Node above it? Isnt it the right way to configure "Calculate Before Aggregation" scenario ? I see you have Aggregation Node at the bottom and Projection Node on top.

      I'm obviously missing something here.

      PS: I've been a silent observer of your blogs and just love reading them. 🙂

      Thanks & Regards,


      Author's profile photo Muthuram Shanmugavel
      Muthuram Shanmugavel

      Hi Shashidhar,

      I tried the same (Enable Keep Flag) in "Default Aggregation Node". It is not working.

      It works in Aggregation nodes, which we create newly.

      This might be reason behind

      Aggregation --> Projection --> Semantics below.

      But I am not sure about it.

      Author's profile photo nilima rodrigues
      nilima rodrigues

      Hi Shashidhar,


      I am curious to know the answer to your question. Because even I tried the same scenario i.e. In calculation view, my lowest node is "Projection" and I have put the calculated column in "Projection Node". the aggregation node is defined on top of "Projection" node and it still gives me the correct output. So why do I need the "Keep Flag" property?




      Author's profile photo Former Member
      Former Member

      Dear Ravi,

      I had to do model multiple measures for which the calculation should happen at a particular level of aggregation. As each measure has to be calculated at a differing aggregation level, creating one aggregation node for each combination became cumbersome. I forced HANA to do the aggregation at required level using the formula itself. See the code below.




        "PROD_PRICE" * "SOLD_QTY"))));

      The formula has a dummy check ISNULL to force the calculation view to get the data at this level from the underlying analytical view. Furthermore, when you do not request this measure in the reporting tool (like Analysis office) and only use other measures, the calculation view then does not force analytic view to get the data at this detail level. It automatically requests data at the higher aggregate level.

      Please let me know of your thoughts.


      Author's profile photo Upamanyu Mukherjee
      Upamanyu Mukherjee

      Hi Ravi,

      Same doubt as Shadhidhar's...

      Seems that you are basically trying to achieve "Calculate Before Aggregation" ...

      Can you please help me out with what I am missing here...



      Author's profile photo Former Member
      Former Member

      This is a much needed article...Thanks for posting.

      Author's profile photo Former Member
      Former Member

      Hi Ravi,

      I am using HANA studio 2.0.11

      I tried to achieve exception aggregation by putting Keep Flag as true for an attribute. But don't see any effect on the results. I am still getting results as calculated after aggregation. What may be the issue here?



      Author's profile photo Rama Shankar
      Rama Shankar

      Nice refresher blog Ravi. Thanks