Skip to Content
Technical Articles
Author's profile photo PALLAB HALDAR

Exception Aggregation in SAP SAC,BW/BI and HANA : A Practical approach

Today  am going to discuss about a very useful topic about exception aggregation in term of concepts and usage scenario in SAP Analytics Cloud, BW/BI and HANA.

In all the Analytics reports or dashboards Key figures are shown in the aggregated level. But the main question is how the aggregation done and shown in the report ?

In Standard Aggregation applied to a Calculated key figure, Key figure aggregation done by group by all the  dimensions in a single row for a single select.



Standard Aggregation : 

For the above table we created a calculated Quantity/key Figure/Measure  “Eligible Quantity ” = No of  “Available Quantity”>1 and  if  Standard Aggregation applied to it

and select one dimension “Sales Organization” in a single row of Select Query/Model  by default the  grouping took place only by “Sales Organization”.






Exception Aggregation : 

In some business requirement, Quantity/Key Figure/Measure aggregation required based on dimension which is not used to select in query or display but required in group by while aggregation.  In this scenario we will use Exception aggregation on a calculated column. T

This way we will get the accurate figure on this scenario.

In this case suppose the table granularity(Key(“Sales organization”+ “Division”)) for Measure available Quantity based on per “Sales organization” and “Division”.

But in dashboard business want to the No of  “Available Quantity”>1 for a sales organization.

In this scenario we need to  Exception Aggregation on calculated column “Eligible Quantity ” with reference characteristics “Division” So that grouping while aggregating will based on the below –



Result :




A. In SAP Analytics Cloud :

In SAC you can create exception aggregation in either in the model or in the Story. It is better to add  exception aggregation in model as you can reuse it in different Story.

1. In Model implement the Exception Aggregation –  Go inside the model and click on Account. You will go to the key figure . Select the Key figure and define the Exception Aggregation Type –



2. In Story –


B. In BW Query :

Create a CKF (Calculated Key Figure) CKF_EXPECTED_QUANTITY  with calculation  AVAILABLE_QUANTITY>1  like below –





C. In Native HANA :

  1. Create a calculated Column(Measure) ZEXPTITY .
  2. Go to Aggregation TAB and select SUM as type of exception aggregation aggregation.
  3. Select Division as reference dimension.


Hope it will help to understand Exception Aggregation.

Assigned Tags

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

      In the provided example, it's not clear in what way exception aggregation is special when compared to relational aggregation.

      Suppose we have a aggregated relational view, "cube_view", with 4 columns for the grouping dimensions and one column for the available_quantity measure.

      Then, for the provided example, the following relational aggregation query gives the same result as exception aggregation:

      select count(*), sales_org

      from cube_view

      where available_quantity > 1

      group by sales_org


      Note: when cube_view is unfolded for query processing, we have 2 levels of grouping and the "where" clause becomes de facto a "having" clause on the aggregate in the view lower grouping.

      It would be good to highlight with more clarity the specifics of exception aggregation compared to relational aggregation, with this example it looks like the GUI equivalent of syntactic sugar.

      Author's profile photo Lars Breddemann
      Lars Breddemann

      That's of course a correct remark - BW specific functionality like "exception aggregation" and "constant selection" do not map directly to SQL syntax, but require more complex expression to achieve the more complex (and more useful) results.

      Unfortunately, SAP has not done a good job of explaining those functions. Data analysts and engineers outside the "SAP sphere" don't know nor care for these SAP-specifics and no end user tools beside SAP ones support them.

      While SAP BW natively supports many very useful and otherwise complex query semantics, these semantics are effectively lost when trying to connect and work with non-SAP tools/environments. What could have been a good argument for investing into BW effectively has turned into an integration hurdle. This hurdle - making the semantics usable in other tools/environments - can be overcome by throwing some custom development at it. Effectively this means, BW customers have to "buy their way out" of a SAP-only environment.
      It's not hard to see why for the customers that paid this price, there is little incentive to ever go back.

      SAP has got very useful tools here - but by effectively locking those away (as "exclusive" features) - they just turn into unused capabilities.