"Out of memory" when repeatedly running (F9) large/complex spreadsheet report in Controller 10.1.1308 or later

Technote (troubleshooting)


Problem(Abstract)

User launches Excel. User opens spreadsheet (XLS / XLSX file) containing Controller formulae. User clicks "Add-ins - Controller - Log in". User logs into Controller. User clicks F9 - the report works OK.
After repeatedly running a number of F9 refreshes, an error appears.

Symptom

IBM Cognos Controller Link

Out of memory

[OK]


More Information:

The number of F9 runs/refreshes will vary depending on the complexity/design of the report. In one real-life customer example:

  • Spreadsheet #1 was absolutely huge (42Mb) and had an enormous number (38) of separate sheets
    • It contained more than a quarter of a million fGetVal formulae (254,071 to be precise).
    • In this case, their spreadsheet only ran once successfully. On the second attempt (second F9 run) the error appeared.
  • Spreadsheet #2 was very large (8Mb)
    • It contained 167,363 fGetVals.
    • In this case, the spreadsheet ran successfully 7 times. On the 8th attempt (eighth F9 run) the error appeared.


Cause

There are many possible causes for memory issues when running reports.

  • For more examples, see separate IBM Technote #1406746.

This Technote specifically relates to the scenario where the customer is using Controller 10.1.1308 (or later).
  • In other words, the version of Controller used already contains a fix for the known issue described inside separate Technote #1588281.

In this scenario the cause is a known limitation of Controller, which is triggered when the spreadsheet report is extraordinarily large/complex.
  • Specifically, the number of fGetVals is absolutely huge (for example larger than 50,000).

More Information:
IBM recommend that customers design their spreadsheets using the 'best practice' guidelines that can be found inside Technote #1347497.
  • Specifically (for this issue) the most important limit is the number of fGetVal formulae per Excel spreadsheet
    • IBM recommend a maximum of 10000 fGetVal formulae per spreadsheet
    • IBM have found that many more fGetVals (e.g. 50,000) can work OK in some environments, but it is not recommended.

Environment

The issue only affects customers whose spreadsheets are extraordinarily large/complex.

Diagnosing the problem

To count the number of fGetVal formulae in the entire spreadsheet, perform the following:

  1. Open the spreadsheet inside Excel
  2. Click "Ctrl-F" (Find)
  3. Inside "Find what" type: fgetval
  4. Click "Options" and change "Within" to be: Workbook
  5. Click "Find All":

Resolving the problem

Workaround:

Reduce the complexity of the spreadsheet (mainly reduce the number of fGetVal formulae).

  • For example, when the real-life customer's spreadsheet #2 was modified so it only contained 40,000 fGetVals, the spreadsheet did not ever give the error message (even when repeatedly refreshed/re-run 60 times).

Fix:
Upgrade to a later version of Controller (when released) which contains an enhancement to cope with this known limitation.
  • TIP: To find out when this version is released, subscribe to APAR PM74241.

Related information

1406746 - **Troubleshooting** 'OutOfMemoryException' er
1588281 - "Out of memory / 'System.OutOfMemoryException
1347497 - Controller Excel Report Performance Tips
APAR PM74241

Rate this page:

(0 users)Average rating

Document information


More support for:

Cognos Controller
Controller

Software version:

10.1.1

Operating system(s):

Windows

Reference #:

1610976

Modified date:

2013-05-09

Translate my page

Machine Translation

Content navigation