The integration of Microsoft Excel with Planning Analytics, powered by TM1 Server, remains one of the most
powerful pieces in the overall planning, modeling, and other solution capabilities provided by Planning Analytics! In a recent webinar of the same name, which you can find in our QueBIT video catalog, we focused on that integration and made a strong case for the Planning Analytics for Excel add-in as the Excel integration standard-bearer, going forward.
Planning Analytics for Excel (“PAfE” for short, previously called “PAx”) is designed to ultimately replace Excel-based functionality in the over 20-year-old TM1 Perspectives Excel add-in. Perspectives may be hard to let go of for some users, but remember that it was designed at a time when we didn’t even have web interfaces for TM1, let alone many of the newer features that have been added to TM1 since its first release. PAfE, by contrast, is designed for the most recent versions of Excel and for the modern Planning Analytics “stack” of components, including Planning Analytics Workspace (PAW). It can also perform more consistently across different network environments, which is well-covered in a previous blog post entitled “How to Survive Remote TM1 Perspectives”.
Whether you’re an IBM Planning Analytics Cloud user or a customer who’s been using TM1 on-premise since the 1980’s, we believe there are several reasons why now is the perfect time to consider taking full advantage of Planning Analytics for Excel’s capabilities. We’ll describe why Excel integration is so important and suggest some ways of making the most of that integration in your organization.
Spreadsheets are not the Enemy
A common trend we’ve seen in the last decade is the demonization of spreadsheets by software vendors, particularly those selling ERP and planning software. Vendors trumpet their solution as a way of “killing spreadsheets” in an organization. The real problem at hand is spreadsheet-misuse, where an organization has been using spreadsheets to manage business-critical data and calculations, which are things spreadsheets were, of course, never designed to do!
Any solutions whose goal is to replace spreadsheets, however, ignore the legitimate reasons people use Excel in the first place: it is ubiquitous, it is incredibly flexible for ad-hoc analysis, and, most importantly, it is excellent at presenting information. Interestingly, when many of these same anti-spreadsheet vendors are pushed to provide some form of Excel integration their solution is this: provide a means of exporting data to Excel. This does nothing but enable the original spreadsheet-misuse problem! But it doesn’t have to be this way.
The Five Principles of Spreadsheet Integration Done Right
We believe that Excel spreadsheets don’t have to be the enemy of business and IT! To discourage the bad habits of Excel spreadsheet misuse when it comes to data integration, which are a serious concern, we propose these principles of “Excel Integration Done Right”:
- Excel should be used for presentation and ad-hoc analysis of data, NOT data management
- Data should be sourced from live connections to source database(s), NOT bulk-exported into Excel
- Important calculations and models should be defined in the source database(s), NOT in Excel
- Excel integration should feel to the user like another Excel feature, NOT a competing application, especially one that alters normal Excel behavior
- Users should be able to write back from Excel to the source database(s), security permitting, where relevant and appropriate (like in a planning application)
Both of Planning Analytics’ Excel add-ins, TM1 Perspectives and PAfE, score very highly on these principles. PAfE, being a younger product, did suffer as a result of some early technical issues, but we believe those have been addressed and that it is now ready for serious consideration by customers as a replacement for TM1 Perspectives, which at some point in the future will be officially retired by IBM. Not to mention PAfE, in many ways, is superior when it comes to its integration with Excel.
Reasons to Standardize on Planning Analytics for Excel (PAfE) Today
Here are some of the top feature-oriented reasons to consider adopting PAfE for Planning Analyitcs users who use Excel:
- PAfE is designed with a modern Excel ribbon interface that connects to any cloud or on-premise TM1 Server database. It also includes task panes that don’t take you out of the Excel experience and doesn’t require adjusting things like Excel calculation modes.
- PAfE has multiple options for exploring and referencing TM1 Server data, including a Cube Viewer, Explorations (in-spreadsheet browser), Quick Reports (formula-less reporting), and Dynamic & Custom Reports (formula-based reporting)
- All data exploration features are governed by TM1 security and permit write-back as well as drill-through to other sources, if defined in Planning Analytics
- PAfE is able to leverage content in PAW as well as TM1 Server Application Folders, and is also designed to support newer Planning Analytics features like hierarchies
- PAfE has a new VBA API which provides direct access to the newer REST (OData) API in Planning Analytics
Another reason we think the time is right to standardize on PAfE now is that IBM has designated the Planning Analytics 2.0.9 release as an LTSR, or long-term service, release. This designation means that IBM will support this version for a minimum of 2 years. Several fixes and improvements related to PAfE have also gone into the 2.0.9 release, making this a great combination to standardize on.
Planning a Migration to Planning Analytics for Excel
If you are interested in making the change from TM1 Perspectives to Planning Analytics for Excel we recommend taking the following steps.
Please contact QueBIT at info@quebit.com if you would like us to help you put together a more detailed migration plan for Planning Analytics for Excel!
Step 1: Install Recent Versions of all Required Components
You should install the most recent versions of PA Server, PAW (which is required for PAfE), and PAfE. At the time of this writing, the most recent versions available are:
- PA Server: 2.0.9 IF2.1
- Planning Analytics Workspace (PAW): 2.0.49
- Planning Analytics for Excel (PAfE): 2.0.51
Even though PAW is required by PAfE, no additional licensing cost is required to use PAW with PAfE, nor is there an additional cost for your Modeler users to use PAW. Licensing costs are only required if you plan to allow all users to use PAW.
Step 2: Testing & Conversion
PAfE uses a different format of Action button, and there is a conversion tool that will need to be used for any reports that contain Action Buttons. In addition, there are some formula behavior differences in PAfE that could cause existing Perspective reports to not render properly in PAfE. It is important to make sure users test their reports, which could also present an opportunity to clean up unused reports.
Tip: QueBIT’s ReportWORQ can quickly tell you what is wrong with any formulas in your reports (in addition to helping you automate Excel report distribution!), saving you time should you encounter this problem. ReportWORQ will also include a free Print Reports replacement tool for PAfE in an upcoming release!
Click here to learn more about ReportWORQ for Planning Analytics and to request a free trial
Be sure to test reports in PAfE with a representative volume of users and, most importantly, do not try to load Perspectives and PAfE add-ins at the same time in Excel!
Click here to see if a QueBIT Reporting Healthcheck is needed as part of your PAfE conversion
Click here to see if it’s time for an overall IBM Planning Analytics Healthcheck as part of your PAfE upgrade plans
Step 3: Invest in Training
Training is a key part of the adoption of PAfE. We recommend starting with train-the-trainer sessions, which can be supplemented with additional drop-in sessions during and after your migration. The mapping between Perspectives and PAfE features is straightforward, so users comfortable in Perspectives should adapt quickly to PAfE. QueBIT offers live and computer-based (CBT) training packages in PAfE which may be helpful.
Click here to purchase QueBIT’s IBM Planning Analytics for Excel Computer-Based Learning class and learn more about other live training options
Do not forget to train your IT Desktop Support staff in the differences between Perspectives and PAfE, since the same troubleshooting techniques they used for Perspectives issues likely will not apply to PAfE
Step 4: Do NOT let your users keep Perspectives, post-upgrade
This is, by far, the most important success factor in adopting PAfE in an organization. Provided your tests pass and you’re ready for go-live, we strongly advise against installing Perspectives once you’ve introduced PAfE in order to ensure its full adoption.
Step 5: Consider Decommissioning Citrix or Remote Desktop Environments
It is possible that PAfE’s performance over VPN or other remote connections will eliminate the need for Citrix or similar “Desktop as a Service” applications, if used by your remote Excel and Planning Analytics users.
Step 6: Engage IBM Support When Needed
IBM wants to hear from you when you encounter problems with Planning Analytics, in general, not just PAfE. Before you try to spend days troubleshooting a problem why not reach out and make use of the helpful IBM support team!
Conclusion
We believe superior Excel integration is one of the things that makes Planning Analytics and TM1 so powerful. The Planning Analytics for Excel (PAfE) add-in we feel is now worth considering as a replacement for the legacy Perspectives add-in, and encourage our customers to give it a serious look. We hope the information provided here will help convince you of this, and provide you with some tips for making that transition!
