Improving export to Excel performance

[Former Member]

Does anyone have any tips on improving excel performance when exporting a webi report? I don't want to get into discussions on why users are trying to export larger files (in excess of 6MB); I am just looking for suggestions.

When a user runs the export, not only does it take a long time, but there is simply no notification that the export is even processing or if it stopped, etc.

In previous tools, the user could run the same query and it would run and export very quickly, but in BO it could take upwards of 10min or [much] more.

Any ideas? We are on BO 4.0 SP 7 Patch 7 currently, with a planned upgrade to BO 4.1 SP4 over the summer.  Maybe this is no longer an issue with 4.1?

Thanks,

Missy

[former_member191664]

Hi,

If the Excel output bottleneck is found to be network bound, you may improve the large Excel file network transmission between BI4.0 and client browser by adding "compression="on" compressionLevel="1" compressionMinSize="2048" ... application/vnd.ms-excel,application/pdf" to your Tomcat7 server.xml connector to enable excel and PDF compression.

There is a catch, though, the client workstation and browser is responsible to decompress the zip file once receives it, and the decompression may add extra load to your PC if undersized.

Hope this helps,

Jin-Chong

[Former Member]

Hi Jin-Chong,

Thanks for your response.  We will have an opportunity to test this suggestion out this week in our test environment, so I'll let you know if helps us.  I did verify that while compression is set to on in our server.xml files, excel is not one of the applications listed.

To clarify - we would only see a potential performance improvement when a user is exporting to excel directly; this would not impact compression/performance when they try to schedule the report as excel to email, etc., correct?

Thanks,
Missy

[former_member191664]

Hi,

"this would not impact compression/performance when they try to schedule the report as excel to email, etc., correct?"

That is correct.  The compression on excel is only for the HTTP/HTTPS communication, and it has no impact on the scheduled job output to email as excel.

If you click on the successful instance on a timestamp with excel output format from a report's history in InfoView/BI Launch Pad, the transmission of this excel output will be compressed in HTTP/HTTPS communication.

Hope this helps,

Jin-Chong

[Former Member]

Hi again,

We applied this change and ran several tests.  I am not sure how to view the history of these tests to see whether it says HTTP/HTTPs communication though since it is not a scheduled report.  We are just running the report, selecting Export to Excel 2007.

Our results are mixed - several reports were actually larger in size (despite same row/column count) after we made the change, than they were prior to the change.  The time it took for the pop up to come up (to open or save) generally decreased though (despite the larger file size).

I am not sure that it hurt us here, but I am confused as to why the file size would be larger now.

I appreciate any additional insight you can share here...

Thanks,
Missy

[former_member191664]

Hi,

1. please make sure "compression="on" ... to both HTTP and HTTPS listener in tomcat's server.xml,
2. use HTTP watcher and Firefox + Lori to monitor the HTTP/HTTPS traffic and the page load turnaround time and amount of data before and after the change.

Hope this helps,

Jin-Chong

[Former Member]

Sorry for the extended delay in replying here... but no luck either way.  I also just tried the compression changes to the server.xml file with Patch 2 (on 4.1 SP4).  Times are consistent with what they prior to the change.

Patch 2 on its own made no difference for us either, though others we have talked with have reported better performance. All very peculiar...

Thanks,
missy

[former_member191664]

Hope you log a SAP incident as Pranay Sharma suggests before and look forward to your resolution here when performance bottleneck is fixed.

Regards,

Jin-Chong

[Former Member]

Hi Jin-Chong,

I did open an SAP incident as Pranay suggested, but the same results were see - no performance improvement on Patch 2 for us.  I even applied it in a sandbox environment myself just so we could test some additional reports as well.  No luck.  I know others who have seen performance improvements, running in a similar environment and with similar sized reports, so I am really not sure what to make of it.

Thanks,
Missy

[former_member190903]

Be sure to check the box for Caching Excel when scheduling, it may help when users open it.

Jon