Performance Optimization for Webi Report based on BW BEx Queries

[Former Member]

Performance Optimization for Webi Report based on BW BEx Queries

Applies to:

SAP BI 3.x/7.x and BO WEBi XI R3.1. For more information, visit the Business Intelligence Home Page.

Summary

This document talks about Performance Optimization for Webi Report based on BW BEx Queries.

Author: Pulla Reddy Nemali     

Company: Cognizant Technology Solutions

Created on: 08 June 2012

Author Bio

The author has worked in various support, migration and development projects in BusinessObjects and SAP BW for over 5 years. He is currently working with Cognizant.

Introduction

This document talks about Performance Optimization for Webi Report. To achieve the performance optimization settings are to be done at BEx and BOBJ side.

BEx Query

1. Restricted and Calculated Key Figures:

A general recommendation on the need for calculations and formulas being leveraged as custom objects in the data set is to leverage the capabilities of the Restricted Key Figures and Calculated Key Figures as much as possible. The main reasons for this recommendation Is:

Restricted Key Figures and Calculated Key Figures can be built as part of the underlying BI query, and in that way they will be processed from the BW backend. This allows you to ensure that these elements are not causing any unnecessary overhead for the overall performance.

Objects like a calculated key figure or a restricted key figure or a filtered measure should be created as part of the BW query. You should always push restrictions and calculations as far down the stack as possible.

2.     BW Specific Tuning and Configuration:

Set Specific RSRT Property

Check the "Use Selection of Structure Elements" Query Property in RSRT as seen here:

          

Checking this property helps improve performance in situations where there are many Restricted Key Figures and other calculations in the Query Definition checking this ensures the structure elements are sent to the database for processing.

3.     Filters:

The general rule is that any prompt or filter should be defined in the BW query already not the Web Intelligence query panel. This helps improving the webi report performance.

4.     Conditions Exceptions and Text Variables:

Removing the conditions, exceptions and Text variables from BEx query in the migration from BEx to BOBJ. Because conditions, exceptions and Text variables are not passed to BOBJ. These Removing the conditions, exceptions and Text variables can be applied at Webi report.

5.     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.

6.     Do not use the exclude function in BEx query:

This includes document and universe design best practices which are performance and scalability related. BEx query level. The common best practice is to push as much as possible to the BW query. In some cases, where clauses created at the universe layer can be sent down to BW and sometimes not. When they aren't, restriction of data is occurring more data than necessary is being moved across the wire to the SAPBO deployment.

Objects like a calculated key figure or a restricted key figure or a filtered measure should be created as part of the BW query. You should always push restrictions and calculations as far down the stack as possible.

WEBi Report:

1.     Use Query Drill Option:

If the hierarchies are defined for the characteristics at BEx level then we need to create manual hierarchy at universe level to have drill functionality available at webi report level.

Drill can be used within Web Intelligence report as long as we have a hierarchy defined. In order to have only the data for the current drill context fetched, rather than the entire dataset being fetched up-front, ensure that "Use query drill" is checked in the Web Intelligence document properties.

Custom Hierarchies at Universe Level:

2.     Use Inclusive Member Filters Rather than Exclusive Ones:

Avoid using the operators Not Equal To, Not In, Not between, etc. in the filter pane of the Web Intelligence query panel. Due to the need to resolve filters to member-sets, these types of filters giving performance issue. Replace these types of filters with the inclusive equivalent.

3.     Adding Keys to Objects Used in an LOV for Filtering

The sections on filtering, it is important that objects which have a List of Values (LOV) associated with them should always have a key which points to the underlying technical name for the Characteristic values represented by the objects. By default, all Dimension objects automatically created when generating a BI Universe have this defined. In cases where customization has been done or where detail objects are used in this way, this needs to be done manually.

4.     Static Filters on BOBJ:

Defining filters in the Web Intelligence query panel rather than in the underlying BI query provides a lot of flexibility and allows a single BI query and single Universe to be reused for many Web Intelligence reports. By following a few simple guidelines, it is possible to implement good performing the queries using static Web Intelligence filters.

5.     Query Stripping:

This feature optimizes the query to fetch only data which will be dragged on to the report for user display thus increases the performance and faster display of the report for the user.

1. On the query level, enable the Query Stripping.

       

2. On the document property, enable the Query Stripping.

Note: The option is available with Service pack3.

6.     Scheduling of the BOBJ reports:

One of the first factors to consider when designing a report is whether it is necessary to have the report run on-demand or if the reporting need can be by having users access scheduled instances of the report. If it is possible to minimize the number of times a report is run against the BW system, it is giving a performance issue. So, it is recommended to use scheduling when practical. The primary benefits of scheduling rather than viewing on-demand are:

1. Vastly improved viewing response time for the user

2. Overall reduction in burden on the BW system versus having many ad-hoc queries run.

7.     Report Linking:

Create two reports in Web Intelligence (Summary and Detail) and Export both to the Central

Management Server. Since the Parameter has to be passed to the Detail report, create the detail

Report with User Prompts which is getting the parameter sent from the Summary.

The Summary report shows the Employee Number and numbers of records for each Employee number and second Detail report shows the Employee Number and Details for the particular selected employee number using Query Prompts.

This report linking is to improve the performance the webi report.

To create Hyperlink in first report, open Business Objects Enterprise Java Infoview and open the

Web Intelligence document .Select the Object that is to be linked with the second report then

RIGHT CLICK –> Hyperlink –> New

           

             Click Browse, select the second report (Detail report) and select ok.

            

Select Refresh on open option from Hyperlink properties in Create Hyperlink Window and select the        Options as in following screenshots below.

            

            Then the report will look like what is shown below, as hyperlinks for the object that is selected.

           

            Click the link from Infoview, which leads to second report.

           

Note: The above workflow can be achieved in Interactive mode in Infoview, can be set under Preferences.

Related Content

You will find the following material useful as an adjunct to this white paper or in providing additional information.

http://help.sap.com/saphelp_nw70/helpdata/EN/43/e39fd25ff502d2e10000000a1553f7/frameset.htm

https://www.sdn.sap.com/irj/sdn/bi-performance-tuning

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.