Allow Member Formula

[sap_user62]

Hello Friends,

Env 10.1 standard on hana/BW 7.4 - enable hana mdx is on.

I would like to know your take on this subject, Is clicking "Allow Member Formula" terrible for report performance.

I have accounts ticked for "allow member formula",

I have a requirement for a member formula with account and functional area combination.

I am thinking twice, if I should tick "allow member formula" on functional area and write a member formula or write a script to calculate the values.

I understand that "allow member formula" is irreversible. FA has 2 hierarchies.

Thanks for your time

Ed.

[former_member186338 · Accepted solution]

"In this case does Functional area dimension need to have the allow member formula ON." - incorrect! "ON" - only if you have member of functional area with formula!

"I assumed all the dimensions in the definition need to have member formula turned on." - wrong assumption!

The reason of the error is somehow different!

[sap_user62]

Vadim, I totally agree with your explanation. These member formula are meant to be run for reporting on Financial model only (which has all the dimensions we use in the member formula), though we can see the member in other models as we share dimensions.

Report on a model which has missing FA,

UJO_READ_EXCEPTION_017 severity: error description: Dimension FUNCAREA does not exist in XXXXXXXXXXXX (Member formula). -- very valid error --

I did not understand the requirement that a dimension on which a member formula is based should be a part of all the models in the environment.

As I have tested, account dimension is not a part of allocation model, but all my formulas which involved account dimension have successfully being processed and gave accurate results in reports. Same is the case with account and profit center combination member formula.

The only issue I had is when FA is added to the mix.

[former_member186338]

"I did not understand the requirement that a dimension on which a member formula is based should be a part of all the models in the environment."

Sorry, but have to repeat again and again: member formulas are global for ALL Models. BPC can't read the information from your brain that you will use this member ONLY for reports for some particular model!

And BPC is validating member formula against all models (or may be against some models or...)

In my previous comment I have explained the possible workaround:

"If member formula contain member of the dimension missing in the report model then instead of processing formula simply return NULL for report value."

Also formula has to be validated that at least one model has all dimensions used in formula...

[sap_user62]

Thanks Vadim for your link.

I ran another test in a different sandbox environment. Identical service packs as a the QA system. Here Functional area is ticked ON for "allow member formula". FA is not a part of one model.

Test 3a - Created couple of member formulas with account and FA combination and couple of them with Account only. All of them processed well and gave the correct result.

2 formulas with the following definition

[PL535] + [PL193] + [PL735] + [PL736] - ([FUNCAREA].[COS],[ACCOUNT].[PL193]) - ([FUNCAREA].[COS],[ACCOUNT].[PL735]) - ([FUNCAREA].[COS],[ACCOUNT].[PL736]) + ([FUNCAREA].[COS],[ACCOUNT].[XXXX8809)

another 2 formulas with only one account [PL189]

Test 3b. -

Here I added Profit center to the formula, Profit center is not a part of one of the model and the allow member formula on profit center is not ticked on.

member formula with account+FA, account+FA+PC, account - all 3 work well.

As I go through the models, I realized that account dimension is not a part of one model(allocations, we have a separate account dimension)

Hard to understand this behavior. I want to utilize member formula instead of scripts to fill up these non-gaap measures.

[former_member186338]

Try to understand, that dimensions and dimension formulas are global for environment!

Let's assume that you have report with a member with dimension member formula that contain members of the dimension missing in the model. If you run this report error will be generated!

Environment: E1

Dimensions: D1,D2,D3,D4,D5,D6

Dimension D1 has member M11: [D1].[M12]+([D1].[M13],[D6].[M61])

Model: M1 Containing: D1,D2,D3,D4,D5 - D6 missing!

Report for model M1 containing member M11 from dimension D1 will generate error.

It's very hard to understand the check logic done by SAP to validate member formula... Models used, order etc...

Theoretically the check can be implemented in SAP code like:

If member formula contain member of the dimension missing in the report model then instead of processing formula simply return NULL for report value.

But how it's done in you BPC version - only low level ABAP debugging can answer!

[sap_user62]

These are the tests I ran. All member formula's are created on account dimension. Functional area is not ticked for "allow member formulas". There is one model which does not have FA dimension.

Test 1- created 2 member formulas with accounts only. Works well.

Test 2a - cleared all the member formula's. Created one new member formula with account and functional area- works well (surprisingly)

Test 2b - Now I added a 2nd member formula with the same account functional area combination. It fails - "Internal Error- no result set"

Test 2c - 2nd member formula added with a single account. It fails agin - "Internal Error- no result set"

It seems the member formula with combination seems to work for one member formula only. If i try to add another member formula its fails outright. short dumps. SLG1 logs doesnt help.

[former_member186338]

"Test 2a - cleared all the member formula's. Created one new member formula with account and functional area- works well (surprisingly)" - this is the bug!

The rule is simple - if you have member formula with number of dimensions mentioned in the formula text (not counting the dimension of member formula)then all dimensions mentioned has to be available in all models of current environment!

Example - let's assume all models have TIME dimension. Then you can use TIME members in dimension member formula of account dimension.

https://help.sap.com/viewer/a2049170bfeb4178ace32222842c3ec1/10.1/en-US/4c2cbc14f3f65ed7e10000000a42...

[former_member186338]

"Yes. There is a model with account dimension but no functional area dimension" - this is the reson of the error!

For this model the provided formula is incorrect! You can do nothing with it.

[sap_user62]

Thanks Vadim for your reply.

The member formula will be a combination of Accounts and Functional Area sitting in Account dimension.

In this case does Functional area dimension need to have the allow member formula ON.

Account dimension has the Member formula turned on. In of the test I got an error, so I assumed all the dimensions in the definition need to have member formula turned on.

[former_member186338]

P.S. Can you provide your formula text?

[sap_user62]

[PL03193] + [PL03735] + ([FUNCAREA].[COS],[ACCOUNT].[PL03193])

[former_member186338]

OK, nothing wrong with this formula...

But do you have in the same environment the model without functional area dimension? Can be a reason for the error!

[sap_user62]

Yes. There is a model with account dimension but no functional area dimension

[former_member186338]

I don't think that you will have serious issues with performance!

But, not clear:

"I have a requirement for a member formula with account and functional area combination."

Do you need to create member formula for member of functional area dimension??