Performance Tuning of BO reports

[former_member203850]

Performance related issues can be encountered at the Visualization/Reporting Layer, Semantic Layer, Database Layer and Server Layer. Following are the steps to do a root cause analysis of the performance issues:

• Report level query execution in the database.
• If execution time of the query is less than report execution time then tuning might be required at Report /Universe/Server level.
• If execution time of the query is same or greater than report execution time then tuning might be required at database level.

The performance at the Report level can be made optimum by the following actions :

        General Check Points:

1) Minimize usage of Report variables/formulas:

If the report is pulling tons of data, doing loads of joins, using lot of report variables and formulas, report may run very slow. Report variables and formulas are loaded and calculated in memory at real time. As variables are created at real time and calculations are performed at report level, reports takes more time to execute.

          For Example:

When we are dealing with big reports, then we should minimize usage of report variables/formulas and try to place them at universe or better at ETL levelto deliver high performance reports.

2)    Operands precedence

We should use the faster operands when creating conditions. The operands are approximately listed from fastest to slowest (=, <, >, <=, >=, between, In List, Matches Pattern, <>, Not). AND conditions together are faster than OR conditions together.

i. WWebI Report Check Points:

1) Breaks/Crosstab Reports/Charts/Alerters will degrade the performance a bit in reports:

Use it if it’s very much required but do not use it frequently.

2) Remove auto height/width if possible in report.

3) Use refresh on demand instead of refresh on open.

4) If we have a very large reports we should not use refresh on demand in BI Launchpad:

Instead we should try to schedule the report, as refreshing will increase load on processing server while scheduling will be done on job server which is less busy than processing server in most of the cases.

5)    Usually indexes are created on codes/ids.

Always use codes for filter conditions. If report query using description as filter, then add one more filter with the corresponding code.

This concept works fine for pre-canned reports, not for ad-hoc reports (user generated). Users have no idea which columns are indexed. One idea is to create object names mentioning whether they are indexed or not <normal object name + indexed/not indexed.

6)    Using Hyperlink feature to enhance report performance.

We can keep pre refreshed documents using hyperlink for detail information in reports. So that records will store in the cubes and it prevent queries from hitting the Database frequently.

7) Allow duplicate rows option in report

The option in Query Panel to remove duplicate rows is a tremendous performance hit. Business Objects will suppress duplicate rows in the report.

😎 Union data

Whenever possible we should use Union instead of adding additional data providers. Union makes the db server do the synchronization instead of the Business Objects.

The performance at the Database level can be made optimum by the following actions :

1) Use of Explain plan

• Analyze all the tables used in report query. This will update table statistics (information on that table) and guide oracle to generate Optimized execution plan.
• Check the explain plan. If explain plan shows full table scan for any huge table, create index on that table or check if index is disabled.

2) Use of index

• Check whether indexes are present for all columns used in joins and filters of the query.

• If number of distinct values in that column is less then creation of Bitmap index would be more effective, otherwise normal index.

• Use composite indexes. Generally LOOKUP table contains a Code, Type and Description. If the Type is normally retrieved with the code, create composite index <code, Type> to escape table read.

• Do not use calculations or functions on indexed columns. Otherwise it will bypass the index

Examples :

Disables Index	Uses Index
Salary/12>2000	Salary>2000*12
TRUNC(Sales_Date) > TRUNC (sysdate)	Sales_Date BETWEEN TRUNC(sysdate) and TRUNC(sysdate) + .9999

Use indexes on the below type of columns :

• Primary key columns
• Foreign key columns
• Columns used in ORDER BY clause
• Columns used in GROUPBY clause
• Columns specified exactly in WHERE clause.

In general use index columns that are included in WHERE clause using conditions that don’t include functions or calculations.Do not use indexes for

• Columns with text, image or bit data types
• Columns which are not unique.
• Columns those are too wide to be useful for indexes

3) Use of hints

• Sometimes use of hints optimizes the performance. Useful hints for BI reports are –
  • ALL_ROWS:ALL_ROWS
  • PARALLEL: The PARALLEL hint helps to specify the desired number of concurrent servers that can be used for a parallel operation.

How to use hint in reports

Create hint objects in the universe; select those objects first while developing the reports. Use custom sql and include hints in the query. This is not a good practice but it can be used for verification i.e. to check whether hint will help in reality or not.

4) Avoid IS NULL, IS NOT NULL

Avoid such comparisons as much as possible. NULL values should be replaced with dummy values. Using NVL, Decode, and Coalescefunctions wecan replace Null values with some another values.It can be done both at report level as well as in database level.

5) Partitioning of the Tables in the Database

Always we should partition the large table based on specific criteria to avoid the backup, restore and lengthy query times etc.The criterion may be number range, date range etc.So it is better to do partitioning of tables to avoid the performance issues in future.

6) Usage of In and Exists in sub queries

When we write a query using the IN clause, we're telling the rule-based optimizer that we want the inner query to drive the outer query.

While in case of EXISTS in a where clause, we're telling the optimizer that we want the outer query to be run first, using each value to fetch a value from the inner query.

To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires us to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the sub query are very small.

Hope this will be helpful.

Regards,

Pranay