IBM Support

Long waiting times when refreshing a Custom Report in IBM Planning Analytics for Excel

Troubleshooting


Problem

Some users might experience long waiting times when they refresh their Custom Reports in IBM Planning Analytics for Microsoft Excel.

Cause

Custom Reports that contain many tuple requests to the TM1 Server might cause longer than usual refresh times.

Resolving The Problem

You might experience improved refresh times in your Custom Reports by adding or modifying the following performance configuration settings in the CognosOfficeXLLSettings.xml file:
 
You can also also apply these performance configuration settings to Quick Reports to improve refresh times. For more information, refer to Applying Custom Report performance configuration settings to Quick Reports.

MinimumPolytopeSize

Use the MinimumPolytopeSize setting to configure the minimum size of queries that are sent to TM1 when you refresh a Custom Report. The default minimum size is 2.

Increasing the MinimumPolytopeSize can improve performance as it decreases the number of queries sent to TM1 for the workload. However, in some cases, increasing the value too high can degrade performance as the queries can become too large and take longer to execute on the server.

To test this configuration parameter with a Planning Analytics for Excel Custom Report, start by increasing MinimumPolytopeSize to 10 and measure the difference in performance. If there is an improvement, try increasing it in intervals of 10 through 100. When performance improvements plateau or begin decreasing for your workload, decrease it by 10 to find the ideal setting for your workload.

  1. Close Microsoft Excel.
  2. Open the CognosOfficeXLLSettings.xml file, located in Users\[user name]\AppData\Local\Cognos\Office Connection.
  3. Locate the following lines:
    <userSettings>
      <setting name="MinimumPolytopeSize">2</setting>
    </userSettings>
  4. Set MinimumPolytopeSize to 10.
  5. Save the CognosOfficeXLLSettings.xml file.

MaximumPolytopeAmount

You can use the MaximumPolytopeAmount setting to configure the maximum number of queries that a Custom Report executes against TM1 for a refresh. The default value of MaximumPolytopeAmount in Planning Analytics for Excel is 60.

When the MaximumPolytopeAmount exceeds the default value, Planning Analytics for Excel falls back to use a single tuple bucket request. In some cases, this behaviour ensures improved performance when the workloads have a wider variety of complexity. In other cases, however, it can degrade performance as the single tuple bucket query can be slow to execute for some workloads.

To test this configuration parameter with a Planning Analytics for Excel Custom Report workload, start by increasing it to 70, and measure the difference in performance. If there is an improvement, try increasing it in intervals of 10 through 100. When performance improvements plateau or start decreasing for your workload, decrease it by 10 to find the ideal setting for your workload.

  1. Close Microsoft Excel.
  2. Open the CognosOfficeXLLSettings.xml file, located in Users\[user name]\AppData\Local\Cognos\Office Connection.
  3. Locate the following lines:
    <userSettings>
      <setting name="     MaximumPolytopeAmount ">60</setting>
    </userSettings>
  4. Set MaximumPolytopeAmount to 70.
  5. Save the CognosOfficeXLLSettings.xml file.

MaximumQueryTuples

Use the MaximumQueryTuples setting to configure the maximum number of queries sent to TM1 when refreshing a Custom Report. The default value in the product is 65536 and corresponds to the maximum count of tuple intersections in the query that is being sent.

This configuration should only be adjusted after making changes to the MinimumPolytopeSize and MaximumPolytopeAmount configuration settings and testing them thoroughly.

  1. Close Microsoft Excel.
  2. Open the CognosOfficeXLLSettings.xml file, located in Users\[user name]\AppData\Local\Cognos\Office Connection.
  3. Locate the following lines:
    <userSettings>
      <setting name=" MaximumQueryTuples ">65536  </setting>
    </userSettings>
  4. Set MaximumQueryTuples to 70000.
  5. Save the CognosOfficeXLLSettings.xml file.
Applying Custom Report performance configuration settings to Quick Reports
You can apply Custom Reports’ performance configuration settings to Quick Reports to improve report refresh times. 
 
To do apply these performance configuration settings, enable the r60_UseQuerySharding feature flag in the tm1features.json file.  Enabling r60_UseQuerySharding applies the Custom Report query generation strategy to Quick Reports, which can help boost performance for some workloads. It is especially effective when working with large cubes where the server is often restarted.
Note: When r60_UseQuerySharding is on, the configuration parameters apply to both Custom and Quick Reports. A setting mix that works well for Quick Report workloads might not work well for Custom Report workloads. You will need to measure performance as part of the overall Planning Analytics for Excel IT deployment strategy to determine the best setting mix for your workloads.

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"ARM Category":[{"code":"a8m0z000000GoRcAAK","label":"Troubleshooting-\u003EPlanning Analytics For Excel"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 August 2023

UID

ibm16241608

Page Feedback