The purpose of this wiki is to explain the reason why SAP BusinessObjects Analysis, edition for Microsoft Office (AO) shows the message "Size limit of result set exceeded" in some scenarios and also to provide details about the memory consumption and what to do when working with huge reports.
The page is only for Anaylsis for Office 2.x. For Analysis for Office 1.x version, please check: https://wiki.scn.sap.com/wiki/x/EYhRFw.
Please note that Analysis for Office is a reporting tool, hence it is not suitable for mass data extraction but for data analysis.
1 - Analysis for Office is showing the message "Size limit of result set exceeded"
When the report retrieves too much data from the backend, one of the three scenarios below may occur:
- The message "Size limit of result set exceeded" is displayed in AO instead of the data, see also note 1656983.
- It takes too much time to display the data = performance issue.
- An error/exception occurs in AO, which is related to "out of memory".
2 - Default limit of 500.000 data cells
In Analysis for Office, there is a default limit of 500.000 data cells for the result set. This limit exists because of a memory limitation in .NET framework explained in section #4.
When the report retrieves too much data from the backend and ends up reaching the limit of data cells, the message "Size limit of result set exceeded" will be shown in AO instead of the expected data.
3 - Changing the default limit of the result set in Analysis for Office
SAP does not recommend to change the result set default limit of Analysis for Office.
- However, it is possible to change the default limit.
- You can define the limit of data cells on client side or in BW backend system.
- Note that when connected directly to HANA system, it is not possible to define the limit in HANA side like when connected to BW system. In this case, AO will use the value defined in client side only.
3.1 - Result size Configuration on Client using parameter ResultSetSizeLimit
- You can change result size with the DataSourceConfiguration parameter ResultSetSizeLimit.
- In AO Administrator’s Guide the parameter is described as following:
- This setting defines the maximum number of data cells that are loaded from the server for one data source.
- If a data source contains data for more cells than defined here, a message displays.
- The standard value for this setting is empty and the maximum number of cells is 500000.
- If you set the parameter to a specific number greater than or equal to 0, you define the maximum number of cells with this value.
- If you set the parameter to -1, the setting uses the values defined in the BW system.
- In AO 2.x, the settings are maintained in the file system and not in the registry like in AO 1.x versions.
- The ResultSetSizeLimit setting can be maintained in Ao_app.config (located in %PROGRAMDATA%\Sap\Cof) or Ao_user_roaming.config (located in %APPDATA%\Sap\Cof).
- When scheduling Analysis workbooks, the ResultSetSizeLimit setting is maintained in AO_BiPrecalculation.config (located in Analysis Precalculation installation folder).
- Please follow note 2083067 - How to maintain settings for Analysis Office 2.x to set ResultSetSizeLimit parameter.
- The value of the ResultSetSizeLimit setting is defined as below by default:
|<!--default: value="500000" --> |
|This is Comment Line. |
The default value for this setting is empty. It means this setting will use the default value 500000 for the maximum number of cells.
|<ResultSetSizeLimit value="400000" />||The parameter can be set to a specific number to define the maximum number of data cells with this value.|
The example is changing the ResultSetSizeLimit to 400.000 data cells.
|<ResultSetSizeLimit value="-1" />|
The parameter can be set to -1 so that the setting uses the values defined in the BW System (see section 3.2).
3.2 - Result size configuration in BW System using parameter BICS_DA_RESULT_SET_LIMIT_MAX
- If you set the ResultSetSizeLimit parameter to -1, the setting uses the values defined in the BW system.
- In BW system, the parameter is set in the RSADMIN table for object BICS_DA_RESULT_SET_LIMIT_MAX (see note 1656983).
4 - The ResultSetSizeLimit setting has been changed and now it is taking too long to display the data or there is an error/exception in Analysis for Office related to "out of memory"
- The performance issue occurs because of the amount of data cells that is being retrieved from the backend. If the report is huge and shows too much data, it will take a time to be displayed.
- The "out of memory" error occurs because there is a limit of memory that a process can allocate in a 32-bit environment. This limit is not related to Analysis for Office and it is not possible to change it.
- When a query is executed in AO, objects are created in the main memory of the Frontend for each cell of the result set. Regardless of the number of cells, AO itself allocate additional memory as a kind of baseline from the starting point on (about 100 megabytes).
- Independently of the memory capacity in the Frontend, in a 32 Bit environment, a process can allocate a maximum of approximately 2 gigabytes (GB). The .NET framework itself has an overhead of 600-800 megabytes (MB). So the maximum memory allocation of the EXCEL.EXE process is approximately 1,2 (GB) before getting an out of memory exception on the Frontend.
- Also see note 1729141 - Analysis Office: Front End Memory Consumption
5 - How to avoid the "Size limit of result set exceeded" message, the "out of memory" exception and the performance issue
- The SAP recommendation is to not change the default limitation of 500.000 data cells in Analysis for Office.
- If any of the scenarios explained on this page is occurring, the recommendation is to evaluate whether the report can be changed to display less data.
- This can be done by adding more filters and mandatory variables, for example.
- If it is really necessary to execute huge reports for data analysis and change the ResultSetSizeLimit setting, we recommend to use the 64 Bit version of MS Office and the 64 Bit version of Analysis, edition for MS Office since more memory can be allocated in this case.
- Also, see note 1703279 - General performance recommendations Analysis Office.
6 - Debugging / FAQ
6.1 - I changed the value of the ResultSetSizeLimit setting. How do I know whether Analysis for Office is using the value that I defined?
- Reproduce the scenario in Analysis for Office recording a backend trace (Settings > Support > "Enable BW Server Tracing");
- In the BW system, go to transaction RSTT and enter the trace;
- Click on display;
- Select the program module BICS_PROV_GET_RESULT_SET and click on "Parameters";
- Check the tag <I_MAX_DATA_CELLS>, this is the value that Analysis Office is using for the limit of the result set size.
6.2 - I confirmed that Analysis Office is using the value that I defined for the limit of the result set but still I am getting message "Size limit of result set exceeded". Why?
The report is retrieving more data cells than the value defined for the result set size limit.
Follow the steps below in order to know exactly how many data cells are being retrieved from the backend:
- Run transaction SE37 and enter function module BICS_PROV_GET_RESULT_SET;
Set an external breakpoint line 94:
- Reproduce the scenario in Analysis for Office;
- Check the value in >> l_n_data_cells
This value is the number of data cells that Analysis for Office is trying to retrieve from the backend.
7 - Creating a SAP incident related to this scenario
In case everything is in place according to this Wiki page but the scenario is not working as expected and you want to create an SAP incident for further analysis, please check and provide the following:
Is the scenario reproducible in the latest version of Analysis for Office?
- What is the value of the setting ResultSetSizeLimit?
- If ResultSetSizeLimit is set to -1, what is the value of BICS_DA_RESULT_SET_LIMIT_MAX in RSADMIN table?
Record and attach to the incident the TRACE files of the scenario reproduced in the latest version of Analysis for Office by following the instructions of SAP note 2129389. Do not forget to select the "Enable BW Server Tracing" check box.
Attach to the incident a document with screenshots showing the step-by-step to reproduce the scenario in details, including the technical name of the objects involved;
Attach to the incident the files Ao_app.config and Ao_user_roaming.config;
Make sure that the following connections are open on the system:
- SAP NI Connection (note 1718597)
- R/3 Support (note 812732)
- Make sure there is valid user and password information in the secure area (see note 1773689).
Link to this page: https://wiki.scn.sap.com/wiki/x/IIh9Gg