Poor performance when refreshing BEx Analyzer workbook

[Former Member]

We have a BEx Analyzer workbook that consists of about 10 worksheets, each displaying data from a different query. Refreshing the workbook in BEx Analyzer takes a very long time, typically 20 minutes or so. During this refresh process there is very little activity on the BW system itself. Each individual query runs on the web in a few seconds. Aggregates have been built for every query in the workbook.

The workbook's file size has also increased from ~2MB to 15MB as changes are made to the workbook.

Any suggestions on how to improve workbook performance would be helpful.

Thanks,

Jason Kraft

[Former Member · Accepted solution]

Hi Jason,

On the file size, I recommend a VBA macro which reformats the sheets for you, particularly getting rid of use of colour as much as possible.

The portal performance and the BEx Analyzer performance may be dependent on different servers. It could be that the BEx Analyzer is using the local machines CPU power. Just monitor CPU usage during idle times, opening of BEx and then refresh of the query data to see the demand.

The portal on the other hand could use an entirely different server which has more CPU power and capacity than the platform that Analyzer is being run on.

Incidentally, 20 minutes wait for a workbook with 10 sheets isn't necessarily a huge waiting time if these queries are returning many rows.