How to perform exception aggregation on BPC member

[Former Member]

Member formula is widely used in BPC environment, but many customers facing the same challenge when running queries with aggregation on member formulas, the calculation result is incorrect. For example in following table, the correct result should be 0.2, but query result usually displayed as 0.5:

ACCOUNT	ENTITY	PERIODIC	explanation
T100T	C1000	30	T100T=SUM(T101, T102)
T101	C1000	10
T102	C1000	20
T200T	C1000	150	T100T=SUM(T101, T102)
T201	C1000	100
T202	C1000	50
R100T	C1000	0.2 0.5	R100T=T100T/T200T Not: SUM(R101, R102)
R101	C1000	0.1	R101=T101/T201
R102	C1000	0.4	R102=T102/T202

This is a BPC production limitation at the moment, which is unable to handle exception aggregation on member formula for transient queries, and this can’t be handled in BW side alone either, as BW BEX query is unable to recognize the difference between member formula rows and normal rows.

The solution below is to combine both BPC member formula and BW exception aggregation methodology to handle this challenge for reporting.

1. Scenario

Here is account type, account hierarchy setup from BPC side:

               

R100T and its children nodes R101 and R102 are ratio nodes, which are calculated from T101, T201 etc.

2. Member formula

R101 and R102 are member formula to calculate ratio. Besides the member formula on children nodes, also design accordingly member formula on parent node level (R100T = T100T/T200T).

3. Add “PERIODIC” as restricted key figure, and create “Formula 1” which is actually “PERIODIC”,

4. Set the Exception Aggregation on “Formula 1”as “No aggregation along hierarchy”.

5. Create “Formula 2” with following logic:

‘Formula 1’ + ( ‘Formula 1’ == 0 ) * ‘Periodic’

This formula is used to check the value for “Formula 1”, if “Formula 1” has a value (not empty),  then (“Formula 1” == 0) will return false value, which is 0, it’s going to use the value from “Formula 1”.  If “Formula 1” is empty ( because no aggregation on hierarchy for the exception aggregation),  then (“Formula 1” == 0) will return true value, which is 1, the value from “Periodic” will used for the final result.

6. Use standard aggregation on “Formula 2”(no exception aggregation)

7. Run the BEX query, you will find the R100T aggregation on:

“Periodic” is 0.7 (incorrect), which is a result of 0.2(number calculated from member formula in BPC) + 0.5 (aggregation result from BW, 0.1 +0.4) “Formula 1” on R100T is correct, 0.2(number calculated from member formula in BPC), but it’s empty for T100T, T200T, because we set exception aggregation on BW ( No aggregation on hierarchy).“Formula 2” is correct for all aggregation nodes, because we used formula in BEX query.(if “Formula 1” is empty, take the value from original aggregation value from “Periodic”.

8. Now hide the key figures “Periodic” and “Formula 1”,

The query result will look like below, correct aggregation numbers will be displayed for all nodes:

Above is just an example for hierarchy with periodic, similar method could be used for YTD/QTD calculations like:

	2014.01	2014.02	2014.03	2014.Q1
A	10	20	30	60
B	5	8	6	19
C=A/B	2	2.5	5	?

Feedback:

If you have any comments, questions or feedback, please kindly send an e-mail to:

George Yang (ge.yang@sap.com)

Freeter Xie (freeter.xie@sap.com)

Alex Shen (yang.shen@sap.com)