Planning Analytics Excel Options

August 20th, 2020

Many of you use Excel when working with Planning Analytics.  You may be creating budgets, exporting files, creating reports, or uploading info via Excel.  It can be a little overwhelming figuring out all of the different Planning Analytics Excel options.  Below I am going to outline all the different options to help you in your Planning Analytics journey. 

We recently had a client looking to create offline reports for people to enter their budgets and then reconnect to the TM1 server and update the info. We worked with them to create quick reports to send to people and once they send back, we will reconnect to TM1 and update the info.

Planning Analytics Excel – PAX

Planning Analytics for Excel, known as PAX, is the new Excel add in for Planning Analytics.  Unlike Perspectives, the old and slower prior version of Excel for TM1, PAX offers a seamless Excel add in experience that works great over both local and wide area networks.  PAX also offers several different report types and options.  Below you will find details of each report option.

  • Exploration Report - quickly change how you view performance measures and to show detailed information from your database in rows and columns.  Explorations offer slice and dice capabilities to easily change the look and feel of your reports and create quick analysis. You can learn more here.
Exploration Report

  • Quick Report - combine data from multiple data sources in a Quick Report and then enhance the data by using Microsoft Excel formulas, formats, and cell references. When you create or open a Quick Report, the names of the dimensions used in the Quick Report are automatically displayed at the top of the report. Click here for more information. 
Quick Report
  • Dynamic Report – create complex reports that combine the functionality of IBM® TM1® with Microsoft Excel features.  Dynamic Reports are implemented through a series of worksheet functions that define the components of a form such as context members, row members and display properties. When you convert an Exploration View to a Dynamic Report, the functions are created for you. Dynamic Reports support features available in Explorations and Quick Reports, such as selectable context members, stacked row and column dimensions, expandable/collapsible consolidations (rows only), zero suppression (rows only) and inserting rows and columns (with some restrictions).

You can read more here about Dynamic Reports

Dynamic Reports
  • Custom Report - create reports by using TM1® worksheet functions to populate the cells of the report.  You can create a Custom Report by using the following methods - converting an Exploration View to a Custom Report, converting a view in the source tree to a Custom Report or manually entering formulas to build a report.  Learn more here.
Custom Report

Exporting to Excel

There are a couple of ways to export to Excel.  You can use the PAX option as detailed above.  You can also use TM1 Web and Planning Analytics Workspace (PAW) to export.  Both options allow an export on to multiple tabs based on a dimension you choose.  There are some limits and I will detail all of this below.

  1. PAW – The procedure to export from PAW is detailed here.

Couple of items to note.  There are size limitations to the export. 

  • Max rows – 1,048,576
  • Max columns – 16,384
  • Max # of cells – 8 million
  • Max # of views (tabs) – 100

The screen shot below would result in 15 tabs in 1 excel workbook.  You do have the option to separate these into multiple workbooks.

PAW Procedure

2. TM1 Web – The option to export to Excel in TM1 is similar to PAW except you are only allowed 50 views in total.

TM1 Web

Import to Create Dimensions

Did you know you can create dimensions from csv or text files?  Simply click, create new dimension, in PAW and click, browse for file and upload directly in to Planning Analytics. 

Import to Create Dimensions

Summary

Excel is not going away and if we can learn to use it in conjunction with Planning Analytics, the power of both can take your planning and analysis to the next level.  If you need to discuss your particular case please reach out to us at services@lodestarsolutions.com  For more information on TM1, Excel and Planning Analytics check out our long library of blogs.

Cognos Planning Analytics for Excel

Planning Analytics for Excel (PAX)
Planning Analytics for Excel (PAX)
March 12, 2020

Upgrading TM1, checkout Cognos Planning Analytics for Excel PAX

Are you a long time TM1 user looking to upgrade to the latest version of Planning Analytics?  Well if you are on TM1 10.2 you are on an unsupported version, so you really should upgrade.  You don’t want to have a Microsoft Excel update break your Perspectives report and not be able to get a patch because your version is unsupported by IBM. So, it’s time to plan your upgrade.  When you are upgrading TM1 checkout Cognos Planning Analytics for Excel (PAX). 

What is PAX

Planning Analytics for Excel (PAX) is the newer user interface that IBM has created for TM1 users. Officially per IBM, “IBM Planning Analytics for Microsoft Excel is a Microsoft Excel-based tool that professional report authors use to build sophisticated, multiple-sheet, multiple-query reports against multiple databases. Users can build sophisticated multiple-sheet, multiple-query reports in Excel from different kinds of data sources and analyze and explore IBM Cognos dimensionally modeled data.”

There are many benefits of PAX.  The biggest benefit is that Planning Analytics for Microsoft Excel reports are designed to work even in a wide area network environment, without the need for Citrix. Visit this link to see other benefits. 

You Own Planning Analytics for Excel (PAX)

Almost all TM1 clients that are current on IBM support have rights to download the latest version and have the rights to use Planning Analytics Excel (PAX).  Note, there are a few clients that do not automatically have the rights to PAX, for details see our earlier blog on this topic.  If you are an older IBM TM1 client there’s a likelihood that your end users might not have licensing for Planning Analytics Workspace (PAW), the new dashboarding and modeling interface, but they probably have PAX licensing. Whereas, TM1 Modelers typically have licensing for both the PAX and PAW.

What you need to know to use PAX

PAX and TM1 Perspectives

When you upgrade to Planning Analytics you might have older Perspectives reports that you still want to use.  It’s important to know that Perspectives and Planning Analytics for Excel do not play nice together.  So, when you install Planning Analytics, you will want to install Perspectives and PAX on separate servers or work stations.  If you need both products, we recommend installing on Citrix or terminal services and separating them.  Another big consideration is based on existing Perspective workbooks.  You should copy that report and then open in PAX, leaving you both a Perspective version and a PAX version.  If you open the original Perspective report in PAX it will convert and you will not have that report in Perspectives any longer. Last, Perspectives is a LAN (Local Area Network) tool and PAX is a WAN (Wide Area Network) tool and performance in PAX leveraging the wide area network, is much quicker and more stable.

You should Install PAW for PAX

Lodestar Solutions recommends that when you install Cognos Planning Analytics to upgrade your TM1, that you plan to also install the Planning Analytics Workspace (PAW), even if your end users don’t have PAW rights. Remember your TM1 Modelers do have PAW rights. 

The Modelers will be able to use the PAW to create hierarchies which provides tremendous new flexibility in your TM1 models  Check out this link for more info.  Hierarchies enable the use of data as virtual dimensions.   The hierarchies then can be used in PAX.  Note you can’t create hierarchies in TM1 architect and that’s why PAW is so important.

Another reason to install the PAW is that your TM1 Modelers might want to do a proof of concept and show the executives and users the power of the new TM1 dashboarding functionality. Your dashboard could include a PAX view embedded in with visualizations.  At Lodestar Solutions, we have found that clients that build a proof of concept for executives receive greater support for their initiatives and are more likely to receive funding. So, make sure you plan to install PAW with PAX when you upgrade.

PAX on the Web

For TM1 users that push Perspectives reports to TM1 web it’s important to know you can upgrade your Perspective reports to PAX.  Additionally, you can push PAX reports to the TM1 Web as well.

IBM Cognos TM1 Web

It’s time for PAX!

You’re upgrading TM1, so checkout Cognos Planning Analytics for Excel (PAX) and plan appropriately!  But you don’t have to go it alone!  Lodestar Solutions can help answer all your questions and help you plan your upgrade.  For a free consultation, email us at Services@lodestarsolutions.com.

X