Skip to Content
Author's profile photo Former Member

Dear aBI: Why are my Live Office Connections So Slow?

It is time for yet another entry in the Dear aBI series of blog posts where community members help answer the questions of other community members. I’ll skip over the part talking about me, except to say that I rule.

This bout’s question comes from Paul in Dubai, who writes:

Hi all,
 
We are using the BO XI 3.1 SP3 and live office 3.1 sp3. We have created a dashboard which connects (via Live Office) to a Webi report that uses 30 universe queries to create 12 blocks which are each pulled in. The dashboard takes 29 seconds to load. The Webi report has been scheduled, and my Live Office connections are set to pick the latest instance. Is there any way to optimize the live office connection. The size of my Webi report is 35 MB; does that make a difference in my Live Office connection?

Regards
Paul

Two things stand out to me about this question. First, thank you Paul, for being polite. Is it that hard for the rest of you advice seekers to begin with a salutation and close with “regards”? I would hope not. Second, I’m actually pretty impressed that your dashboard loads that fast.

Good job on scheduling the Webi Report and using the latest instance in your Live Office connections. That is generally accepted as a best practice whenever it is possible to schedule. Unfortunately, with this much data and processing going on, that’s a bit like fighting a deer with your bare hands; you just aren’t bringing enough firepower to the party.

Even though it is going against an existing instance, Live Office still has to “view” the instance before it can pull it in, and the rest of your dashboard also has to initialize. I’ll bet if you just try to view the report in Infoview it takes too long to load. Am I right?

If I’m right, you need to figure out some way to make that Webi report load faster. My first step is to try taking out all of the formatting except that which is absolutely necessary (because you’ll reformat it on the dashboard anyway). Then I’ll try to reduce the number of queries and the number of variables on the report, because even though the load of merging that data and renaming it is very small, it adds up, and every second counts.

A final Live Office solution would be to break it up into separate Live Office reports and not call them all when you are loading your dashboard. For instance (and depending on how your dashboard is set up), you could not bring in all of the data the first time the dashboard loads, but rather load data for a given chart when the user goes to that chart. I’m not a big fan of this, as it is a pain and the user still has to wait for it to load (it is just more spread out) but I have seen it done.

If you try all of that and it still isn’t fast enough, you could go back to the absolute basics and use QaaWS. You can’t schedule QaaWS, but it is pretty easy to create materialized views or temporary tables that have the data all laid out just the way you want them ahead of time. You then just have to make some universe objects that point to your new, pre-run source tables and you are good to go. This is obviously a little painstaking, but it really is much faster because you don’t have to wait for the Webi report to initialize before it can be pulled into the dashboard at runtime.

Best of luck, Paul, I hope this helped.

Sincerely,

Jamie Oswald

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Hi Jamie and Paul,

      Another option to consider would be to use the Webi Rich Client and BI Services, which tends to perform better than Live Office and allows more flexibility.  It uses the same method as QaaWS, however much better!

      Kind regards,

      Josh Fletcher

      Author's profile photo Anil K
      Anil K
      Hi Josh,

      Could you tell us the advantages BI services over WRC has over QaaWS.

      Regards,
      Anil

      Author's profile photo Former Member
      Former Member
      Hi Anil,

      BI Services thru WRC allow a block of data (with formulas etc in built) to be published as a web service.  You are also provided multiple methods within the one service which allow you to read data, pass prompts, and even drill into the block of data.

      I did a recent podcast about BI Services, you can find it here http://geek2live.net/posts/new-vodcast/.

      Thanks, Josh

      Author's profile photo Anil K
      Anil K
      Thank you Josh for the Podcast. Interesting and Sounds Promising!!

      Could you inform us if there will be any limitations of BI services through WRC on SAP BW data like  Charactereistic-Hierarchies.

      Regards,
      Anil

      Author's profile photo Former Member
      Former Member
      Hi Anil,

      I'm afraid I haven't worked with SAP BW and BI Services in depth. We've done a project for a client recently using BI Services on top of BW to feed Xcelsius dashboards, and there hasn't been any showstoppers.

      BI Services is still relatively new though, so no doubt it will be greatly improved in BI4.

      Cheers, Josh

      Author's profile photo Ingo Hilgefort
      Ingo Hilgefort
      Hi Anil,

      in terms of meta-data support the BI WebService supports what the universe supports.

      The recommendation actually is - in case Xcelsius is the target - to use the direct connectivity from Xcelsius to SAP BW.

      regards
      Ingo Hilgefort

      Author's profile photo Derek Loranca
      Derek Loranca
      Great answer, Jaime.  I agree with your assessment about LO.  We actually moved away from QaaWS and LO in favor of XML to bring back the data.  Then again, you know me- it's all about the VLDB!

      Josh- great points about WRC and BI Services.  Yet another good way to use WRC and another nugget I can take back to architecture to help accelerate the WRC rollout...

      Author's profile photo Former Member
      Former Member
      The first thing that occurred to me is that, if the Webi document is 35Mb, it contains significantly more data than is needed for a dashboard. Are your universe objects aggregating at the database? Make sure they all have aggregate functions in their Select statements. If they are already doing that, then make sure the document's queries aren't retrieving any data that isn't needed for the dashboard. Don't bring in granular data that isn't needed for a dashboard.
      Author's profile photo Former Member
      Former Member
      We faced the same challenges and found some weird behavior under the covers.  We opened a ticket and were told it was working as expected.

      If you have 1 webi with 4 blocks. The Live Office connection will run the entire report 4 time in the Live Office connection.  Even if it is pre run it will have to grab the latest instance 4 times.  This can be huge!

      It actually makes more sense to have several individual webis with 1 block each -- counter intuitive.

      This shaved off > 15 seconds.

      We have also found that IE8 allows 6 threads at once.  IE6 only allowed 2 threads at a time.  You would think that the parallelization would be a good thing but for some reason it caused a delay.  We have found if you kick off the refreshes one by one we could reduce another 5 seconds.

      Having a tools like HTTPWATCH was very helpful to see these issues.  You see each connection created, connection execute time and closing the connections.  You can download a free version but it is worth buying the full featured version.
      It was the database monitoring that identified that each connection executed the same 4 blocks.

      Hope this helps.  If anyone has workarounds to what I described, please share.

      Author's profile photo Former Member
      Former Member
      I guess I would like to politely add that I would be interested it why SAP BusinessObjects does not see the 2 points I mentioned below as bugs.  I would be more than happy to recreate the scenerios for the development team if need be.

      Thanks.

      Author's profile photo Andreas J A Schneider
      Andreas J A Schneider
      I wish that Xcelsius could execute LiveOffice connections in parallel, similar to Xcelsius with SAP Bex queries via BICS!