1892290-Tips for Fine Tuning Performance for the Webi Rich Internet Applet (RIA) Symptom. The load time of the Rich Internet Applet (RIA) varies between client machines. This is due to a number of variables that come in to play: What version of Java the client has installed. This paper provides a step-by-step description including screenshots to evaluate how SAP BW interprets SAP BO Web Intelligence reports using the BICS connectivity. This functionality, also known as tracing or performance analysis on BW & BO, can if used properly increase the performance and user convenience of SAP BusinessObjects Web Intelligence and other reporting tools using BICS tremendously. เพิ่มความเร็วและประสิทธิภาพสูงสุดให้กับ SAP ด้วย Performance Tuning (Faster. These Removing the conditions, exceptions and Text variables can be applied at Webi report. Attributes in the free characteristics area instead of Rows and Columns: Keep the required characteristics in the free characters area rather than in rows and columns. This will improve the query performance and in turn the webi report performance. SAP BOBJ Best Practice Tuning WEB INTELLIGENCE SAP. Language: english. File: PDF, 1.12 MB. Send-to-Kindle or Email. Please login to your account first; Need help? Please read our short guide how to send a book to Kindle. You may be interested in Powered.
First, Let us look into the question what are Performance Issues in a report?
1) Reports are running extremely slow and getting timed out
3) Performance of the BO Report displaying aggregated or summarized data is extremely slow
4) BO report is taking more processing time and still displaying partial data
5) A list of values request is taking more than fifteen minutes to return
Second, Let us look into the options of how to we tune the performance of the reports-
BO reports can be optimized at 4 levels:
b) Report level
d) Server level
-> Allocate weight to each table
-> Use aggreagte functions
-> Minimize usage of the derived tables
Modify Array Fetch parameter: The Array fetch parameter sets the maximum number of rows that are permitted in a FETCH proedure. For example, of the Array Fetch size is 20, and total rows are 100, then five fetches will be executed to retrieve the data, which will consume more time in comparison with one fetch.
Resolution:If network allows sending large arrays, then set Array fetch parameter to new larger value. This speed up the FETCH procedure, and reduce query processing time.
Allocating table weights: Table weight is a measure of how many rows there are in a table. Lighter tables have less rows than heavier tables. By default BusinessObjects sorts the tables from the lighter to the heavier tables. The order in which tables are sorted at the database level depends on your database. For example, Sybase uses the same order as BusinessObjects, but Oracle uses the opposite order. The SQL will be optimized for most databases, but not for Oracle where the smallest table is put first in the sort order. So, if you are using an Oracle database, you can optimize the SQL by reversing the order that BusinessObjects sorts the tables. To do this you must change a parameter in the relevant PRM file of the database.
Resolution:Business Objects settings, the ORACLE PRM file must be modified as below:
• Browse to directory Business ObjectsBusinessObjects Enterprise
• Open ORACLE.PRM file, change the REVERSE_TABLE_WEIGHT
Using Shortcut joins: Numbers of tables in join are more, even when selected objects are less. Even when no object of related table is selected, then also that table is appearing in the join condition. For e.g., If A_id object from A table of C table is selected with B table in between, then BO generated SQL shows that intermediate table ‘B’ table was present in ‘From’ clause.
Resolution: Shortcut joins allow users to skip intermediate tables and allow alternative paths between tables. Use of shortcut join reduces the number of tables used in query to improve SQL performance. Results in query performance improving from 1.5 minute to 30 seconds!!
Use aggregate functions: Data is aggregated on the subject of analysis (user selected criteria) at report level. This takes more processing time, as data from database is loaded in temporary
memory and then aggregated or processed to display.
Resolution: Use aggregate functions (e.g., sum, count, min, max) in measure objects at universe
level. Aggregate functions will aggregate the data at database level rather than at report level which will save on processing time at report level and also reduce the number of rows returned back to report.
Creating and using aggregate tables: Aggregate data are obtained by scanning and summarizing all of the records in the fact table at real-time which consumes more time.
Resolution: Aggregate tables contain pre-calculated aggregated data. Using aggregate tables
instead of detail tables enhances the performance of SQL transactions and speeds up query execution. Aggregate_Awareness function has ability to dynamically re-write SQL to the level of granularity needed to answer a business question. Aggregate tables allow for faster querying speed and increases query performance manifolds!!
Minimize usage of derived tables: Since derived tables are evaluated and executed at runtime, SQL tuning is not possible.
Resolution: Minimize the usage of derived tabled and replace them with tables or materialized
view. SQL tuning techniques such as creating index can be applied on tables or materialized views which will improve performance of BO reports.
-> List of Values (LOV's)
-> Complex Calcualtion in ETL
Opt for Refresh At- Will over Refresh-On-Open: Refresh-on-open reports refresh new data each time it is opened. Connection with database is established each time report is refreshed which in turn slows the report performance.
Resolution: If report is based on snapshot data and static, it is better to publish report without
refresh-on-open property. Users will thus view the same instance of report without establishing database connection, which will reduce the response time of BO report.
List of Values (LOV's): When we create LOV object, distinct values are selected into it. DISTINCT forces an internal sort/compare on the table. Selecting a distinct list on large table is not optimal e.g., selecting a distinct list of custom_store against t_curr_tran_daily table is not
Resolution:
a. Re-map the object list of values to smaller look up tables.
b. If there are no smaller lookup tables, then create external file as a source to LOV. This file needs to be exported along with universe and be available to all users, which is additional overhead. Usage of external file replaces the need of lookup table and delivers high performance and weighs down the overhead cost
Webi Performance Tuning Tools
c. Avoid creating LOV on dates and measures. Disassociate LOV from all such objects which are not display as prompts.
Universe Condition Objects: The entire data from database is fetched (<=maximum rows setting) and the filters are applied at the report level. As data is not restricted at the database or universe level, the reports takes more time to execute.
When handling huge data, one of the following steps can be taken to limit data:
1. Use prompts to restrict data selection at universe level. Preferably use time period prompts in reports.
2. Replace report filters with Universe condition objects, if possible. Usage of conditional objects will limit rows returned at database level.
Complex Calculations: The data from database is fetched and then calculations are applied to that data. As calculations are performed at universe or report level on huge data, reports takes more time to execute.
Resolution: When dealing with huge data warehouses perform complex calculations at ETL level.
Thus Business Objects saves time on calculations and deliver high performance.
Minimize usage of Report variables/formulas: If the report is pulling tons of data, doing loads of joins, making lot of clever calculations, 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.
When dealing with big reports, minimize usage of report variables/formulas and try to place them at universe to deliver high performance reports.
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.
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
-> Report Server/Job Server closer to database server
Over the years when ever we discuss about Webi Reports or matter of fact any reports, Performance tuning has always been an important topic, so lets discuss how can we improve ‘Performance’ of a webi reports !! here are some ways ..
1. List of Values (LOV’s): When we create LOV object, distinct values are selected into it. DISTINCT always forces an internal sort on the table. Selecting a distinct list on small table is always optimal. If there are no smaller lookup tables, then create external file as a source to LOV (Of course this file needs to be exported along with universe and should be made available to all users). Try to avoid creating LOV on dates and measures.
2. Universe Condition Objects: Always try to restrict data applying filters at the data base level rather than at the Report level. Since data is restricted at the database or universe level, the report takes less time to execute.
3. Avoid Complex Calculations on Reports: For any calculation applied at the report level, first data is fetched from the batabase and then calculations are applied to the fetched data. If the report is dealing with huge column of data, then performing calcs on the report will degrade its performance. When dealing with huge data warehouses perform complex calculations at ETL level itself.
4. Avoid Grouping variables: Grouping variable values to create a new variable is a slow process. Create a table in the db or an object in the universe to perform this task.
5. Do not pre-sort: Do not add sorts in the Query Panel. Sorting adds time to data retrieval. Business Objects will usually re-sort the data anyway.
6. Minimize usage of Report variables/formulas: If the report is pulling tons of data, doing loads of joins, making lot of cals, 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.
Webi Performance Tuning Techniques
Deleting trailing blanks, Opting for Refresh At- Will over Refresh-On-Open can also help resolving performance problems in Webi reports.