Skip to Content

Query Optimisation in BO Webi Reports

Introduction: The document helps in defining different means to optimize the BO query performance. It features use of different options available in Business Objects Suite to minimize the query run time. Need for Query Optimization: Query Optimization is essential to remove performance breakdowns. It improves the productivity as it reduces the time the query is interacting with the server. Thus we can run improve the number of applications processed in a particular time frame. It is essential to remove the overheads so that the report query takes less time to run. This presents the result set faster to the user and speeds up the application. Query Optimization techniques: Different types of query optimization techniques are mentioned below. • Array Fetch Size: Array Fetch parameter is set in the universe. It is the number of rows the server fetches from the database at once when the query is executed. If this is set to a high value, the number of interactions or transactions between the servers and database will be reduced and the response time will be minimized in retrieving data from database. Thus query time is minimized to an extent. • Short Cut Join: Running a Webi report for objects that are connected to each other via intermediate tables generates a SQL query containing many database tables in the FROM clause than those in the SELECT clause. This is due to the way the join is defined. The query runs such that intermediate tables along the join between the two tables are also traversed. This increases the time query takes to fetch the objects. Once the intermediate tables are removed the query takes less time comparatively. Using shortcut join in such cases to connect the objects eliminates the intermediate tables from the query SQL. Thus shortcut joins reduces query run time. • @Aggregate Awareness: Using @Aggregate Awareness to define aggregate tables to store summarized measures at particular granular level saves a lot of time in executing a query. It executes the SQL with aggregate table instead of detail table. Whenever the query is ran for a particular granular level, the aggregation defined for that level is incorporated in the query instead of calculating the aggregation at run time. This eliminates the time it would take to fetch all data from detail table and the aggregate to the granular level. • Derived Table: Derived tables are tables created at universe level. They don’t exist in database. Thus when objects from these tables are called by a query, the derived table is first created, evaluated and then the object fetched from it at run time. If these tables would have been created at DB level, the query takes less time as it avoids time required to execute table creation time. • Data Restriction at DB than Report level: Using prompts instead of report filters or conditions helps to reduce the time report takes to execute by restricting data retrieved from DB. Also using universe defined filters will fetch lesser data in DB than report level filters based on the Array Fetch Size parameter. If report level filters are used then the filter will be applied once all the data from the database is fetched. • Calculation at DB level than Report level: Performing calculations at DB level saves time BO spends on calculating them when the report runs and saves a lot of report execution time. Thus performing complex calculations, using variables or formulae in a BO report must be avoided or minimized as the calculations are executed and variables created and then executed at run time.

You must be Logged on to comment or reply to a post.
  • Unfortunately some of the comments are either misleading or plain wrong.

    Shortcut joins are not a silver bullet and should be use carefully. They typically have a greater chance of being used in OLTP systems or snowflaked schemas and should not be used to casually link tables with common columns that don't belong together.

    The derived table is created in the universe, not at run time. At run time it is no more than an in line select statement. You're correct that the DBA has no visibility and would not proactively tune it though.

    Calculation at the DB level can take longer and can often not be correction. Ratios/percentages/averages are often calculated at the database level then have to be recalculated at the report because the level that they are presented at in the report doesn't always match the level at which they were calculated! Similarly a variable may be quicker to calculate than an object that issues a function call to the database.

    The key thing in much of this that has been completely missed is the database itself! Working with your database administrator on such things as partitioning and indexing will give some of the biggest gains, along with identifying the server's quite times when you can run scheduled reports and get the server's "full attention".

    The article also suffers from either being a cut and paste job or bad formatting - a review of the paragraphs/bullets is needed to make it more readable.