Business Intelligence works on the pillar of gaining insights on a momentary glance of the reports and visualization. The amount of data we require from the backend plays a major part in how quickly we could gather information from the report.
Despite the advancement and availability of a wide range of tools available for reporting and visualization, Excel has been a simple go-to tool for any kind of analysis. Thus, Analysis for Office continues to be one of the major ad-hoc analytic tools today.
In this blog, we are going to discuss about consuming large datasets from backend, the various aspects of it and how to configure it on Analysis for Microsoft Excel.
SAP BusinessObjects Analysis, edition for Microsoft Office (AO) throws the error “Size limit of result set exceeded” or “out of memory” in some scenarios when we try to bring in too much data from the backend into an AO report.
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.
Default Limit
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. When a query is executed in AO, objects are created in the main memory of the Frontend for each cell of the result set.
Independent of the memory capacity in the Frontend, on a 32 Bit environment, a process can allocate a maximum of approximately 2 gigabytes (GB). The .NET framework, by itself has an overhead memory usage of 600-800 megabytes (MB). So, the maximum memory allocated to the process EXCEL.EXE is about 1.2 GB before getting an “OutOfMemory” exception on the Front-end.
OutOfMemory Exception
This below exception occurs because of the amount of data cells that is being retrieved from the query/backend. If the report is huge and shows too much data, it will take quite sometime to be displayed.
In this case, we try to reduce the number of cells to be displayed by filtering or by simplifying the complexity of the query.
The following characteristics of a query or the result set could have a negative impact to the memory consumption:
- Number of attributes
- Active list calculation
- Activated input ready cells for a planning scenario
- Number of member presentations (key / text)
- Hierarchies
Memory limitation in 32-bit environment
Once ResultSetSizeLimit setting is changed and there is an error/exception in Analysis for Office related to “out of memory”, this is 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.
If you want to allocate more than the described maximum amount of memory in the 32 Bit environment, you can use the 64 Bit version of MS Office and the 64 Bit version of Analysis, edition for MS Office.
Maintain Sizelimit Settings in Analysis for Office (versions 2.5 or later):
Besides maintaining settings directly in file system, you can now modify settings directly in Analysis Office from versions 2.5 and later.
The dialog can be opened in the backstage area under
File -> Analysis -> Customize Analysis -> Technical Configuration
After changing the values in Analysis Office, the changes are executed immediately, and the changed values are available in the Technical Configuration dialog and in the corresponding files in the system.
Maintain Sizelimit Settings for Analysis for Office until 2.4:
For Analysis Office versions until 2.4, you need to maintain settings in the file system of the client PC. the settings are maintained in the file system and not in the registry. The Analysis specific settings are stored in the file system
User Level Configuration of Analysis for Office:
As a user, you can change the settings from user level in two files:
Ao_user_roaming.config and Cof_user_roaming.config.
The files are located in the file system under %APPDATA%\Sap\Cof.
These files are created automatically if you modify any setting in the settings dialog. You can also create these files manually.
Navigate to the folder %PROGRAMDATA%\Sap\Cof and open Ao_app.config file.
1. Search the entry for ResultSetSizeLimit and check the configuration level defined in the configSections area.
2. Ensure the configuration level is “UserRoaming” which means that user can change the setting ResultSetSizeLimit locally in Ao_user_roaming.config and the value of ResultSetSizeLimit in Ao_user_roaming.config file will overwrite the one in Ao_app.config file.
3. Add entry for ResultSetSizeLimit in Ao_user_roaming.config file if the setting is not already available in the file or change the existing parameter value of ResultSetSizeLimit.
4. You can set ResultSetSizeLimit with the parameter value to -1 in Ao_user_roaming.config file so that the ResultSetSizeLimit setting uses the values defined in the BW system.
<ResultSetSizeLimit value=”-1″ /> or you can set ResultSetSizeLimit with the parameter value to a specific number to define the maximum number of cells with this value, for example,
<ResultSetSizeLimit value=”1000000″ />
5. Save the Ao_user_roaming.config file
Scheduling Analysis Office workbooks – Sizelimit settings
When scheduling Analysis Office workbooks, settings maintained in AO_BiPrecalculation.config will be used instead of using settings in Ao_app.config and Ao_user_roaming.config on client. The file “AO_BiPrecalculation.config“ can be found in Analysis Precalculation installation folder of the scheduling server, for example,
[Drive]:\Program Files (x86)\SAP BusinessObjects\Analysis Precalculation.
On Summarising,
Many a times the data brought into the front end are minimal for purposes of performance and insight clarity. However, there are a lot reports built for certain complex business cases which require huge datasets to be brought in from the backend for reporting purposes.
SAP BusinessObjects Analysis, edition for Office(AO) serves as one of the best possible options if the size of the data comes up as a serious concern. As explained, the versions of AO starting from 2.5 have an in-built option to increase the size limit of data that is being consumed from the backend.
In the earlier versions of AO(2.4 and below), nevertheless this can be achieved through a system-wide or user-specific configuration, enabling the business users to consume large datasets from the backend.
If you have your reports as BEx workbooks and looking to migrate it to Analysis for Office, you might want to take a look at this – BEx Workbook to Analysis for Office Report – One Click Conversion
Got questions? Click here to get in touch.
