IBM Planning Analytics for Microsoft Excel – or PAfE (previously referred to as PAX) – is a Microsoft Excel-based tool that report authors use to build sophisticated, multiple-sheet, multiple-query reports against multiple databases, including Planning Analytics models.

If you are a PAfE user, or support a group of PAfE users, you may eventually need to diagnose a performance issue. The following ideas have been pulled together both from hands-on experience as well as discussions with consultants in the field. I hope you find them helpful.

How do I diagnose a PAfE Performance Problem?   

The first step in addressing a PAfE performance issue is to identify the “scope” of the issue. In other words, ask questions like: “Is performance the same for all users or does it vary for specific individuals or groups? Does performance deteriorate when performing certain actions within PAfE?  Are there issues related to only certain data sources or connections? Finally, can you associate performance complaints to specific worksheets or files?

PAfE performance issues will be either environmental or data related. Environmental issues include problems such as network bandwidth, the physical workstation where you are running PAfE or Excel itself as in how it was installed or configured or if it is a much older version. Data – either a workbook or CSV file – can also affect performance, based upon overall size and how it was constructed.

First Steps

Your first step in diagnosing any PAfE issue should be to perform the following tests:

  1. Disconnect from any data source you may be connected to and close Excel. Reopen Excel and make sure the PAfE add-in is loaded. If you are a previous Perspectives user, make sure Perspectives did not also load. Try reconnecting. (If you are confused with the difference between Perspectives, PAX and PAfE, you might want to read this article).
  2. Within PAfE, click on Options, then IBM. From there, make sure you click on the Clear Cache button. Next, check the “Clear Cache on Save” checkbox. Close and reopen Excel. Regularly clearing the cache can also reduce the overall size of the workbook. Read more about cache management here.
  3. Excel 2016 and newer comes with the ability to turn on “Optimize for Compatibility”. To utilize this feature, within Excel, click “File” and then “Options” and look at the top of the “General” section. If you have “When using multiple displays” under “User Interface Options”, select “Optimize for compatibility”. Close and reopen Excel.
  4. Within Excel, go to Formulas, and make sure “Calculation Options” is set to Manual. This will avoid having worksheets automatically recalculate.
  5. Check your version of PAfE and consider upgrading PAfE to a newer version (available from Fix Central). IBM releases updates to PAfE almost monthly. For a comprehensive listing of Planning Analytics components (including PAfE) releases by month and year, see Timeline for IBM Planning Analytics Releases. To better understand how to locate and download install packages, you can refer to this helpful article: Demystifying the PA and PAfE install files.

Excel & PAfE Navigation

If when using PAfE to view a file, PAfE “stalls” or becomes very slow when you try navigating around the file (not when you are trying to refresh or pull data but rather simply when trying to move around in the file), you should review the following:

  1. Location – Consider the location of the file. Performance issues may occur if the connection to the network location is lost during the time that Excel is opening or accessing the file. Try moving the file to a local folder before opening it. Moving the file locally will help determine whether there is something wrong with the file, or with where the file is saved. Several issues can arise when you save an Excel file over a network or to a web server. It is a good idea to save the file locally.
  2. Conflicts – Take notice of what add-in(s) are loaded in Excel when the performance degradation occurs. There may be a conflict occurring between add-ins. You can try removing all add-in’s other than PAfE on-by-one and see if that resolves the problem. Third-party or “feature specific” add-ins are a good place to start. Find the problematic add-in and disable it and see if the performance issue is resolved.
  3. In-Use – Using Windows Task Manager, make sure another process is not using the file in question and also make sure there are not multiple instances of Excel running. You may need to reboot your system to clear any in-use contentions.
  4. Antivirus – If your antivirus software is not up-to-date, Excel may not function properly or “be slower” when performing some tasks. If your antivirus software includes integration with Excel, you may experience performance issues. In this case, you may be able to disable all Excel integration within the antivirus software or disable any antivirus software add-ins that are installed in Excel.

Slow Worksheets – Unpredictable Function

There can be issues in performance caused by the use of certain Excel formula functions. Certain functions, or the way the functions have been implemented within a worksheet, can cause unwanted recalculating. One example is using Excel functions on column headings (or other cells) that DBRW formulas on the rows reference. Basically, every entry into a cell has the potential to cause the entire sheet to recalculate. Unpredictable function behavior may also be caused by using OFFSET, INDIRECT and SUMIF (Excel functions). If any TM1/Planning Analytics formulas are dependent on the results of these functions, you may want to rethink their use.

Final Thoughts

The above are simple common-sense ideas for addressing PAfE performance issues. If you have any questions or are still experiencing trouble after taking these steps, call the experts at QueBIT at info@quebit.com who would be happy to help resolve your particular situation.