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:
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?
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.