SAP BusinessObjects Increasing Stability by Setting Limits on Max. Retrievable Cells from SAP BW into Web Intelligence using BICS

(1)

SAP BusinessObjects Increasing

Stability by Setting Limits on Max.

Retrievable Cells from SAP BW into

Web Intelligence using BICS

Applies to:

SAP BusinessObjects BI 4.0 Web Intelligence from patch 16, BICS Connection, SAP BW. .

Summary

This paper documents a step by step description incl. screenshots of how to set the maximum retrievable cells from BW into SAP BO Web Intelligence. This functionality, also known as safety belt, used properly can increase the stability of the SAP BusinessObjects Enterprise system. Without the Safety Belt end users of Web Intelligence can accidently create an enormous query that will consume all memory in the

BusinessObjects Enterprise service called Adaptive Processing Server.

Author: Kurt Holst, Senior SAP BusinessObjects Consultant Company: SAP

Created on: 16 September 2012

Author Bio

(2)

1. Background and the need for a safety belt (max. retrievable cells).

The cause of the problems with SAP BusinessObjects Enterprise has in some cases been identified as related to the ad hoc Web Intelligence reports (runaway queries) using the BICS connection to BW. This has been documented using the java log file shows that the Web Intelligence Processing Server (running under the umbrella of the Adaptive Processing Server) crashed due to an out of memory issue. The obvious

question here is whether this can be linked to limitations or shortages in the SAP BusinessObjects Enterprise software? This issue has been addressed in SAP BO 4.0 Patch 16 with the introduction of a new parameter that Web Intelligence abides by – the safety belt or max. retrievable cells. This implies that you can now set the number of cells that are returned in a query are limited to the number that you specify. This limits the number of cells returned, but does not restrict the BW from processing all cells in the query.

The safety belt limits the number of cells being sent to Web Intelligence. But BW has actually performed the full query and calculated the number cells to be sending back to Web Intelligence – just before BW would normally start to send cells back to Web Intelligence.

With the use of the safety belt it should prevent the out of memory issues caused by runaway queries generated with Web Intelligence on a BICS connection to BW.

Definition:

“Safety belt" is a size restriction for result sets and protects against large results quantities that may be requested by the end user by mistake. Large result sets could reduce the system performance and could cause high memory consumption.

(4)

2. Steps required for setting safety belt (max. retrievable cells)

The Safety Belt feature restricts as described above the number of cells that SAP BO Web Intelligence can retrieve from the BW system using BICS connectivity. The Safety Belt feature is activated and configured directly on the SAP BW system. Nothing is (as of SAP BO 4 SP02 Patch 19, FP3 or SP04) customizable on the BusinessObjects side.

Open your SAP GUI:

From the SAP GUI go to transaction code SE38 – ABAP editor. This would normally be an area where you should ask your SAP Basis / administrator colleagues to assist performing these configuration changes.

(5)

Specify OBJECT = BICS_DA_RESULT_SET_LIMIT_MAX

VALUE = 500000 (cells) and click insert

(6)

3. Check that the Safety Belt parameters are set.

From the SAP GUI go to transaction SE16 on the BW system.

Type table name “RSADMIN”:

Restrict the objects names or just show all objects in the RSADMIN table.

(7)

4. Testing the Safety Belt using Web Intelligence

Create an OLAP connection using SAP BusinessObjects Central Management Console (CMC) or SAP Information Design Tool (IDT). The Central Management Console (CMC) is a web-based tool to perform regular administrative tasks, including user, content, and server management.

It also allows you to organize, and set security levels for all of your SAP BusinessObjects User Management System content. Because the CMC is a web-based application, you can perform all of these administrative tasks through a web browser on any machine that can connect to the server. All users can log on to the CMC to change their user preference settings. Only members of the Administrators group can change

management settings, unless explicitly granted the rights to do so.

(8)

From the BO frontend tools create a new Web Intelligence report using connection just created Bex).

Create a new Web Intelligence report based on the newly created BICS connection.

(9)

Change the BICS limit to something that will trigger the safety belt:

Confirmation:

As shown below the safety belt “protected” the SAP BO system from getting more than 10 cells back into Web Intelligence. This will in effect secure a more stable SAP BO environment without maverick queries causing the Adaptive Processing Server to run out of

(10)

5. How the Safety Belt affect other tools?

How is Analysis for OLAP affected by the Safety Belt?

Just to make sure that Analysis for OLAP isn’t limited the 10 cells safety belt below is a screenshot with a lot of cells (>10) in a Analysis for OLAP report.

As can be seen above Analysis for OLAP is not affected by the safety belt.

(11)

6. How does the Safety Belt effects Business Warehouse?

What are the effects on the Business Warehouse application with the Safety belt configured?

In order for BW to determine if the BICS limit has been reached the full query generated by Web Intelligence is actually performed as shown on the illustration below.

Technical details:

The technical background for determining if BW is actually performing the full query generated by Web Intelligence can be clearly seen on this table below. The screenshots from BW tracing (details on how to perform a trace in BW is deemed outside the scope of this article) below show the RFC for

BICS_PROV_GET_RESULT_SET which is the actual cells being transmitted from BW to the

BusinessObjects Enterprise system (Adaptive Processing Server, Dimension Semantic Layer Bridge).

Example with Web Intelligence query executed WITHOUT Safety Belt limits.

A trace in the BW system reveals that 2000 Cube records are retrieved by the BW OLAP from the database:

(12)

The BW trace reveals that the data retrieved by the BW OLAP from the database is still the same:

(13)

7. Appendix: This functionality can also be applied to BEx Analyzer.

You can set only a default value centrally and then change it locally in BEx Analyzer.

But if you set max centrally, then there is no chance to change it individually for any query view. For a query view -> you can say use “max” or “default” or “custom-defined”.

Max is set in RSADMIN BICS_DA_RESULT_SET_LIMIT_MAX Default -> BICS_DA_RESULT_SET_LIMIT_DEF

You need to set at least one of these two parameters:

ANALYZER_LIMIT_DEF: The default number of maximum cells if no further setting is specified. The default number can be overwritten locally in BEx Analyzer for each data provider.

ANALYZER_LIMIT_MAX: the absolute maximum number of cells (see below). The maximum number cannot be overwritten in BEx Analyzer. Instead of a table with values the message "Result set is too large; data retrieval restricted by configuration" appears.

This message indicates that the "Safety belt" is active. "Safety belt" is a size restriction for result sets and protects against large results quantities that may be requested by the end user by mistake. Large result sets could reduce the system performance and could cause high memory consumption.

Solution

The "safety belt" can be activated for a specific Query View or globally using a parameter in the customizing table RSADMIN.

Query View

The following steps describe how to change the "safety belt" for Query Views:

1. Use the context menu Properties / Data Provider in a BEx Web Application to maintain the "safety belt" for a Query View.

2. Choose the register "Size Restriction for Result Sets".

3. Choose an entry from the dropdown box to specify the maximum number of cells for the result set. The following values are available:

- Maximum Number - Default Number

- Custom-Defined Number

Behind "Maximum Number" and "Default Number" you can find the current numbers defined in the customizing table RSADMIN (see below).

4. Save the Query View and use it in another Web Template.

You want to specify a safety belt for large result sets in BEx Analyzer as described in note 1127156 for Web Java Runtime. This may be to reduce the waiting time and to overcome the .NET limitation described in note 1040454 that will lead to an out of memory error in BEx Analyzer.

(14)

o ANALYZER_LIMIT_MAX: the absolute maximum number of cells (see below). The maximum number cannot be overwritten in BEx Analyzer.

If only one entry is specified both parameters will have that value.

When the safety belt is activated globally you can also overwrite this setting locally in BEx Analyzer. In the properties of a data provider

(Create/Change Data Provider) you can set the maximum number of cells to the default number (i.e.

ANALYZER_LIMIT_DEF in RSADMIN), the maximum number (i.e. ANALYZER_LIMIT_MAX in RSADMIN) or a custom defined value.

(15)

8. Technical background:

Customizing table RSADMIN "Maximum Number" (BICS_DA_RESULT_SET_LIMIT_MAX) and "Default Number" (BICS_DA_RESULT_SET_LIMIT_DEF) are defined in the customizing table RSADMIN. "Maximum Number" (BICS_DA_RESULT_SET_LIMIT_MAX) defines the maximum number of cells that a user can enter manually for a specific Query View (see above). "Default Number"

(BICS_DA_RESULT_SET_LIMIT_DEF) contains the number of cells that is used for all Queries (and Query Views) without any custom-defined number.

Execute the program SAP_RSADMIN_MAINTAIN (in transaction SE38) and create the parameter OBJECT = BICS_DA_RESULT_SET_LIMIT_DEF (or BICS_DA_RESULT_SET_LIMIT_MAX) with the value VALUE = <maximum number of cells in the result set>. When the requested result set of a user is larger than is defined in the parameter, the user receives a relevant message and the processing is terminated. The recommended setting is 500,000 cells (see under "Analysis Web item").

Definition Number of Cells: The number of cells is referring to the number of data cells. The number of data cells is different to the number of table cells, i.e. cells that are visible in the HTML table.

The data cells are the cells with the numbers. This is something that is independent on how the data is rendered in the end and therefore the measure for checking the limit.

The number of table cells is for sure higher than the data cells, as for the table cells the column and row headers are needed as well (characteristic members texts and keys, attributes, ....).

The maximum number of cells is checked when the data cells are transferred from ABAP to Java (via RFC). Before this data transfer starts, the maximum number of cells is checked. You could see the number of transferred cells with the help of the BI Query Runtime Statistics. The counter of the event ID 3200 contains the number of cells.

(16)

9. Related Content

The following documents are referenced in this document:

DOCUMENT NAME LOCATION ISSUE DATE

SAP Note 1127156

Service.sap.com

SAP Note 1040454

Service.sap.com

(17)

Copyright

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.

Oracle is a registered trademark of Oracle Corporation.

UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.

Java is a registered trademark of Oracle Corporation.

JavaScript is a registered trademark of Oracle Corporation, used under license for technology invented and implemented by Netscape. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.

All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.

SAP BusinessObjects Increasing Stability by Setting Limits on Max. Retrievable Cells from SAP BW into Web Intelligence using BICS