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

[Former Member]

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

Detailed view :

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

Now sum is matching.