Troubleshooting
Problem
You are encountering performance concerns with Planning Analytics for Excel or Planning Analytics Workspace, however do not know where to start. The objective of this document is to help you better understand the components involved with Planning Analytics for Excel / Workspace and the areas of the product that require focus.
Diagnosing The Problem
To diagnose a Planning Analytics for Excel or Workspace performance concern, you must quantify the problem and provide specific details:
-Are all reports/books affected? Or only one/some?
-For any reports/books affected, how was the performance concern raised?
-Did the report perform better previously? What is the basis of comparison for the concern?
-Is performance being compared to a different product/viewer, or the same product?
-Is performance being compared to a different environment? How do the environments/data compare?
-Is performance being compared to a different environment? How do the environments/data compare?
-Is performance being compared to a different version of the product?
-Is the performance problem related to concurrent users? What had been done to test/optimize your environment for concurrency?
-Is the performance problem related to concurrent users? What had been done to test/optimize your environment for concurrency?
-Does the problem impact all users, or only some? Is the problem related to geography/latency?
-Does the problem include write-back to the TM1 Server? (which in turn, can cause invalidation of cube cache / TM1 Rules that need to be refired)
Whether a performance concern impacts multiple reports or a single report, it is important to ensure consistency during the troubleshooting process:
-Create a list of reports or specific actions, recording your performance observations ( # of seconds / minutes to perform X action )
-Using the same reports or specific actions, record the performance observations from your test/development environment
-Using the same reports or specific actions, record the performance observations from your test/development environment
-Ensure all performance testing is consistent with the reports/actions identified. Continuously record your observations / results
-Clearly define the success criteria for your performance concern
-Clearly define the success criteria for your performance concern
-How many seconds/minutes do you expect the report to perform within?
-How did you determine this success criteria?
-How did you determine this success criteria?
-Clearly label any log files for the scenario(s) identified/captured
When working with IBM Support, the questions/focus points above need to be made very clear in your problem description.
The following logs will be useful towards understanding where the problem may reside:
-TM1 Server Thread Output (TM1Top / Planning Analytics Administration)
-TM1Server.log file (with log4j.logger.TM1.Lock.Exception=DEBUG enabled)
-TM1 Server Thread Output (TM1Top / Planning Analytics Administration)
-TM1Server.log file (with log4j.logger.TM1.Lock.Exception=DEBUG enabled)
-TM1_Messages.log file (from the TM1 Application Server)
-Fiddler / HTTP Trace (from a Planning Analytics for Excel or Workspace client machine that exhibits the problem)
-PA for Excel Logs (from PA for Excel Options menu > IBM > View Logs)
*Note, you may want to capture various log levels - increasing from None to All
*Note, you may want to capture various log levels - increasing from None to All
-Planning Analytics Workspace Logs (from the Planning Analytics Workspace install directory)
*Copy and zip up all of the log files from under the \logs\ directory
Resolving The Problem
It is very difficult to provide one clear plan to resolve a performance concern, due to the various ways that a performance problem may manifest itself. In order to resolve a performance problem with Planning Analytics for Excel / Workspace, you need to determine where time is being spent during a request/query.
The execution of a typical PA for Excel report, looks like the following:
-PA for Excel is opened, report is executed/refreshed
-PA for Excel sends the request through the TM1 Application Server (via PMHub)
-The TM1 Application Server (PMHub), sends the request/query to the TM1 Server
The execution of a typical PA for Excel report, looks like the following:
-PA for Excel is opened, report is executed/refreshed
-PA for Excel sends the request through the TM1 Application Server (via PMHub)
-The TM1 Application Server (PMHub), sends the request/query to the TM1 Server
-Once the TM1 Server has completed the request/query, the request is sent back to the TM1 Application Server (PMHub)
-The TM1 Application Server (PMHub), processes the request and returns it back to PA for Excel
-PA for Excel (and native Excel) finishes the processing of the request locally
-The TM1 Application Server (PMHub), processes the request and returns it back to PA for Excel
-PA for Excel (and native Excel) finishes the processing of the request locally
The execution of an action/query using the Subset Editor or Cube Viewer in PA for Excel is different:
-The Subset Editor / Cube Viewer are shared components between PA for Excel and Planning Analytics Workspace
-These components run through Workspace and communicate directly with the TM1 Server
-As such, you should confirm if your problem can be replicated within Workspace directly to confirm where focus should lie
-As such, you should confirm if your problem can be replicated within Workspace directly to confirm where focus should lie
The execution of a Book in Planning Analytics Workspace:
-Communicates directly with the TM1 Server / Data tier, using the TM1 REST API
-As such, you should confirm if your problem can be replicated outside of Workspace using TM1 Architect Cube Viewer where possible
-The exception to this rule is with Embedded Websheets, which utilize the TM1 Application Server (TM1Web)
-If you have a performance problem with an embedded websheet, you should confirm whether the problem occurs in TM1Web directly
The majority of performance concerns with Planning Analytics for Excel / Workspace are related to the TM1 Server, and not the client tool (PA Excel/Workspace):
-When doing performance testing/analysis, TM1 Server Thread output (TM1Top / Planning Analytics for Excel) needs to be captured
-Have a user run a report/perform an action in PA Excel/Workspace, and follow that user's thread on the TM1 Server using TM1Top/PAA
-How much time is spent processing the query on the TM1 Server?
-Does the user's thread contend with any other threads running on the TM1 Server? (WAIT states)
-See: How to Troubleshoot TM1 Server Lock Contention ( https://www-01.ibm.com/support/docview.wss?uid=ibm10734733 )
-See: How to Troubleshoot TM1 Server Lock Contention ( https://www-01.ibm.com/support/docview.wss?uid=ibm10734733 )
-If most time is spent on the TM1 Server, can the problem be replicated without Planning Analytics for Excel?
-Test a view that you can replicate using the TM1 Architect Cube Viewer, does the problem persist?
-If your PA for Excel / Workspace view is somewhat complex, test a view that is more closely aligned with a Cube View from Architect
-You may not have an issue with PA for Excel /Workspace at all, but a TM1 Server Performance problem and perhaps, an inefficiently designed TM1 Model
If the majority of time spent for your report/action does not appear to be on the TM1 Server, the time may be spent on the TM1 Application Server (in PMHub):
-You may not have an issue with PA for Excel /Workspace at all, but a TM1 Server Performance problem and perhaps, an inefficiently designed TM1 Model
If the majority of time spent for your report/action does not appear to be on the TM1 Server, the time may be spent on the TM1 Application Server (in PMHub):
-Only applicable to Planning Analytics for Excel / Embedded Websheets
-When doing performance testing/analysis, monitor the CPU/Memory for the TM1 Application Server's JAVA process
-After the TM1 Server has finished processing the query, do you observe high CPU on the TM1 Application Server?
-If CPU/Memory is high on the TM1 Application Server, you may need to better tune (or distribute) the TM1 Application Server
-See: How to Manage your TM1 Application Server (JAVA) Memory ( https://www-01.ibm.com/support/docview.wss?uid=ibm10737313 )
-NOTE: PA for Excel may perform better using a garbage collection policy of GENCON
-See: How to Manage your TM1 Application Server (JAVA) Memory ( https://www-01.ibm.com/support/docview.wss?uid=ibm10737313 )
-NOTE: PA for Excel may perform better using a garbage collection policy of GENCON
If time does not seem to be spent on the TM1 Server or TM1 Application Server, it is possible the time is being spent local to Excel / Web Browser:
-Using Task Manager, observe the CPU for the Excel/Browser process to see where time is spent
-If using Excel 32bit, does Excel 64bit perform better? (64bit should be used for optimal performance)
-If in a Web Browser, do other Web Browsers perform better? (if using Internet Explorer, does Chrome or FireFox perform any better?)
-What are the specs of the end user's machine? Do they have an outdated CPU? Or is the machine low on memory?
-What are the specs of the end user's machine? Do they have an outdated CPU? Or is the machine low on memory?
-Are there a lot of local/excel functions being used in the report? (only applicable to PA for Excel)
Performing all of the steps above will allow you to better understand where the problem you are troubleshooting resides.
To summarize the above - it is critical that you:
-Document all steps/descriptions/success criteria of performance concerns consistently
-Ensure you have performed load/stress testing prior to any Production implementation
-Test a number of scenarios to determine which component your problem truly resides in
-Capture the suggested logging in order to paint a complete picture of the user experience
-Test a number of scenarios to determine which component your problem truly resides in
-Capture the suggested logging in order to paint a complete picture of the user experience
Important Notes:
You can not just provide a set of logs and expect an answer. Troubleshooting performance concerns is very much a collaborative process. When providing logs for a Performance concern, you must provide details on who was performing what action, what time the action occurred, and the outcome/observations from that testing. Successfully troubleshooting a performance concern is more about the documentation/details captured, and less about the technical.
In addition to all items above, if you have a problem that is intermittent or only impacts a handful of users - the same set of logs/details should be provided from a scenario which does not demonstrate the performance problem - as this comparison will allow for a better understanding of differences.
Lastly, if you have a scenario which can be replicated on demand - it may be best to provide everything required to replicate that scenario to IBM Support in order to further expedite resolution to the problem. This would include:
-TM1 Data Directory
-TM1S.cfg file
-Excel Reports / Workspace Books
-Steps required to recreate
-TM1 Data Directory
-TM1S.cfg file
-Excel Reports / Workspace Books
-Steps required to recreate
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
27 April 2021
UID
ibm10872292