BPC 10.0 Problems when refreshing a large report

[Former Member]

hello everyone,

We are upgrading from BPC 7.5 to BPC 10.0 and are in the process of converting all the 7.5 reports to 10.0 and i'm having a problem with a large report.

We are on BPC 10.0 SP 17 for the Microsoft platform and we are on the EPM Add-in 10.0 SP22, we also use excel 2013.

The report in question consists of ~65,000 rows of data.   Basically, the row axis is running 8 different accounts by 630 Entity members by 13 different time members.   Many of the rows contain empty or blank data, but they are needed for other calculations in the report, so remove blank/empty rows is not an option.  For testing purposes i have created the report without any calculations to see how performance is impacted.

What I've found is that the initial report create time is ~ 25 seconds, however, if i change a member of the time dimension then refresh, the report takes ~10 minutes to refresh.   The 7.5 version refreshed in ~ 1 minute consistently.

After enabling logging for the EPM add-in it appears the overhead is related to clearing the old dataset perhaps?  these are the logging entries for the second refresh i see:

2015-07-06 12:16:30,317|INFO|OfficeWrappers|FPMXLClient.Office.ExcelReportCleaner.ClearOnData||||||1|Smart clearData topLeftData=Row 3 - Column 4,downRightData=Row 70562 - Column 5#

2015-07-06 12:24:11,264|INFO|OfficeWrappers|FPMXLClient.Office.ExcelReportWriter.WriteSmart||||||1|(Smart) Writting Time: 00:07:40.9323318#

I'm not sure where to go to next to try to optimize the performance on the subsequent refreshes.   If anybody has any advice it would be much appreciated.

Thank you!

Don Collins

[Former Member]

Just a followup on this issue.   We did have the same performance problems on our test environment, and again it was seemingly on the clearing of the report data that occurs after you refresh the report according to the EPM log.  

I ended up being able to resolve the issue by introducing VBA to refresh the report instead of using the native BPC Refresh.  By clearing the report data via the VBA, then refreshing the sheet, the refresh time is now consistently ~1 minute even with the added calculated formulas.

Thanks for the responses on this.

Don

[Former Member]

Hello Don ,let me know how many fields are calculated formulas and tell me if the excel report have VPA code or macros to calculate other figures. regards daniel

[Shrikant_Jadhav]

Hi Don,

Try this

Options -> User options -> others -> "Force symmetric Refresh for Large Asymmetric Axis.

Also Sheet options- > remove zero and empty rows

Shrikant

[Former Member]

Hi Shrikant,

Thank you for your response.

The report was already marked as Force symmetric Refresh for Large Asymmetric Axis.

As for the remove zero and empty rows, I need all rows in there for other calculations that I will be adding in.  For example, part of the reports function in 7.5  is that It takes average of the last 6 months then compares that average.   These empty and zero rows are used to compute that average.  I have not added any of these calculations in the BPC 10.0 report yet due to the performance.

I realize this is a large report if I don't remove zero and empty rows, but the strange thing is the initial creation of it is very fast (Especially relative to BPC 7.5), but a subsequent refresh is slow.

Don

[Former Member]

Can you reproduce the issue on a separate server as well - for example in test or dev?

Either way - you need to enable the BPC log with the option ALL and see what exactly is happening on the first run as well as the subsequent runs.

See SAP note 1635926 for how to enable logging.