BPC Member formula - performance issue

[former_member200880]

Dear All

we are using BPC10 NW. In that we have almost 25 member formulas. Each member formula refers to function in script logic.

my query is

1. Is member formula advisable? as it may cause performance issue

2. Since the member formulas inturn referring to script logic, does it causes heavy performance issue? like when we input or refresh a single cell its taking long time.

Please advise.

If member formulas can cause this performance issues, what could be alternate way?

Can it be thru script logic for all the calculation mentioned in formula

? If we go with script logic, it will write the data into Database which increases load in dB. please advise.

Second thing is..

we have hierarchy like this

A ----> Parent1

           B------->Base member   --- Member formula is C*D for example

           C----->base members

           D------>base member

when we do reporting on A, we could see aggregation of C and D alone but not the value of B.

Is it the standard behavior that parent member wont aggregate base member containing formula

Please advise

thanks

[former_member186338 · Accepted solution]

First: In many cases like KPI/Rate calculations Dimension member formula is the only solution and can't be replaced by script logic permanent calculations! Read:

Second - using functions to extend formula length is fine, no performance issues, only maintenance issues (2 places to configure).

In some cases when you simply want to sum few members like [M1]+[M2]+[M5]... you can use alternative hierarchy (will improve performance!)

"Is it the standard behavior that parent member wont aggregate base member containing formula" - correct, member formulas are not aggregating!

Vadim

P.S. Calculations like [Price]*[Qty] - better to perform in script logic with permanent result storage!

[former_member200880 · Accepted solution]

hi Vadim

thanks for the reply.

For all the members there are calculations like 10 input parameters.

i.e (a+b+C+..... /x+y+z)*2000.

Does it cause performance issues?

because only for this model we are facing severe performance issues even a single refresh of  report taking time.

please advise

thanks

[former_member186338 · Accepted solution]

Sorry, but performance depends on hardware, database etc...!

you may try to compare refresh speed for the formula like:

[A]/[B]

with:

([A1]+[A2]+[A3]+[A4]+[A5])/([B1]+[B2]+[B3]+[B4]+[B5])

If you will see some difference you can create hierarchy to aggregate A1,...,A5 -> A and B1,...,B5 -> B

Vadim

P.S. There are no universal solutions, you have to experiment yourself

[former_member200880 · Accepted solution]

hi Vadim

is it advisable to go with script logic.

I would like to put all code in script logic and do calculation in script logic and removing the memberformulas.

is it good idea?

only barrier would be, it will write data to database.

please suggest

[former_member186338 · Accepted solution]

Sorry, you didn't read the link I provided! For ratios you can't use script logic! Do you understand why?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you for the link.

Cheers!

Ranjeet

[former_member200880 · Accepted solution]

hi Vadim

yes I read the link of yours.

But didnt get it exactly.

we have a script logic which has functions. And each member in account dimension has the member formulas. And inside memberformulas we are calling these functions in script  logic.

sample piece of code in script logic  for member SA_54

*FUNCTION fn_sa_54(SA_02,SA_104,SA_107,SA_110,SA_113,SA_116,SA_119,SA_122,SA_125,SA_128,SA_131,SA_134,SA_137,SA_140,SA_143,SA_146)

// SIFa - Contractor

// (All contractor actuals by precursor) / Exposure hours * 200,000

IIF([PR_ACCOUNT].[PARENTH1].[SA_02]=0,NULL,([PR_ACCOUNT].[PARENTH1].[SA_104]+[PR_ACCOUNT].[PARENTH1].[SA_107]+[PR_ACCOUNT].[PARENTH1].[SA_110]+[PR_ACCOUNT].[PARENTH1].[SA_113]+[PR_ACCOUNT].[PARENTH1].[SA_116]+[PR_ACCOUNT].[PARENTH1].[SA_119]+[PR_ACCOUNT].[PARENTH1].[SA_122]+[PR_ACCOUNT].[PARENTH1].[SA_125]+[PR_ACCOUNT].[PARENTH1].[SA_128]+[PR_ACCOUNT].[PARENTH1].[SA_131]+[PR_ACCOUNT].[PARENTH1].[SA_134]+[PR_ACCOUNT].[PARENTH1].[SA_137]+[PR_ACCOUNT].[PARENTH1].[SA_140]+[PR_ACCOUNT].[PARENTH1].[SA_143]+[PR_ACCOUNT].[PARENTH1].[SA_146])/[PR_ACCOUNT].[PARENTH1].[SA_02]*200000)

*ENDFUNCTION

[former_member186338 · Accepted solution]

Hi Vijay,

Unfortunately I can't help you... You don't want to hear me...

1. You use INCORRECT syntnax for the functions - already told you! Absolutely incorrect! Read help! *FUNCTION / *ENDFUNCTION - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP ...

2. Functions are used just to bypass the 255 symbols limitation for the dimension member formulas - nothing related to performance. There is no real function call - just text replacement!!!

3. Your function is calculating ratio - you can't use script logic instead!

Vadim

[former_member200880 · Accepted solution]

hi Vadim

sorry to bother you.

i have read your post.

Average percent values on nodes.

would you be little kind to me and let me know why cannot ratios be calculated in script logic.

For calculating drivers we normally use script logic right?

like wise cant we use script logic instead of memberformula

say for example when account is  A= ((B+C)/D)*100

[former_member186338 · Accepted solution]

We can calculate ratio formula using script logic only for base members. But for parent nodes the ratios will be simply summed resulting in incorrect figures.

Example:

You have sales departments - dimension DEPARTMENTS: S1,S2,S3 with parent - STOT

You have accounts: REVENUE and HEADCOUNT

You want to report KPI - Revenue per Sales person REVPERHEAD

You can use script to calculate base REVPERHEAD for each S1,S2,S3

But you will have absolutely meaningless result for STOT And for YEAR.TOTAL... Etc!

Hope it's clear!

Vadim

[former_member200880 · Accepted solution]

yes its clear..

but we are not writing any calculations on parent..

all the formulas, ratios i was talking about is for bas member only.

[former_member186338 · Accepted solution]

It's not possible to write to the parents. But on the report with parents you will have incorrect results due to aggregation of base members. Users will be happy to see year total ratio as a sum  of months ratios

[former_member200327 · Accepted solution]

If your formula has only additions or multiplication by a constant then you can replace it with a script and store the result. In all other cases reports will show wrong values at nodes. So, if your report shows base members only, then you can use script as well.

For all other cases you can use SQE BADI that is called when report is executed.