IBM Support

Large Number of Threads Created on TM1 Server When Running Large Planning Analytics for Excel Report

Troubleshooting


Problem

When monitoring TM1Top/PAA, administrators observe moments where tens or hundreds of threads are opened on the TM1 Server in a short time span.  

Symptom

=====  2020-02-24 16:35:34 GMT      2020-02-24 16:35:34 local  ===== Server:tm1===========
139643815196416    Th:Pseudo    -    Idle    -    -    -    -         0
139643898828544    Th:DynamicConfig    -    Idle    -    -    -    -         0
139506236552960    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        13
139504367855360    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        11
139504651437824    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        12
139504639915776    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        12
139504631523072    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        12
139504613742336    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        12
139506871572224    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        14
139507334510336    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        14
139507307382528    NAMESPACENAME/USERNAMEX("NAMESPACEID:u:uid=12345678")    Workspace:3423    Run:R    -    GET    /api/v1/Cellsets('RYyGuUsFAIA5AAAg')    -        14

Cause

Planning Analytics for Excel uses a parameter called CellsetChunkSize to increase throughput between Planning Analytics for Excel and the TM1 Server.  The behaviour described in this document occurs when users open very large views/reports in Planning Analytics for Excel.
The CellsetChunkSize is set in the following file:
  C:\Users\<user>\AppData\Local\Cognos\Office Connection\CognosOfficeReportingSettings.xml
The default setting is: <setting name="CellsetChunkSize">8000</setting>
While refreshing data, Planning Analytics for Excel will retrieve the CellsetSize of a query and then divide that number by the CellsetChunkSize.  For example, if you run a report that has 1million cells... divided by 8000, will result in 125 GET requests to the TM1 Server.

Resolving The Problem

To resolve the problem, modify the CellsetChunkSize accordingly
-Close Excel
-Modify the CellsetChunkSize property in 'C:\Users\<user>\AppData\Local\Cognos\Office Connection\CognosOfficeReportingSettings.xml'
-Increase the setting to a reasonable number based on the size of your report (number of cells)

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSCTEW","label":"IBM Planning Analytics Local"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
20 September 2021

UID

ibm13380007

Page Feedback