There can be many reasons due to which we would be doing performance tuning in business objects environment, for example reports refreshing time is very slow or you are going to add lot of users and thinking of performance improvement in advance. Actually Performance tuning should always be the first step in Business Objects and not the last step (which is usually being done). Always try to follow best practices while planning/implementing Business Objects reports/universe/servers.
There are many ways by which you can improve the performance of Business Objects. These are:
- Improving performance of Universe
- Improving Performance of Reports
- Improving Performance of Business Objects servers
- Improving Performance of Database
1. Improving performance of Universe
- Reduce the use of unnecessary joins- Unnecessary joins will degrade the performance as it increases the execution time of query. Explore the universe properly and take out unnecessary joins.
- Use @Aggregate_Aware- @Aggregate_Aware is a function used in BO universe, by which universe can use aggregate tables in a database. It drastically reduces the query execution time and in turn enhances the performance.
- Use shortcut joins- Shortcut joins provides the shorter path between 2 tables and thus improves the query efficiency.
- Set the proper value of array fetch size in universe connection- Array fetch size is the number of records fetched by a universe from database in each fetch. It should neither be too high and nor be too low. It depends on the network speed and database which you use. Sometimes its good to play with this number and verify the performance.
- All measure objects should use SQL aggregate function- Every Measure Objects should use SQL Aggregation function so that while querying there will be less number of dataset retuned in report. Without this user has to handle many rows in the report side.
- Use Row and Time limit in Universe Parameter- Use the universe parameter “Limit size of result set to” and “Limit execution time to” efficiently and according to your need, because this feature can increase or decrease the universe performance based on how you use it.
- Use Universe Conditions instead of report filters- the reason is very obvious, as Universe conditions will restrict the data at database itself but if you use report filters data will fetched from database and then it will be restricted at report side which degrades the performance.
2. Report Level:
· Reduce the number of complex formulas/local report variables/complex filters- try to use complex formulas/variables/filters in universe side
· Try to use variables instead of formulas.
· Breaks/Crosstab Reports/Charts will degrade the performance a bit in reports-Use it if its very much required but do not use it frequently.
· Get benefits from prompts- specially the use date prompts if possible.
· Use drills- It makes the report interactive for users and improve the performance as well.
· Remove auto height/width if possible.
· Use refresh on demand instead of refresh on open.
· If you have a very large reports do not use refresh on demand in infoview-instead 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.
3. Database Level:
· Use Indexes
· Partitioning of the Tables in the Database
4. Server Level:
· Turn off the tracing if not required, it uses lots of space.
· Move the repository (both database and file store) to a different server
· Try to use server group
I have mentioned the performance tuning in brief here, it will serve as a checklist for performance tuning of business objects environment at your end. I will be writing details of some critical points mentioned here soon in my next post.
