How to improve report performance

[Former Member]

We have migrated from BPC 7.0 to BPC 10.1. Running MS version on MSSQL 2012, Excel 2010, EPM 10.0 SP24 .NET 4, Server version 10.1.4.0.

I now faces an issue with our Forecast input schedule. In our reporting structure we have legal entity (ENTITY) and BA structure (OPERATING). Each Entity can have 1 to several different Operating to report on. In our monthly input we expand each operating as columns, but in forecast the columns are fixed to 12 months. So in Forecast we create 1 input sheet for each Operating using VBA.

Our master sheet consist of 2 reports (this sheet is copied using VBA). Default report have Account in Row, and Time in column (Income statement). Report001 (Balance sheet) have Account and AccDetail in Row and share column (Time) with default report.

As an example from BPC 7 to create 12 input sheets and Expand takes approx 90 seconds. We have manually migrated the input schedule to make it work with VBA and EVDRE in BPC 10. API replaced with EPM API etc. Running the report in BPC 10 then took approx 700 seconds!!!!

I decided to recreate the input schedule using EPM from scratch. I was again disappointed. The best Refresh time I can do is approx 150 seconds. A more normal outcome is approx 250 seconds. To be honest all functions from BPC 7 is not yet in place in the EPM report. I need to add a number of hidden EPM sheets to collect all figures.

Does anyone have similar experience?

How to make a well functional report work as fast in BPC 10 as in BPC 7?

I have seen improvments with other reports, but then there are less cells. In my Input sheet I have approx 4.200 input cells (350 * 12). With 12 sheets it's 50.400 cells. There are a lot of empty cells in the data grid.

I have tried different approach

Loop each sheet and run RefreshActiveSheet or RefreshReportDataOnly. The one I'm using now is RefreshActiveWorkbook. Any other ideas? I have also checked "Force Symmetric Refresh for Large Asymmetric Axis", but couldn't see any change in performance.

[Former Member]

I just wrote a document that worked for our on demand packet style reports, I don't know if it would work in an MS based environment, but its worth a shot.

The overhead of multiple EPM worksheets and reports is taxing on the client try pulling the data on a single sheet/report.

[Former Member]

I might have found the reason for slow performance. On another workbook (open in background) I had a Linked Picture with reference to a dynamic range. When I remove the link the performance increased dramatically. After removing the Linked Picture my refresh time seams to be stable at 40-45 seconds. This is more in line with expectations.

Does anyone know how a linked picture in another workbook can disturb EPM performance?

[Former Member]

Hello Clas,

We found through experience that any external links to other workbooks, objects, named ranges, etc. slow down performance with BPC 10.0 tremendously.  We've simply recommended to all of our users to make workbooks entirely self-contained, as some linked workbooks were taking 20 minutes just to open, nevermind refreshing.

[Former Member]

Hello Darryl

Interesting to hear your experience. My problem was that there were no links in my EPM workbook, it is self-contained. The other workbook is intended to be a start page with some general information. I have now removed the Linked Picture from that workbook and performance increased.

[Former Member]

Hi Clas,

migrating ffrom 7.0 to 10.1 is always an issue - not only due to the reduced EVDRE functionality. Your Approach to re-create your reports from scratch using EPM is definately right. From my experience some VBA can significantly slow down the report performance. Just give it a try and do a step by step analysis. First step you can do is running your report without using of any VBA. If you experience a hard performance boost, you at least know where the problem comes from. This is, beside checking log files, my approach for analyzing perfomance issues and in most of my cases the problem comes from VBA.

Just to mention as an Approach.

Best regards,

Karsten