Calculate KPI values for High level view and detailed view using Concatenation+Exception aggregation

This blog will clarify how to resolve the issue related to calculation of KPI for high level view and detailed view

where detailed view is based on drill down of 2 different Infoobjects and this combination is unique key :-

For Example:

we have a division calculation for one KPI : (Unit charge/Consumption * 100 ).

High level view:

Account Determination ID	Unit charge Value	Consumption	Unit charge/Consumption
Commercial Customers	275.9100000	1,872.300	14.73642045

Detailed view :

If we drill down on Installation and date output is :

NOTE: Installations 6000359409 is repeated and date 03/31/2014 is also repeated corresponding to different Installation.

Account Determination ID	Installation Number	To Date	Unit charge Value	Consumption	Unit charge/Consumption
Commercial Customers	6000359300	03/31/2014	194.69000000	1172.10000000	16.61035748
Commercial Customers	6000359359	03/31/2014	51.60000000	518.60000000	9.94986502
Commercial Customers	6000359409	02/28/2014	15.59000000	95.57900000	16.31111437
Commercial Customers	6000359409	03/31/2014	14.03000000	86.02100000	16.30997082

however if we see the sum of last column:

Unit charge/Consumption

16.61035748

9.94986502

16.31111437

16.30997082

Sum is =

59.18130769

If we use Exception aggregation alone , it won’t work for example if we aggregate on Installation output will be like below :-

42.87079519

Account Determination ID	Installation Number	To Date	Unit charge Value	Consumption	Unit charge/Consumption
Commercial Customers	6000359300	03/31/2014	194.69000000	1172.10000000	16.61035748
Commercial Customers	6000359359	03/31/2014	51.60000000	518.60000000	9.94986502
Commercial Customers	6000359409	02/28/2014	29.62000000	181.60000000	16.31057269
				Sum =

How to achieve this in BW :-

1. Concatenation:

To achieve this we have to create a new Infoobject of length equal to sum of both these Infoobjects and get concatenated value into this new object :-

2. Exception agreegation

Now apply exception aggregation on

Unit charge/Consumption calculation based on this new Infoobject

Output will be displayed like below :-

High level view:

Account Determination ID	Installation Number and Date	Unit charge Value	Consumption	Unit chare/Consumption
Commercial Customers	600035930020140331	194.69000000	1172.10000000	16.61035748
Commercial Customers	600035935920140331	51.60000000	518.60000000	9.94986502
Commercial Customers	600035940920140228	15.59000000	95.57900000	16.31111437
Commercial Customers	600035940920140331	14.03000000	86.02100000	16.30997082