2651810 - Out of Memory error in Analysis Office

When refreshing a workbook or inserting a query in Analysis Office, an error occurs related to out of memory.

The entry below is also seen in AO traces:
------------
Exception of type 'System.OutOfMemoryException' was thrown.
------------

SAP Analysis for Microsoft Office

1. Refresh an existing workbook in Analysis Office or insert a new query;
2. Confirm the error "Exception of type 'System.OutOfMemoryException' was thrown." 

The out of memory error in Analysis Office usually occurs when:

• The report is retrieving a huge amount of data;
• The setting "ResultSetSizeLimit" has been changed in the machine and it is not using its default value.

Analysis for Office is a reporting tool, hence it is not suitable for mass data extraction but for data analysis.

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 SAP note 1729141.

This limit can be changed by changing the value of setting "ResultSetSizeLimit" in Analysis Office, but this is not recommended.

The SAP recommendation is to not change the default limitation of 500.000 data cells in Analysis for Office.

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 and the Wiki page https://wiki.scn.sap.com/wiki/x/IIh9Gg which contains additional hints about the topic.

1729141 - Analysis Office: Front End Memory Consumption
1703279 - General performance recommendations Analysis Office
1656983 - Result Set Size Limit Exceeded Message

Wiki page: "Analysis for Office 2.x - Data Cells Limit and Memory Consumption"

out of memory, consumption, report, query, limit, crash, error, exception, big , KBA , BI-RA-AO-XLA , Excel Addin , Problem