BPC Standard | Member Formula | Average | ENABLE_HANA_MDX = X

[ChristianSass]

Hi Community,

we do have an issue with the calculation of average values in BPC Standard with HANA MDX active.

I try to describe the problem with different scenarios:

set up:

- SAP BWonHANA 7.5 SP10, HANA 122

- 1st of all I created a BPC Standard Model based on ENVIRONMENT shell

- I used the delivered model: planning to create an easy sheet

- I activated member formulas on account dimension

- rows: 2 base members (PL010, PL020) from account dimension

- columns: 12 periods of 2017

1st scenario: I enter values for each period on account PL010

2nd scenario: every 2nd period is NULL -> no value

- additionally, I created 3 member formulas:

1. PL010_AVG: AVG( PERIODSTODATE ([TIME].[LEVEL00], [TIME].CURRENTMEMBER), [ACCOUNT].[PL010] )

2. PL020_AVG: AVG( PERIODSTODATE ([TIME].[LEVEL00], [TIME].CURRENTMEMBER), [ACCOUNT].[PL020] )

3. PL020_AVG_2: AVG( PERIODSTODATE ([TIME].[LEVEL00], [TIME].CURRENTMEMBER), COALESCEEMPTY( [ACCOUNT].[PL020], 0) )

Everything works as expected:

1. Calculation of average is correct

2. Calculation of average is wrong since MDX cannot handle empty elements, Excel has the same problem

3. Calculation of average is correct, Member Formula is extended if "COALESCEEMPTY" which returns 0 in case of NULL

Since the performance of Member formulas is not sufficient, we activated HANA MDX

Report: BPC_MIGRATE_TO_HANA -> done

Environment Level: ACCELERATOR_ON = X -> ok

Model Level: ENABLE_HANA_MDX = X -> ok

Result:

1. Calculation of average is correct, as expected

2. Calculation of average is wrong, as expected

now I add my average formula with "COALESCEEMPTY" which I activated in the web UI without issues!

3. Calculation of average -> dumps completely !!!

Any idea on that?

I've checked function module UJN_API_GET_MDX_FUNCTIONS already and "coalesceempty" is supported

I think I remember that this function is not supported with HANA MDX but I would like to have a community opinion or sap note on that.

Do you have any hint on that or any other way to calculate average?

what I use as a workaround is the following formula:

"Sum( PeriodsToDate( [TIME].[LEVEL 00], ClosingPeriod( [TIME].[LEVEL 02], [TIME].CurrentMember) ) , [PL020])"

But we do have problems with performance and solve_order and I would like to use the proper average formula to calculate. Is this possible?

Many thx!

[former_member186338]

I will never answer question with images inserted using "Insert File" instead of correct "Insert Image"! I have written it so many times...

[ChristianSass]

You're absolutely right

[former_member186338]

Hope you are using:

PL020_AVG_2: AVG( PERIODSTODATE ([TIME].[LEVEL 00], [TIME].CURRENTMEMBER), COALESCEEMPTY( [ACCOUNT].[PL020], 0) )

Not:

PL020_AVG_2: AVG( PERIODSTODATE ([TIME].[LEVEL00], [TIME].CURRENTMEMBER), COALESCEEMPTY( [ACCOUNT].[PL020], 0) )

with HANA MDX

[ChristianSass]

I was expecting that answer 😞

Is there any other way to calculate average values else then:

"Sum( PeriodsToDate( [TIME].[LEVEL 00], ClosingPeriod( [TIME].[LEVEL 02], [TIME].CurrentMember) ) , [PL020])"

[former_member186338]

But why not to contact SAP? May be the issue will be solved in a reasonable time 🙂

"Sum( PeriodsToDate( [TIME].[LEVEL 00], ClosingPeriod( [TIME].[LEVEL 02], [TIME].CurrentMember) ) , [PL020])" - I do not understand your logic!

[ChristianSass]

... .Properties("BASE_PERIOD") .... is missing

we had almost the same conversation back in 2016 🙂

https://archive.sap.com/discussions/message/16964426#16964426

it's a shame that i is still not possible to calculate correct average values.

[former_member186338]

Once again - contact SAP for COALESCEEMPTY bug!

[former_member186338]

Then, looks like COALESCEEMPTY is not working on HANA properly. Bug with HANA MDX.

And the only solution - contact SAP support...

In general, not all MDX functions are working properly on HANA 😞

[ChristianSass]

yes, I do. It's not possible to activate the member formula with [LEVEL00].