Question Re: Improving export to Excel performance in Technology Q&A

Improving export to Excel performance

Former Member — Thu, 22 May 2014 19:02:06 GMT

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

Re: Improving export to Excel performance

former_member191664 — Thu, 22 May 2014 19:22:58 GMT

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

Re: Improving export to Excel performance

Former Member — Mon, 16 Jun 2014 18:41:22 GMT

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

Re: Improving export to Excel performance

former_member191664 — Mon, 16 Jun 2014 20:00:26 GMT

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

Re: Improving export to Excel performance

Former Member — Thu, 03 Jul 2014 14:46:01 GMT

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

Re: Improving export to Excel performance

former_member191664 — Thu, 03 Jul 2014 16:49:22 GMT

Hi,

please make sure "compression="on" ... to both HTTP and HTTPS listener in tomcat's server.xml,
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

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 16:48:37 GMT

Is anyone familiar with KBA 1994745 such that any additional insight could be shared here? It's a pretty sparse SAP note... I don't have an XI3.1 environment to compare to - we just have the 4.0 environment where the exports are slow. However, it says that whatever the issue is, has been fixed in 4.1 SP4 Patch 2 (and SP5)... I don't see an ADAPT explicitly listed for that in the Patch 2 release notes though...

Just thought I'd ask. We are in the process of upgrading to Patch 1 now, but may move to Patch 2 if this actually helps.

Thanks,

missy

Re: Improving export to Excel performance

former_member191664 — Thu, 04 Sep 2014 17:16:26 GMT

Good point; there is no ADAPT found in the KB 1994745 nor in the SAP BusinessObjects Business Intelligence Suite 4.1 Support Package 4 Patch 2 Release Note for "1994745 Exporting a Web Intelligence report to Excel format takes much longer in BI4.1 than in XI3.1"

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 18:46:39 GMT

Hi Missy,

The issue mentioned in KBA 1994745 is fixed in 4.1 SP4 and SP5.

What is your server OS windows or Linux.

If it is Linux then it is a known issue.

See sap note 2008223 and the ADAPT mentioned in it says that it will be fixed in SP8 Patch 10(tentatively).

Regards,

Pranay Sharma

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 19:24:20 GMT

Hi Pranay,

Our OS is Windows. I saw when the issue mentioned in KBA 1994745 was to be fixed, which is what sparked my interest and question, but I just didn't see a related ADAPT or any additional information on the issue. We experience slow excel exports in 4.0 when file size is large.

Are you saying the notes 1994745 and 2008223 are for the same issue, both on Linux?? If so, I would say the problem exists on Windows too. To date, SAP has considered it working properly... which has not been easy for our users to swallow.

Additionally, there is not even any feedback to the user here saying the export is still going... separate issue, I know, and one we have submitted an idea for at the ideas place, but still. All the more reason this one caught my eye in the release notes... so I was really hoping to get more details on it.

Thanks,

missy

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 19:28:24 GMT

Hi Missy,

KBA 2008223 is only for linux specifically and 1994745 for general export performance improvement.

You can create an incident with us so that your report can be quickly tested in 4.1 SP4 and SP5 and that will confirm the fix.

Hope this is helpful

Regards,

Pranay

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 19:34:19 GMT

Also for the ADAPT query now going forth we do not create ADAPTS for defects.

The internal process is changed hence you will not see any ADAPT in SAp notes in future.

Now we mention the fix environment directly in sap note.

Re: Improving export to Excel performance

Former Member — Thu, 04 Sep 2014 19:41:45 GMT

Hi Pranay,

Fair point on the ADAPTs no longer being used - I do recall hearing that in a webinar now that you mention it.

Perhaps I will open an incident to test one of our excel reports prior to/instead of downloading and applying patch 2 in one of our sandbox environments and testing on my own... it may very well be quicker!

Thanks,

missy

Re: Improving export to Excel performance

andreasja_schneider — Sat, 06 Sep 2014 09:27:11 GMT

A) Depending on the granularity of your report 3 years might be just too much data volume anyway. So check how much data (rows and columns) is actually being returnd to your Webi report!

B) Are you using a hardcoded OLAP connection or SSO?

For SSO enabled OLAP connections every user might see different data according to his analysis security setting in SAP BW, you would then have to schedule the report for each user separately.

Re: Improving export to Excel performance

Former Member — Mon, 22 Sep 2014 13:32:35 GMT

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

Re: Improving export to Excel performance

former_member191664 — Mon, 22 Sep 2014 18:02:07 GMT

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

Re: Improving export to Excel performance

Former Member — Tue, 30 Sep 2014 18:48:51 GMT

This is still an isssue in 4.1 sp4. In fact, it is far worse for us in 4.1 sp 4 than it was in 4.0 sp4 patch 13. When we upgraded to 4.1 sp4 the excel export takes 4x longer and we have been trying to find a reason. These are not even huge files. We have some that are like 30,000 records and it seems to take forever. No pop ups, hourglass, or any indication that it is happening. Just have to wait. And, if you think nothing is happening and select to export it again, then it takes 2x longer. If anyone finds the answer please post to everyone.

Re: Improving export to Excel performance

andreasja_schneider — Wed, 01 Oct 2014 09:24:35 GMT

Just curious: how long does it take when exporting the data from Webi RICH Client to MS Excel?

Re: Improving export to Excel performance

Former Member — Wed, 01 Oct 2014 13:04:06 GMT

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

Re: Improving export to Excel performance

Former Member — Wed, 01 Oct 2014 13:20:34 GMT

Jack,

Since there is no indication to the user that the export is actually going on, I use the WebiAdminTool to monitor it in the background. Obviously that's not something a user can do - generally they contact me (as a BO admin) and I check in on it. It's not the most efficient activity.

Related to that though, please vote for our idea/add comments: https://ideas.sap.com/ct/ct_a_view_idea.bix?i=388E5BA6

We were previously on 4.0 SP7 Patch 10 prior to upgrading to 4.1 SP4 Patch 1 and then, for the purposes of Excel performance testing, Patch 2. We saw no performance improvement... at all. As I mentioned though, others who upgraded that we have talked to have seen improvements. This was encouraging, but then disappointing when we did not have the same results. We are still comparing environments, settings, etc. with them to see why there is a difference... no smoking guns yet.

We have some huge files, but not all - a range of 145k rows with 11MB files to 5k rows with 4MB files - all take a very long time to export (and yes, no on-screen indicator letting you know it is exporting).

I would certainly post if I had an answer! Hopefully others would too...

Thanks,

missy