Webi Performance Tuning

Return to site

Webi Performance Tuning

WIKI - Tips for Fine Tuning Performance for the Webi Applet. KBA - 1904873 - Web Intelligence Rich Internet Applet loads slower after installing Java 7 Update 25 (JRE 7u25+) and above TIP 1.4 - Make sure JRE Client Side Caching is working. Transcript - It's Andre from the High Performance Academy, welcome to today's webinar. In today's webinar we're going to be looking at some strategies for tuning engines running dual variable valve timing systems so, this is where we're running both variable intake and exhaust cams. Partitioning not only improves query performance,but also enhances load performance. For this reason,BW allows us to partition PSA tables using a special transaction RSCUSTV6. After running this transaction,we obtain Screen,in which we specify the number of records after which a new partition will be created. 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. Below are performance tuning guidelines for webi report development. These guidelines needs to be followed with Universe performance tuning guidelines given separately on this blog to get good performance reports. Avoid using larger number of reports in same document.
Webi Performance Tuning Tools
Webi Performance Tuning Software
Webi Performance Tuning Tool
Webi Report Performance Tuning
Webi Performance Tuning Techniques
Web Performance Timing Api
uery Builder is one of the very few native administrative tools available for Business Objects XI and BI, yet it continues to remain in obscurity for most administrators and developers because of the lack of documentation and training offered for Query Builder.
Business Objects Query Builder is a quickly accessible backdoor to the BO XI and BO BI InfoStore/Repository that requires no SDK development but can return ANYTHING a single SDK query might. Business Objects engineers and consultants use Query Builder with almost every customer engagement, and so you might want to ask yourself, 'Are we using Query Builder, a resource freely available to all BO administrators, to its fullest potential in our BusinessObjects XI or BI environment?'
Who am I?My name is Julian Romeo and I know what it is like to struggle with Query Builder. I have been there. I have thrown my hands up when CMC failed on me and I was left with nothing but hope that it would come back without a complete restart. I have also witnessed public and private reports spontaneously and unexplainably throw errors and even become completely unusable. During those hopeless moments I wished someone had told me how QueryBuilder could help as I want to tell you. I have worked intimately with the Business Objects reporting platform for the past 9 years. For the last 7 years I have struggled in my quest to realize BO Query Builder's true potential. Because I've found my struggles so frustrating and my learned insights of Business Objects Query Builder so valuable, I decided that I should find a way to share my acquired knowledge with others and prevent them from facing the same frustrations.
Why Should You Use BO Query Builder?Engineers and consultants working for SAP Business Objects use Query Builder quite often. Acdsee ultimate 10 keygen core. It can provide the most pure, accurate, and rapid information about your BO repository; more so than any other BusinessObjects tool. BO Query Builder should not be ignored or overlooked by administrators and technically-oriented developers. On the surface Query Builder is a simple GUI tool which enables the user to run queries against the Business Objects repository (also known as the CMS InfoStore). Nevertheless, upon deeper analysis one will discover that Query Builder has the ability to provide invaluable insights beyond those available through InfoView, BI Launchpad, and the Central Management Console (CMC). In fact, Query Builder allows a knowledgeable user (such as one that studies our guide) to peer into the mysterious under-workings of the BOXI repository object meta data.
'The Query Builder Guide is a must have for every BusinessObjects administrator if it is about ‘know your system'. I use the guide in nearly every customer project with BO Enterprise as it unlocks the power of a mostly unknown and hidden feature.'
Raphael Branger
Senior BI Solution Architect at IT-Logix

Introduction to Query Builder for New Adopters
Novices to Query Builder will be steadily introduced to the namesake of Query Builder (the GUI query building tool) and gradually transitioned to the more advanced QueryBuilder topics
In-depth Syntax Information
Students of the guide will be introduced into the real strength of Query Builder, running custom Query Builder SQL statements. No prior knowledge of SQL is required, but expect to step away from the guide with definite SQL knowledge. Syntax, tables, operators, and functions (yes there are some) will be discussed in detail.
Best Practices, Performance Tuning, an Abundance of Samples
The Business Objects Query Builder guide will specify the best methods of utilizing BO Query Builder and help you to tune your more sluggish queries to the highest level of performance. Sample queries will be provided to get you started immediately and provide inspiration for your own custom creations that will meet your requirements.
Applicable to Business Objects versions XI R2, XI 3.0, XI 3.1, and BI 4.0!
Query Builder is based on the BOE SDK. Both Query Builder and the SDK in their BO XI 3.1 and BI 4.0 versions support all of the older methods and syntax used in BO XI R2. In fact, syntax that was labeled as deprecated in XI R2 continues to function even in XI 3.1 and BI 4.0. It is certain that XI 3.x brought some new functionalities, and this guide will distinguish these, but rest assured most of what you can do in Query Builder is applicable to all BO XI and BO BI releases. Including powerful Relationship Queries which are covered thoroughly in the guide!
Making Anyone Proficient in BusinessObjects Query BuilderWebi Performance Tuning ToolsOur guide is written specifically to make anyone proficient in utilizing Query Builder. It walks the beginner through the ins and outs of using the GUI to 'build queries' and then guides and encourages them to take the next logical step into generating their own custom Query Builder queries. The guide completely removes the mystery and confusion surrounding the different logical tables needed to retrieve every kind of object. Our guide details the limits of BO Query Builder and its divergences from standard SQL, from operators to data types. In doing so, students of the guide learn how to quickly build syntactically correct BusinessObjects Query Builder queries from scratch.
Uncover the Mysteries of BusinessObjects BI 4.0, XI 3.1, and XI R2 Metadata RepositoriesThe 'Business Objects Query Builder Guide' uncovers little known functions and syntax that open up new possibilities for BusinessObjects Administrators. This knowledge can lead to new Query Builder outputs that can drastically impact daily operations of the platform and strategic administrative decisions. The guide will show its readers how Query Builder can quickly detect problems in the binding between reports and universes.
Improve Query Builder Query PerformanceDetailing how to optimize Query Builder queries is just another of the many valuable lessons contained in the 'Business Objects Query Builder Guide'. Readers will discover the little-known facts regarding which properties are indexed. With this knowledge they may be able make run-away queries execute in seconds. The guide will also educate its students on the importance of selection criteria order on query performance. With this knowledge deeper and more valuable querying possibilities will be at your fingertips, literally.
'Over the past 6 months we've developed a BO Metadata Repository that uses Query Builder and the SDK to extract the metadata. We are now able to create Webi reports against this new Repository to easily list out our reports, universes, users, groups, their relationships to each other, etc. We also created a Webi report that will compare multiple universes and identify the differences.
Without this guide it would have taken at least twice as long to develop the BO Metadata Repository. Lrp sphere manual. This guide has been invaluable, only cost $27.00 and the updates are free!'
Stan Bennett, Senior Programmer Analyst
Here is just a very small sample of the in-depth information available in this wonderful BO Query Builder resource:
MAX and MIN and Much More
The ALL operator can be quite powerful and a bit confusing. It allows you to get as close to a sub-query, or MAX and MIN functions, as is possible with Query Builder.
The ALL operator should only be used between two properties or fields that are of the same data type. ALL can be used to identify the maximum or minimum value when used in conjunction with two of the same properties. When used with different fields or properties it can be used to compare property 1 to all possible values of property two.
Operator Syntax
= ALL
!= ALL
> ALL
>= ALL
< ALL
<= ALL

Examples
SELECT * FROM CI_InfoObjects WHERE si_children >= ALL si_children
(returns all info objects with the maximum number of children in the repository)

SELECT * FROM CI_InfoObjects WHERE si_id != ALL si_parentid
(finds all info objects that are not parents of other objects)
IMPORTANT NOTE:
Customers of the Business Objects Query Builder Guide will receive access to the guide in Portable Document Format format (PDF); however, more importantly they will be granted free access to any future versions of the guide. Now that's future-proof!!!

How Can You Obtain 'The Most Complete Business Objects Query Builder Guide Ever Written'?
We have called upon countless hours of real-life Query Builder experience and many more hours in verification research and documenting efforts in order to produce this comprehensive Business Objects XI Query Builder Guide. We are confident that beginners and veterans alike will find it insightful and quite helpful, especially for the reasonable price of $27.00 USD. In fact, we are confident that students of the guide who leverage its teachings will find that it pays for itself in two weeks, or even much less, in the form of increased efficiency, capability, and new-found knowledge of your BO system. Of course, we do provide a 100% Money Back Guarantee to the very unlikely customer who might not find this to be the case.
'The Query Builder's Guide is a fantastic resource, and well worth the price just in terms of time saved and insight gained. In addition to being the help file that should have been included originally, it is also full of very useful pre-built queries that give an excellent overview of the capabilities of the tool. I have incorporated a couple of these queries directly into my daily gathering of metrics on the system.'
Marshall Smith, Business Objects Administrator

(current version available is 1.3.0)


Webi Performance Tuning Software
Questions?
Contact:
Julian Romeo
julian@businessobjectstips.com

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 Toolc. 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.
Webi Report Performance TuningResolution: 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.
Webi Performance Tuning Techniques

Examples
SELECT * FROM CI_InfoObjects WHERE si_children >= ALL si_children
(returns all info objects with the maximum number of children in the repository)

SELECT * FROM CI_InfoObjects WHERE si_id != ALL si_parentid
(finds all info objects that are not parents of other objects)
IMPORTANT NOTE:
Customers of the Business Objects Query Builder Guide will receive access to the guide in Portable Document Format format (PDF); however, more importantly they will be granted free access to any future versions of the guide. Now that's future-proof!!!

How Can You Obtain 'The Most Complete Business Objects Query Builder Guide Ever Written'?
We have called upon countless hours of real-life Query Builder experience and many more hours in verification research and documenting efforts in order to produce this comprehensive Business Objects XI Query Builder Guide. We are confident that beginners and veterans alike will find it insightful and quite helpful, especially for the reasonable price of $27.00 USD. In fact, we are confident that students of the guide who leverage its teachings will find that it pays for itself in two weeks, or even much less, in the form of increased efficiency, capability, and new-found knowledge of your BO system. Of course, we do provide a 100% Money Back Guarantee to the very unlikely customer who might not find this to be the case.
'The Query Builder's Guide is a fantastic resource, and well worth the price just in terms of time saved and insight gained. In addition to being the help file that should have been included originally, it is also full of very useful pre-built queries that give an excellent overview of the capabilities of the tool. I have incorporated a couple of these queries directly into my daily gathering of metrics on the system.'
Marshall Smith, Business Objects Administrator
(current version available is 1.3.0)


Webi Performance Tuning Software
Questions?
Contact:
Julian Romeo
julian@businessobjectstips.com

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 Toolc. 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.
Webi Report Performance TuningResolution: 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.
Webi Performance Tuning Techniques 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. 
Web Performance Timing Api

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