Handling Before Aggregation (Exception Aggregation) in BW 7.X

Applies to:       SAP BW 7.X

Summary:      

This document gives a clear picture on How to handle (Calculate)  Before Aggregation (This option was available in the BW 3.x version) at BEx Query level which is obsolete in BW 7.x

Author:           Ravikumar Kypa

Company:       NTT DATA Global Delivery Services Limited

Created On:    24th July 2015

Author Bio  

Ravikumar is a Principal Consultant at NTT DATA from the SAP Analytics Practice.

Scenario:

In some of the reporting scenarios, we need to get the number of records from the info cube and we have to use that counter in calculations. We can easily achieve this in BW 3.x system, as there is a readymade option given by SAP (i.e. Before Aggregation in the Enhance tab of a Calculated Key Figure) at Bex query level.

But this option is obsolete in BW7.X system and we can’t use this option. But SAP has given a different mechanism to achieve this at Bex level.

The below illustration explains you this scenario:

Data:

0DOC_NUMBER	MAT_DOC	MATERIAL	MAT_ITEM	PLANT	CALDAY	PRICE	UNIT
12346	23457	ABC	3	2000	20150102	30	USD
12346	23458	ABC	3	2000	20150102	30	USD
12347	23459	DEF	4	3000	20150103	40	USD
12347	23459	DEF	4	4000	20150103	40	USD
12345	23456	XYZ	1	1000	20150101	25	USD
12345	23456	XYZ	2	1000	20150101	25	USD

The user wants to see the Price of each material in the report, and the format of the report is as shown below:

MATERIAL	Price / Material
ABC	30 USD
DEF	40 USD
XYZ	25 USD

If we execute the report in Bex, it will give the below result:

But expected output is:

MATERIAL	PRICE OF EACH UNIT
ABC	30 USD
DEF	40 USD
XYZ	25 USD

We have to calculate this using Counter at Bex query level. In BW 3.X version we can achieve this by using the option ‘Before Aggregation’ in Enhance tab of the Calculated Key Figure (Counter).

Steps to achieve this in BW 3.X system:

Formula to calculate Price of each material is Price / Counter.

Create New Calculated Key Figure (ZCOUNTER1) and give the value as 1.

In the properties of the Calculated Key Figure Click on Enhance tab:

Keep the Time of Calculation as Before Aggregation as shown in the below screen shot:

If we don’t select the above option,the Counter Value will be 1 and it gives the below output:

So we have to calculate Price of each Material with Before Aggregation property (Now the counter value will be 2):

Now the output of the query will be like this:

Now we can hide the Columns ‘Price’ and ‘Counter (Before Aggr)’ and deliver this report to Customer as per his requirement.

This option is obsolete in BW 7.X ( check the below screen shot) :

Create a Calculated Key Figure as mentioned below (Give value 1):

In the Aggregation Tab, unselect the check box: ‘After Aggregation’.

You will get the below message:

Info: Calculated Key Figure Counter (Before Aggr) uses the obsolete setting ‘Calculation Before Aggregation’.

Steps to achieve this in BW 7.X system:

Create a Calculated Key Figure as mentioned below (Give value 1):

If we this Counter directly in the calculation it will give the below output:

We can achieve the ‘Before Aggregation’ option in BW 7.x system by following the below steps:

Create Counter1 with fixed value 1:

In Aggregation Tab select the below options:

          Exception Aggregation: Counter for All detailed Values

          Characteristic: 0MAT_DOC (Because we have different Material Documents (23457, 23458) for the material ABC):

Now the output of query has given correct value for the material ABC and the other 2 are not correct as they have same Material documents (refer sample data):

Now create Counter2:

Aggregation Tab:

Exception Aggregation: Summation

Ref. Characteristic: 0MAT_ITEM (Because we have different Material Items (1, 2) for the material XYZ).

Now the output is showing correct values for the materials ABC and XYZ and still we are getting wrong values for the material DEF, as it has same Material documents and Material Items:

Now create Counter3:

    Exception Aggregation: Summation

    Ref. Characteristic: 0PLANT (Because we have different Plants (3000 and 4000) for the material DEF).

Now create New Formula: Price of Each Material

Price of Each Material  = Price / Counter3

Now the output is:

Now we can hide the columns ‘Price’ and ‘Counter3’ and show the Price of each material in the output:

Likewise we have to analyze the data in the info cube and we have to identify the Characteristics on the aggregation has happened at Bex query level and we have to use them as the Ref. Characteristic in the Calculated Key Figure and we can achieve the counter ( no. of records aggregated).