BPC 10.0 on NW - Twice to refresh EPM Report

[Former Member]

Some users have to refresh twice before all context driven members completely populate a report.  Certain users have the issue...others do not.  I don't believe it is security related since the report renders on second refresh, but for some, it works the first time.  Any thoughts?

Thank you

[Former Member · Accepted solution]

Guys,

We've found the cause of the problem, at least for our case.

In the input form in question, we use EPMMemberProperty on time dimension in column axis based on a condition on one of its properties to drive dynamic number of dimension members to expand.

Now, it's this setup that causes the problem. BPC doesn't seem to determine the result of EPMMemberProperty before the first refresh, but seems to do it [after / at end of] the first refresh, making the second refresh work.

So what we did (for now) was we went into the cell in which EPMMemberProperty is used and press Enter simply to trigger the calculation of the formula. Then, we simply saved the input form back onto server.

Of course, there's a better to solve this by not using EPMMemberProperty altogether and determining the property some other way, but that's another story and it depends on the requirements of the input form (or report) in question.

As for how I found out it's the time dimension, I simply turned on BPC_STATISTICS parameter and went into transaction UJ_STAT to find out that the time dimension member passed to the query in the backend was the wrong year -- it's last fiscal year, meaning it's not refreshed to reflect new fiscal year. Other dimension members in the query were fine.

Hope this helps more or less,

Anirut W.

[Former Member · Accepted solution]

Hello Anirut,

Pressing Enter, then saving to server....was that a permanent fix...or did you have to do that each time you ran the report?

[Former Member · Accepted solution]

I'd be reluctant to say that it's permanent fix.

Yes, it's a one-time thing that us developers do; users don't have to do it every time they open the report.

No, it's not really permanent in that we have to do it again next fiscal year.

So we're looking to revamp the report and get rid of reliance on EPMMemberProperty to avoid having to do this every fiscal year.

[Former Member · Accepted solution]

Hi Anirut,

In a similar situation, I needed the page axis to be set to a certain intersection based on the property of the employee dimension which was driven by the EPMUser API.

I used VBA to re-write the EPMUser & EPMMemberProperty into the relevant cells when the workbook is being opened so that could work without a refresh.

This works the same way as "Enter" in your case.

So, I was able to eliminate refreshing twice as the cell contained what it needed.

I wrote the following function in the Module

Public Functional AFTER_WORKBOOK_OPEN()

...xxxx...

End Function

Hope this helps!

[Former Member · Accepted solution]

Oh, nice. That's a good alternative. Thanks for sharing. 😉

[david_ings]

In case anyone else has similar issues - we had this issue and came across a different solution that worked for us.

We had a member in the context that had to be refreshed twice in order to update the report (we had an override based on a property in the same way as Mike's original problem).

To resolve, we used the EPMContextMember formula which updates immediately upon changing the context. We then forced the page axis member to look at the cell containing the epmcontextmember formula (so that it always updates to whatever this value is).

e.g.

In cell A1 -> EPMContextMember(,"COST_CENTRE")

In page axis for Cost centre, amended formula as follows directly in the cell:

Before:

EPMOlapMemberO("[COST_CENTRE].[PARENTH1].[CC_10001]","","CC_10001 - Cost Centre 10001","","000")

After:

EPMOlapMemberO(A1,"[COST_CENTRE].[PARENTH1].[CC_10001]","CC_10001 - Cost Centre 10001","","000")

By doing it this way, when changing the context, the page axis updated immediately, and so only 1x refresh was required to activate our dimension override and update the report.

David

[Former Member]

Hi Mike,

We're having the exact same issue as you mention, but let me add more my case details from my investigation.

To find out if it's just her machine, I get my user to log in as her on other machines with Excel 2010, and the issue also comes up. So it's not just her machine, but rather her user-specific setting(s), which I don't know. And this means we can rule out machine-specific settings such as EPM Add-in's 'User Options' -> 'Automatic Refresh on Context Changes'and EPMXLClientPreference.XML as Charlie suspects. Thanks, anyway. 😉

Another thing I've found is even though our users run on a mix of Excel 2010 and 2013, it seems to happen only 2010 and not 2013.

And it happens to her user in both QA and Production systems, so it's not system-specific, either.

I've looked into note 1908840 and applied VBA macro source code to our workbook and can confirm that it solves the problem, but that means the refresh occurs twice, just with one click, which we're not very happy with as it means more refresh time, so we're still searching for a better solution. The SAP note doesn't mention the actual cause of the problem, so it's not clear how to fix it.

By the way, we're on BPC 10.1 SP 4 on HANA.

I'll keep checking back this thread and provide the solution if I ever find one.

[former_member186338]

Still no simple scenario to reproduce the issue...

[Former Member]

Thank you Anirut...appreciate the feedback.

[Former Member]

Just to clarify and elaborate a bit more of what I said that I'm afraid some parts were not clear.

To clear things up:

We have a group of about 20 users, some of whom use Excel 2010 and some others use Excel 2013.

There's only 1 user that experiences this exact problem and she's on Excel 2010.

To elaborate more:

Most users including the one experiencing this problem use EPM Add-in version 10.0 SP 21 Patch 4. And I just made sure that updating EPM Add-in to the latest version -- version 10.0 SP 23 Patch 1 -- didn't help.

So it seems to me that it's a user-specific problem. and NOT a system-specific, a software version-specific, nor a machine-specific problem. And I'm now focusing on all the user-specific settings to find out what causes it.

[former_member182709]

Hi Mike,

Just found a KBA below which has similar symptom as yours.

KBA  2118006 - Refreshing report twice generates the different result - BPC NW

The solution is to apply note 2022276.

If the issue is not solved by this note, I suggest you compare the file EPMXLClientPreference.XML between working machine and non-working machine, which is located in C:\Users\\AppData\Local\EPMOfficeClient.

If you don't have any offline comparing tool, you can use online compare tool http://text-compare.com/.

Charlie

[Former Member]

Thank you, I will investigate that.

[Former Member]

Hi Mike,

Do you have refresh on context change on?

try switch it off.

and which SP are you using for EPM add on?

Andy

[Former Member]

Hello Andy,

I do not have that selected...we are on SP22.  I can check if the non-working users do.

[former_member186338]

Hi Mike,

The detailed report description with screenshots?

Vadim

[Former Member]

Hello Vadim,

1. EpmContext determines the costcenter

2. Employee dimension override uses the costcenter from step 1 to filter the appropriate employee list.

The employee list is the part that takes the second refresh for some users, but not all.

Do you need more detail?

[former_member186338]

Hi Mike,

Is it a screenshot?

Because of 2 steps you have to refresh twice!

Vadim

[former_member229290]

Hello Mike,

Could you please have a look at the following SAP Note:

1908840 - An EPM Add-in report has to be refreshed twice to update data

Best Reagrds,

Noura

[Former Member]

Thank you, Noura,

Yes...I saw this note...However I need to use the Cost object in my employee dimension override function, I don't need the cost center in the report.

Appreciate your response