OBIEE Performance Guidelines

The following guidelines are applicable for both OBIEE 11g and OBIEE 10g
 

Identify the BI Component( BI Server, Presentation Services or the database connectivity) that could have contributed to the issue.

• Check if physical Sql run by user contributed to the issue.
• If the sql runs quickly then Presentation Services might be taking more time to display the results. This will happen if you have a significant number of chart or pivot views for the reports. Check if changing the existing reports to table views improves performance.
• If the physical SQL is taking a long time, performance tuning needs to be done on the database. Most databases have many parameters and advanced features that will help run the queries efficiently. DBAs can help tune the database for these slow running queries. Oracle Database features like proper indexing, partitioning and Star Transformations can help performance to a great extent.
• Enable Usage Tracking and see what are the user actions performed and analyze what could have contributed to the issue.
• Caching can be very helpful in alleviating performance issues for complex queries that are used many times. iBots can be used to seed queries during off peak hours to achieve improved performance.
• Disable unused init blocks and consolidate the other init blocks. It is a good idea to create SQL in the init blocks to populate many variables instead of having many simple init blocks.
• Check how many rows are being retrieved. The OBIEE should not be used to display hundreds of thousands of rows, instead use reports that are filtered and aggregated by some attributes.
• Too many pivot and chart views on a dashboard page can make the UI very slow to display.  Check for hidden sections and guided navigations that always run.
• Guided Navigation when used we should realize that all the reports underlying in that section will be executed and only the report that satisfies the condition will be displayed. It is highly possible that in such cases report will run without any filters on it.
• Depending on the ability of the database to handle certain functions, the DB features tab should be set appropriately in the repository. From the physical layer click on Database to see the DB Features tab. If one of the database features is turned off, incorrect SQL might be generated by OBI. This might be expensive in terms of performance. Check if any features have been changed.
• The repository should be designed according to best practices. De-normalize dimensional objects, combine several dimensional attributes into one flat table. This will help reduce joins. Model your physical tables in a way that should help in creating simpler Business Models.
• Use Aggregate Tables when necessary to improve performance. Aggregate tables will need additional ETL, storage and complex mapping efforts.
• If you have complex logic that involves complex SQL, it is a good idea to consider pushing this logic to ETL rather than handling it in the repository.
• For a large number of concurrent users, use the following guideline for the maximum number of connections in a connection pool:
    20% of concurrent users * number of reports per dashboard
• Perfmon logs gather memory usage statistics. They are especially useful when the entire application becomes slow some time after start up. Check if the application responds quickly after restarting the services. If you start seeing issues after some time of using the application, this might indicate you have memory-related issues.
  On Unix, use the following commands to gather performance statistics:
  ps -ef|grep <obi process>
  top