Web Intelligence Performance Tips

The recommendations in this document are intended to help improve the general performance of BusinessObjects Web Intelligence documents. To that end, we’ll begin with an overview of Web Intelligence work flows that affect document performance.

Refreshing a Document

When a Web Intelligence document is refreshed, either manually or by the Job Server, the queries in that document are run sequentially. The first query to run is the query on the first tab in the Query Panel. Then the second query runs, and so on. The tabs can be reordered, if needed, to reorder the run sequence.

Once the query results are retrieved and stored in the document, Web Intelligence begins the process of combining results, where necessary, and calculating any variables or formulas in the document. Finally, the reports are rendered with the results.

Documents with many variables and/or many rows of data, can take a substantial amount of time to perform the needed calculations. Therefore, it is recommended to push as many calculations as possible to the universe. This allows the data to be calculated by the database, rather than the report server.

Queries that span multiple contexts will generate multiple SQL statements. How these statements are run, and combined, will depend on the JOIN_BY_SQL parameter in the universe. By default, Web Intelligence will generate a separate SQL statement for each context, as well as each measure from a different table. It will then run those SQL statement in parallel, and merge the results after the data has been retrieved. If the JOIN_BY_SQL parameter is set to Yes, then Web Intelligence will generate a single SQL query, with multiple Selects, and a FULL OUTER JOIN between each Select. The results will then be combined by the database, and the final results will be returned to the document. This can result in a much smaller and more efficient dataset being returned to the document.

However, make sure that queries do not contain objects that are not required for the reports. This is especially true of measure objects, since these typically come from one context only. Removing one unneeded measure can remove one SQL statement from the database.

Rendering a Document

Once the query, or queries, complete, the document will merge the results of multiple queries, then perform any and all needed calculations before rendering the results. When a user goes from one report tab to another, the new tab will run it’s calculations again, and then, once complete, display that tab for the user.

Reports that contain many calculations, in the form of formulas or variables, may render very slowly, depending on the volume of data contained in the report. For example, a report that contains 10,000 rows of data, and 10 columns of formulas or variables, will need to perform 100,000 calculations before rendering the results.

Therefore, significant performance improvements can be gained by reducing the number of rows contained on the report, as well as reducing the number of calculations needed. As mentioned in the previous section, it is much better to do calculations in the universe, than in the document. If the calculations are done in the universe, then the database performs the calculations once, and delivers them to the document. However, if the calculations are done in the report, they will happen when the query is completed, as well as when a user clicks on a different tab. Of course, documents will always have calculations in them, but we want to reduce them as much as possible, especially when there is much data in the report.

Data Reduction

As mentioned, one of the most common reasons for poor report performance is too much data in the report. Often, the reason for too much data is the need for multiple levels of data for drilling. Each level of a hierarchy that is added to a document causes the result set to grow exponentially, along with the document size. However, when data sets are large, alternate techniques need to be employed to reduce the amount of data in the document.

Using OpenDocument: One such technique involves creating a high level document, with only the data needed to display the top level before drilling. Then, using the OpenDocument function, we can create a URL that, when clicked on, will drill into a second document, passing values into prompts in that document. For example, if the user clicks on a Region to drill into that region, the Region name, or ID, is passed to the second document, which is then refreshed for that Region only. This technique keeps the first document small and agile, and keeps the second document small as well. Note that OpenDocument can be used in conjunction with standard drilling. A document can be set up to drill one or two levels within the document, then further drilling can be enabled with the OpenDocument function.

Using Query Drill: Another technique, which is similar to the OpenDocument technique, is Query Drill. The difference is that Query Drill operates entirely within one document. The document is built with only high level data. Then, when a user clicks on a value to drill into it, the query is rerun, with an additional query filter added, which will filter the results to those results needed for the drill. For example, if a user clicks on the West Region, the query is rerun, with a new query filter, where Region = ‘West’. This technique also keeps the document size much smaller.

Conclusion

There are many factors involved in improving the performance of a web Intelligence document. The techniques presented here are only a few. There may be things that can be improved in the universe, or at the database, to improve performance. It’s important to optimize your reporting in any and all places available.

Jonathan Brown from SAP, has also written an excellent paper on Web Intelligence performance here.

Advertisements

28 Responses to Web Intelligence Performance Tips

  1. Jansi D says:

    Michael, will you add the other factors as well when you find time? Or is this the end of the blog?

    • Hi, Jansi. I plan to write another post about universe performance tuning. As for database tuning, I’ll leave that to the database experts. Database tuning, of course, depends on the database.

  2. Jansi D says:

    Michael, I asked about the Web-I performance tips only. 🙂

    • Yes, this article is about the Webi performance tips within the documents. There are many other performance factors not included in this article, that are done elsewhere, such as the universe, the database, the BOBJ servers, etc. But those are topics to discussed in other articles, by me, or other people. 🙂

  3. Dave Rathbun says:

    You might want to include query stripping in your update. I think right now it works only on BW queries but it’s supposed to eventually (if it doesn’t already) work for all data sources. Maybe you can do the research so I don’t have to. 😉

    • Hi, Dave. In XI 3.1, query stripping only works with BW data sources, but in 4.x, it works with all data sources. It’s an excellent feature, provided you don’t need the extra objects for drilling. Query stripping is, simply put, a check box that will remove any unneeded objects from the SQL sent to the database for refresh. If an object is in the query, but doesn’t directly contribute to the report(s), it is stripped from the SQL. This can be a pretty sweet performance enhancer.

  4. Jansi D says:

    What about deleting the unnecessary variables and formulas created at the report level?

  5. Yoav says:

    Also:

    1.server sizing – putting some good old CPU ,sizing the timeouts,document cache,putting some extra webi servers,cluster
    2.Using the appropiate restrictions and conditions
    3.limiting (by designer ) the possible row amount

    Thanks

    Yoav

  6. Santosh says:

    thanks Michael very detailed and a useful performance tips. It would also be helpful if you know of techniques to optimize Servers for better processing. This is something that clients are expecting the developer to know atleast.

  7. Nitin Gonsalves says:

    Quoting “When a Web Intelligence document is refreshed, either manually or by the Job Server, the queries in that document are run sequentially. The first query to run is the query on the first tab in the Query Panel”

    In case, I am using Dataprovider(DP) A which is built before DP B and later I update the conditions of DP A to use the output of an object from DB B?

    Does it work in the same way?

    Nice article though.

    Cheers & Happy Holidays!!

    • Yes, it still works the same way. If DP A is on the first tab, and is using the values from an object in DP B, it will use the values from the previous refresh, rather than the current refresh, since DP A will run first.

      • Phil Reid says:

        Michael,

        Are you 100% sure on this? I had always assumed that DP B would run before DP A no matter which tab appeared first in the list. I’ve never read anything anywhere to support your statement, which worries me.

        Regards,

        Phil Reid

      • Hi, Phil. I had first been told this by Tech Support, and subsequently tested it by observing the query order at the database. So yes, I’m sure of this. Thanks for checking.

      • Nitin Gonsalves says:

        Michael,
        I am commenting after a very long time…
        But if your statement holds true, how would it work if I have purged the entire report?
        In that case I presume, DP A would not return any data as DP B doesn’t hold any data.

  8. srisrichand says:

    hi, can you explain about query stripping

    • Query Stripping is a feature that is available for documents that are built on OLAP data sources. Essentially what it does, it to remove anything form the query sent to the cube, that isn’t directly required for the report. In other words, if you have an object in the query that doesn’t contribute to the report, it will be removed from the MDX query sent to the OLAP data source when the query is run.

      • Nitin Gonsalves says:

        What if the Object is not actually used in the report but is a Part of a hierarchy, which can be used while drilling?

      • Good question. And I’m not sure of the answer. My guess is that it would not be included until you drill to that level.

  9. fiona says:

    Hello Michael,

    Thanks for such a helpful post!

    Currently I have a problem with WebI report design with several merged dimensions (BO 4.0 SP4). The WebI on BI-Launchpad show at first wrong data, but after refreshing it shows the correct data from merged dimensions. Also when we do the report dynamic bursting, the files to be shared in PDF format also show the wrong data.

    Is it also because of the performance with big join-dimensions? It is based on MSSAS Cube, how could I optimize it? It would be really helpful, if you could give me some advise on this.

    Thanks very much. Fiona

    • Hi, Fiona;

      Sometimes merged dimensions don’t do what we expect them to. I haven’t found any bugs specifically related to merged dimensions (Except with the Upgrade Management Tool), so it’s probably not a bug causing the problem. Without seeing the document, I can’t really help much with the issue. Perhaps you have some internal expertise that could help you on this one.

  10. Fiona says:

    Hi Michael,

    Ok no problem. Thanks for the reply anyway. I will let you know once the problem is solved.

    Cheers,
    Fiona

  11. Amit says:

    (1) What are the situations where we have to write custom query in query panel? OR how to tune WebI by writing sql?

    What I understand that the query is generated according to the selected objects and filters in query panel so to what extent we can change this generated query. If wrote custom query for a report and the user changes some filters, applies sort etc then again the query will be changed so how the generated query and custom queries are handelled.

  12. Amit says:

    (1) Does Schema Type, SCD Type, Dimension/Fact Table type, OLAP/OLTP affect while creating Unverse/Report? If yes then how?

    What I understand that the above things are already decided by the Database/Data Modeling team and the Universe designer just need to identify the tables that he/she needs in the universe and after inserting those tables the only work is to create joins, removing traps, loops and applying security,creating conditions.

    And the report designer just need to select objects according to requirement of the report then doing some report specific things.

    • The data modeling team needs to consider the BI tool when creating the data model. Different tools prefer different models. However, the universe developer will need to tune the universe based on the type of database being used.

  13. Amit says:

    How to achieve this — “Note that OpenDocument can be used in conjunction with standard drilling. A document can be set up to drill one or two levels within the document, then further drilling can be enabled with the OpenDocument function.”

    • Generally speaking, it’s best to avoid writing custom SQL for a report. These reports become very difficult to maintain, and, as you’ve stated, the custom SQL is overwritten if the user makes any changes in the Query Panel. Your best bet is to tune the universe, so that it generates the most efficient SQL.

    • The Webi user guide gives details on how to create drilling within a document, as well as how to create an OpenDocument hyperlink to another document.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: