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 Member]

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. 

[andreasja_schneider]

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

[Former Member]

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

[Former Member]

Andreas - I can't speak for Jack, but in my environment, we don't use the Rich Client - our users just use the HTML version.  However, for testing purposes it makes no difference.  For example, one report we have produces 34k rows (with 33 columns) and produces a 4.5MB file.  It takes 2min13sec to export to Excel whether I do this export using HTML or Rich Client.  We have much larger reports that take a much longer time, too, but still no difference in time between HTML and Rich Client.

I guess at least with Rich Client, it has a 'saving...' bubble on the screen until it is done saving it.  This is not the case with HTML.

Thanks,

missy

[andreasja_schneider]

Just guessing here:
Could it be that the Webi report has many local calculations (formuals and variables) or filters or merged dimensions? Just trying to pinpoint a possible scapegoat..

And yes, I do understand that it used to work faster, exporting same Webi Report to MS Excel with lower Patch/SP level.

[Former Member]

I just also confirmed that Patch 2 has not fixed the issue.  I ran a report that generates 46,973 rows and it takes 2'55" to pop up the save dalog.  I did this from the dhtml viewer.  the export works completely different depending on the editor or viewer that you are using.  DHTML using export-Current Document-Excel 2007 or Excel.  Either one produces same results.  From Java Editor, choose save as, choose my desktop or my documents, save as excel and it even took a bit longer. 3;03'.

I am an administrator and put every watch on this that we have available.  the only thing we see is a cpu spike but, it gets no where near 100% so it is not a cpu or server resource issue.  This is something in the coding of 4.1 vs 4.0 or earlier versions.

[Former Member]

Jack,

Via the WebiAdminTool, you can see whether the export is running or not (getPages).  Here's a screenshot:

Have you used this before? Here's two links you might find helpful:
http://scn.sap.com/docs/DOC-45626

http://scn.sap.com/docs/DOC-47300

This obviously doesn't make up for the performance, but it's been helpful to me in the past when questions/frustration arise about how long an export is taking - at least I can let the users know it really is still running...

Ours doesn't appear to be a server resource or cpu issue either.  I don't have pre-4.0 to compare to - we converted from a completely different system to BO 4.0 and are in the process now of upgrading all of our environments to 4.1 - so between 4.0 and 4.1 we saw no performance improvements, just bad performance overall with exports.  I will note that some folks on my team have suggested that performance was better in 4.0 SP4 (not sure what, if any, patch level)... and that performance started to decline once we moved to 4.0 SP7 Patch 7 and hasn't improved.

-missy

[Former Member]

Yes Missy, I have seen that.  I am also the trainer here so I always look at things from the perspective of the end user.  I like how the interface of the DHTML viewer looks for the end user to export.  It is much better to me than the java editor SAVE AS dialog to save to excel.  But, the one thing to note is that if you do it from the Java Interface it does show you a pop up that says "Saving" then entire time.  That is nice for the end user.  They can at least see something is happening.