Troubleshooting
Problem
When using both TM1 and Controller plugin for Excel, under certain situations Excel will seem to hang and stop responding, seem slow or have very bad performance.
Symptom
User first opens a TM1 Perspective report (typically a large/complex TM1 report).
Next, the user tries to open a Controller Excel report or form. However, Excel will appear to hang (and stop responding) or take very long to open.
Cause
Limitation (reference APAR PM74916) of some older versions of Controller, triggered by the default way that Microsoft Excel handles calculations.
More Information
By default, modern versions of Excel set its “calculation mode” to “Automatic”:
- This will make Excel recalculate/re-run all sheets in all open workbooks, with every change made.
- This is done to ensure that all changes are updated across different sheets
However, “Automatic” calculation can cause slow performance/hanging for large/complex TM1 reports.
- Therefore, TM1 Perspective will switch “calculation mode” to “Manual”.
However, when Controller opens, this will set Excel’s calculation mode back to the default (“Automatic”).
- Therefore, when Controller is building the report or Form (e.g. when the report/form is being transferred from the Controller client into the Excel subsystem), each change will cause the TM1 report to be re-run/recalculated.
If the TM1 reports contain complicated or time consuming calculations, each of these recalculations can slow down Excel considerably.
- If the end user is patient (and waits some time for the TM1 spreadsheet report to finish running) the system will eventually come back to normal
- However, the end users may believe that the system has 'hung' and therefore they may erroneously think that the system has crashed.
Environment
Excel has both TM1 Perspectives and Controller Excel addin installed.
Resolving The Problem
* Fix *:
Upgrade to Controller 10.1.1 FP1 (10.1.1671) or later, and then use the new "EXCELCALCMANUAL" local preference.
IMPORTANT: After upgrading, to enable the change in how Controller works (with respect to auto-recalculation) each user must also perform the following steps:
- Launch Controller
- Click "Maintain - Installation - Local Preferences"
- Create a new entry with the following key: excelcalcmanual
- Give it the value of: true
- Save changes
- Restart Controller and test.
NOTE: If a user opens Controller's "Data Entry" functionality and clicks 'save' then (by default) all formulas in other open workbooks will be recalculated.
- This is by Microsoft design in Excel when "Recalculate workbook before saving" is selected (ticked).
* Workaround *:
There are two workarounds:
Method #1 (works for all environments)
- Open Controller-related reports/forms (files) first and then open TM1 reports afterwards.
- Initially (when the Controller report/form is opened) Excel is running as ‘automatic’
- Afterwards, TM1 will automatically set Excel to ‘manual’ (desired setting) as soon as its report is opened.
- Therefore, the TM1 report is only ever calculated once.
Method #2 (only works in some environments)
- Modify the default behaviour of Excel so that it does not automatically recalculate reports.
- In other words, change its default calculation mode to “manual”.
- The following will affect ALL spreadsheets (not just Controller-related ones) that the end user opens on his/her PC, so please make sure that the end user is made aware of this change
- Also, be aware that this workaround only works in some (not all) environments.
Steps:
NOTE:
2. Click "START - RUN"
3. Type: Regedit <Enter>
4. Locate the Registry key for your version of Excel.
TIP: This will vary depending on the version of Office that you have installed. For example, if using Excel 2003 (also known as Excel version 11) then navigate to: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
5. Create a DWORD value called FullCalcOnLoadOldFile with the value 0
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21504410