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

[natasa_nakic]

I agree with everything Missy and Jack wrote so far.

I am the admin/developer of an internal SAP BOE system. We had 3.1 , 4.0.3 then 4.1 and finally 4.1. SP4. Everything is on the same server: DB (SQL Server 2010), BOE, tomcat 7. The save to excel definitely became slower and my users are also confused with no response during the first part of the export process.

short workflow: schedule webi report to save an xlsx file to a local folder on the server (same server as above)

It is a single SQL webi report, no additional formatting. Our analysts build ad-hoc webi reports from our custom unx, export the results to Excel, format the excel file further and send that to end users. Since "save as excel" started being so slow I showed them how to do scheduling to excel to offline that part at least so they can do other things in the meantime. Even the scheduling to xls can take hours.

For example, the SQL from one simple single data provider/no formatting/35k rows webi report  takes 5 seconds to refresh in MS Enterprise studio. When scheduled, it takes hours. As a workaround I end up running the SQL in MS Studio, saving as txt and importing/formatting in Excel. I do not want to teach my analysts to do this and they should not have to.

My feeling is that the process of saving to file could be optimized further as the same issue happens even if I try to save result as txt, again only on local file server, and not even copy to enterprise, just the bare minimum of work for the scheduler and it still takes very long time.

Any good ideas, please let me know, this is becoming a serious issue for us. We invested a lot of work into building a data warehouse/unx/training and now this has become a bottleneck.

Thanks,

Natasa

[natasa_nakic]

We did further testing with SP3 and the development version of SP5. This problem seems to have started in SP3 (we did not check SP2) continues in SP4 but in SP5 it looks better. No ADAPTS to link it to, it may be some other fix/regression that was causing the performance change.

We are holding off the upgrade from 4.1 to SP4 as this is a showstopper for us. As soon as SP5 is out we'll test again.

[Former Member]

Boy misery loves company, but I'm glad we're not the only ones seeing performance issues with excel exports - for a while there it seemed like we might be!

I'll keep my fingers crossed that there is a performance improvement with SP5 and keep my eyes peeled for it to come out (later this month right?) so we can try it out in one of our sandbox environments too.

Keep us posted on your testing!

Thanks,

Missy

[Former Member]

Natasa,

I submitted an incident to request SAP support test one of our reports with 4.1 SP5, but they had basically the same performance results we see currently on 4.1 SP4 Patch 1/Patch 2.  I will try in our environment once it is released still too, but so far it doesn't appear to be an improvement for us...

Just wanted to pass along that update...

Thanks,
Missy

[natasa_nakic]

Thanks Missy,

We tested on development version of SP5. I'll update this thread as soon as the official SP5 is available (says 15th Nov but we don't see it out yet) and we repeat the tests.

Natasa

[former_member190903]

We have this issue on 4.1 SP1 Patch 8, so I think it was even earlier that you think. When we removed the 12,000 row report tab, keeping the 3,000 row tabs, it was much faster. Perhaps the APS that renders the report in Excel runs into memory issues and needs its memory bumped up to handle larger rows?

Jon

[Former Member]

Jon,

I believe we have tried bumping up the memory for our APS services on our end, but possibly not since we upgraded to 4.1.  In past discussions with SAP though, I didn't believe there was any one particular service that impacted the export to excel?

Our 3 APS servers are split and memory spread across them in varying amounts based on services.  Would you mind sharing what service you are referring to on the APS and the memory setting you have for it?

Thanks,
Missy

[natasa_nakic]

Our team tested the official SP5 and found an improvement in the speed of exporting to Excel.

The same tests were run as before and the export time was finally acceptable, about 30 min for 23MB exported xlsx file instead of several hours with the previous 4.1 service packs.

Good luck to everyone!

Natasa

[former_member190903]

See

for the BI 4 Process Flows. Under Web Intelligence, see Export a Document.

    In short, the Web Intelligence Processing Server is responsible for exporting to PDF or Excel. If it is scheduled, it may also use the Destination server. We use 6GB on ours and may need more.

[Former Member]

Interesting... If I am reading that right, that would imply that we should be allotting more memory to the webi servers themselves and distributing less to the APS if our struggle is exports.

We have 3 webi servers - with 32GB of RAM each.  20GB on each server is split across our APSs, with a lot of that being put towards Webi Monitoring and DSL Bridge services.

What confuses me with this theory though is that I have watched the memory usage and have never seen it spike beyond 1-2GB being used by the WIReportServer.exe service (via Task Manager) even during large excel exports... but I can run a few tests and see if it makes a difference.

Thanks,

Missy

[former_member190903]

I saw another post that mentioned IE 8 vs IE11 performance differed. So, what version of IE are you using and have you tried Chrome and Firefox? We hve 128GB of memory on our Servers and I too have noticed they don't appear to use up the memory. Are you using Wily Intrascope to see what's happening?

    It may also have something to do with Cache/File storage speed, since it has to save it to Temporary Storage in transit. The drives used should be on the server and as fast as you can afford.

    It would help if SAP could draw us a clear map of everything that may affect performance for large reports and a variety of actions like exporting and scheduled actions.

[denis_konovalov]

Since early days of Webi, the BusinessObjects and now SAP position was always - if your webi report takes longer than couple minutes to refresh or is bigger that couple pages - you're not using the product properly.

Schedule such reports, use Data services or other ETL tools to export your huge datasets to other formats.

Just because BI4.x platform allows almost unlimited report sizes now doesn't mean that having such reports is a good thing.

[former_member190903]

While I agree BO is not an ETL tool, you still dodged the question. What do you think is slowing down such large reports? The main issue is that it use to be faster. So, why is it so much slower now? There was a lot of code change in getting to BI 4.x, something changed to make this worse. It affects reports under 500,000 rows too and if SAP didn't want to support this size, then why work to enable 1 Million rows in Excel when it use to be 65K?

[denis_konovalov]

A lot of things has changed so its impossible to say what affected this directly. I know there was a fix in 4.1 Sp4 P7 to make export process as it was in 3.1 speed wise. I'm not aware of how effective that fix it or test/comparison results with/without it.

It might be slower because 4.1 requires 4x as much resources as 3.1 does ...
maybe its because VM infrastructure is not keeping up with your deployment demands (export process is very disk I/0 dependent and that is Achilles hill of VM's.

SAP didn't enable 1 million row support , Microsoft did. 65K was limit in Excel , not in Webi.

Besides obvious things already discussed here you're best avenue to get answers/fixes for this is to have SAP support Incident opened on it.

[Former Member]

Hi Denis,

Do you happen to have the ADAPT # for that excel fix in SP4 P7?  I'd like to see what patch thats included in the SP5 PX stream.

Or do you happen to know in which patch on SP5 its forward fit to?

Thank you,

Nick

[former_member190903]

True, MS did that change, but SAP worked to get the output onto one tab instead of splashed across multiple tabs.

    Clarify for us, even though we deployed real hardware, each APS is still a VM and I/O is an issue with that architecture choice. Is there anything we can do to optimize this part?

    Also, if you know, are there any IE Registry settings that may be slowing this down?

Thanks for joining the conversation Denis

Jon

[denis_konovalov]

If your BOE is deployed to servers on real hardware, VM's disk I/0 issues are not related to you. (I mean I/O and proper performance tuning is still needed, but not the same)
APS are java processes, not VM's and as far as I can see not part of Export workflow, only refresh in some cases.
Can't say anything on IE registry. The usual "internet speed" hacks should help here , MTU size variations etc... easily googled. Not sure how much improvement that can make.

If you see this at Bi4.1 Sp4 P7+  or Sp5 P3 + or Sp6 + -- you should raise a support ticket.
Per SAP Note 1994745 it should be fixed.

[Former Member]

Hi Denis,

Thanks very much for the info.  I just asked SAP support to test one of our problematic reports in an SP4 Patch 8 environment and an SP5 Patch 4 environment and they reported the same export to excel timing as we see currently on SP4 Patch 1.

We are currently evaluating which of those 2 releases (either SP4Patch 8 or SP5 Patch 4) to go to, so I will wait until we install it in our own environment before passing judgment, but support's results do not leave me hopeful.

To your point earlier, this particular report that we test with takes <45sec to refresh in webi, but takes 6min at a minimum to export to excel.  It has approx. 145,000 rows and 23 columns.  It is just under 12MB in size.  Prior to moving to BO (i.e. using the previous tool), it took 1-2min to export to excel.

Our users do schedule reports, but prior to moving to BO, exporting to excel wasn't something they had to think twice about with these reports, so to them it is decreased functionality to have to schedule it instead.

While we are talking about SAP Notes, I did see another one of interest in the release notes, 2106759 in SP4 Patch 7 / SP 5 Patch 2, which supposedly resolves the lack of a status indicator when using the DHTML pages.

So... even if we don't have a performance improvement, at least we'll get some feedback that it is still churning....

Thanks,

Missy

[Former Member]

Quick update - we downloaded and installed both Patch 8 on SP4 and SP5 Patch 4.  We had some issues with Patch 8 that were not appearing in SP5 Patch 4, so (currently) we are moving forward with SP5 Patch 4...

I have only tested a small handful of our reports so far (at least one was one I sent to SAP to test for us too), but we have seen improvement in the export to excel (at least 2min has been shaved off) !!AND there is an indicator (spinning mouse icon) to signify it is DOING something! <does happy dance>

This is just testing in our single-server Sandbox environment so far, we will be moving to Dev/Test over the next couple weeks and hopefully to production end of May... but so far things look promising! 

Thanks,

Missy

[former_member191664]

Thank you for verification, Missy.

2114259 - Saving as Excel a WebIntelligence report from WebIntelligence Rich Client is slower in SAP BusinessObjects WebIntelligence 4.x than in 3.1 version from BI4.1 SP5 Patch4 fixes it. Regards, Jin-Chong