"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 (reference APAR PM74241) 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

    Fix:

    Upgrade to Controller 10.2.2021 or later.

    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).

    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:

    2014-11-04

    Translate my page

    Machine Translation

    Content navigation