Business Intelligence: Performance Tuning

Performance is such a word that we use in our every day life. We all need to optimize our work and enhance the performance day to day to be ahead and updated in life.

Similarly in Business Objects we need to enhance the performance or can say that to optimize things for fast running of the reports for the business requirements.

In Business Objects, the performance enhancements can be done in 4 basic levels which i would be sharing with you. Since after the development work been done, and there are cases of performance issue in the large and complex reports that we create for the end users. Basically, the best practice is to check for the performance in the report level then further move to universe level and then further to database and server level. As we basically focus on the reporting side, so it is the best practice to do as much as possible optimization been done over the reports developed in Web Intelligence so as to not touch the universe and the complex database.

Reporting Level: Web Intelligence (WEBI).

To enhance the performance of the webi reports we need to see things such as

1. Reduce the JAR files loading. Since in BI4.0 split the single JAR file into 60+ individuals JARs for easy development and updating. Previously in BO versions like in 3.1 version contain single JAR file (thin cadenza.jar). It is best practice to upgrade to BI 4.1 SP3+ for smooth running of WEBI reports.

 *JAR file is 44MB and it takes time to get loaded.

2. We must avoid using large reports in a document. Best practice is to use 10 reports per document or maximum up to 20 reports. Apart from this we must check the number of rows per document i.e. it must not cross 50,000 rows or we can say 2500 rows per reports.
3. The smaller the report, the faster it runs. So to avoid delay during run time and analysis of reports we must see that specific business needs are fulfilled and rest are been removed. It also enhance the refresh time along with improve the performance while doing modification in the reports.
4. Sometime the reports contains charts along with tables which make the reports large and refresh issue comes. So in this cases we can go for using of the report linking feature of WEBI. We can create one report with tables and other with charts and further link them with one another.

* we can link reports together using open document linking and a combination of prompts and filters apart from Hyperlink wizard that is available in HTML interface.
Scenario: (Linking)
Report 1 contains the summary of the sales total for all the branches of company across the nation and it is then scheduled to run every night which takes around 15 minutes to get scheduled. Users can view the latest instance of report 1 which takes few seconds to load and users can drill down into sales total of each branch which will launch a report 2 that will display only the data of the particular branch drilled on.

5. we can limit the number of data provider and it is best practice to use less than 15 data provider per document.
6. Cache enhance the load time of documents, so we must check it that it is not been disabled.
7. While we enable the auto width and height in format cell option of webi, it makes the full document to be calculated during navigation time and finally make it slow and decrease the performance. So best practice is to avoid it as much as possible.

Ex – suppose we have 10 reports in a document and we wish to jump from page 2 to             page 10 then we can see that it is slow to navigate to page 10.

8. Many of the webi reports contain charts with data points. If much data points are been used then it effect the APS i.e. adaptive processing server which makes the report run slow.
9. Try to avoid nested sections in a report as it degrade the performance of the reports. When we open the format section window of webi and enable the button showing hide section when the following are empty. We must not enable it.
10. We must use the test query drill for drill down reports. The option called use query drill can be found in properties of the report. Enable the use query drill which would utilize the database instead of local data and thus reduce the amount of data stored locally for drill session.
11. instead of scope of analysis we can also go for report linking since while we apply scope of analysis it retrieve extra data from the database which is stored in cube and used for drill down purpose.
12. While we use formula like for each and for all in reports, we can use In instead of for each and for all. Similarly, instead of using WHERE operator we can go for using of the if then else which can faster the performance.

Universe Level: Semantic Layer

1. While creating universe we must try to use the required objects to build up the universe design to meet the business requirement.
2. Keep the query simple as possible and we must avoid creating often more complex query i.e. building up the query that only contain the required objects in the documents.
3. Checking of array fetch size optimization is the way to enhance the performance of universe design as array fetch size sets the maximum number of rows with each fetch from the database.
4. While applying the filters, we must prefer to go more with query filters rather than report filters since, query filter modify the SQL query to restrict the data fetched and further displayed while report filters simply modify the displayed data.

Ex – a sales report with year is running and the user wish to see only the November data. So in this case, by using of query filter the WHERE clause we get only the data of November month rather than other months. But while using the report filter we can get the same data of November month but rest of the month data is there hidden in the cube.

5. In the BI 4.0 onward version, query stripping is the new component that is been integrated which enhance the performance by, eliminating the un used objects the query. The option for query stripping has to be enabled in the report, query and in universe level. While using BICS connection stripping is by default but in other connection we have to enable it.

• While using relational database, the parameters that has to bet set for query stripping are allowing query stripping option selected in business layer. Enabling the query stripping option selected in query properties of the WEBI document. And enabling query stripping option select in document properties of WEBI document.
• Use of enhanced query stripping parameter will only optimize the SELECT and GROUP BY clause and will not modify the joins and other clauses.

6. We must go for only merge dimension that are required in the universe.
7. While we generates WEBI reports, there are many servers which are involved such as adaptive processing server, connection server, web intelligence server. So we must check these servers going into CMC to optimize it so that there is hassle free running of webi reports.

DATABASE LEVEL OPTIMIZATION - 

Examine the execution plan of SQL: Determine the execution plan of BO generated SQL in target database. EXPLAIN PLAN is a handy tool for estimating resource requirements in advance. It displays execution plans chosen by Oracle optimizer without executing it and gives an insight on how to make improvements at database level.

SERVER LEVEL OPTIMIZATION - 

If the performance of system deteriorates when reports are accessed by larger number

of users over web, then fix the problem at fourth level i.e., server level (Level 4).

-> Scalable System

-> Event Based Scheduling

-> Report Server/Job Server closer to database server

-> Maximum Allowed Size of Cache