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”.

Select SUM(ELIGIBLE_QTY) GROUP BY SALES_ORG;

Result –

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 –

Select SUM(ELIGIBLE_QTY) GROUP BY SALES_ORG,DIVISION;

 

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.