Budgeting Planning & Forecasting

Top 5 IBM Planning Analytics Tips & Tricks

June 25, 2018 by Lee Lazarow Leave a Comment

Every week we publish a new blog post on IBM Planning Analytics Tips & Tricks. Some of these focus on how to use new features in IBM Planning Analytics and others explain how to optimize key aspects of the core TM1 engine.

We took a look at our analytics (yes, we don’t just implement analytics, we use them too!) to see which blog posts with tips & tricks are the most popular with Revelwood clients and visitors to Revelwood’s Knowledge Center. Here are our most popular blog posts on IBM Planning Analytics and TM1.

1. Working with the TM1 REST API

In this blog post, Nina Gordy, a consultant in our Professional Services Group (PSG), delivers tips on working with the TM1 REST API. Nina worked extensively with the REST API while she was on the development team for Quantum, our TM1 mobile application for system administrators.

2. Displaying Data in Maps

Lee Lazarow, our FPM technical director, details how to easily visualize data in IBM Planning Analytics Workspace. He explains the three easy steps you need to take in order to turn your data into a map.

3. Turbo Integrator Logging

In this blog post, also written by Lee, we provide tips on logging with Turbo Integrator. The proper use of logging can help minimize the amount of disk space needed in your environment and may also help improve the efficiency of your data load processing.

4. Using MDX to Compare Dimensions Hierarchies

MDX, which stands for multidimensional expressions and is similar to SQL, is for multidimensional data in OLAP cubes. TM1, however, has a unique implementation of MDX. Shane Bethea, also a consultant in PSG, explains how TM1 dimensions often have multiple hierarchies that should all roll up to the same total. But sometimes they get out of sync. This is where MDX comes in – it can be used to see if both hierarchies contain the exact same number of n-level customers, and, if the two hierarchies are out of sync, it can identify the elements causing the problem.

5. Making Default Cube Views with MDX

Jay Apwah, a consultant in PSG, also wrote a post on MDX. His post covers how you can easily update a cube value to control the period elements that will display in a subset used in views. The concept he covers in this post can be applied in many different ways.

Want more tips & tricks on working with IBM Planning Analytics and TM1? Visit our Knowledge Center on a regular basis

IBM Planning Analytics Tips & Tricks: Planning Analytics for Microsoft Excel (PAx) VBA API Calls

June 19, 2018 by Revelwood Leave a Comment

This is a guest post from Revelwood’s Shane Bethea.

Did you know IBM Planning Analytics lets you make API calls through VBA in Planning Analytics for Microsoft Excel (PAx)? You probably know that action buttons can be used to refresh or rebuild reports in PAx. But what if you want to refresh the data in your report as part of a VBA routine? Maybe there are more tasks you want to perform through VBA and then you want to refresh the data as the last step. IBM offers the ability to make API calls through VBA in PAx with just a few steps.

The first step is to open the Visual Basic editor in Excel and import two files (one module and one class module) that ship with your PAx installation. Do this in the VBA editor by right clicking on your workbook and selecting Import File.

This opens an Import File dialogue box. Navigate to your PAx install location (either Program Files or Program Files (x86) ) and then navigate to \ibm\cognos\IBM for Microsoft Office\Automation\ .

You can then import two files: ‘CognosOfficeAutomationExample.bas’ and ‘CognosOfficeMessageSuppressor.cls’.

Learn how to make VBA API calls with PAx

Once imported, you can use the public sub routines in the CognosOfficeAutomationExample module. This module includes some useful sub routines, such as:

ClearAllData() – Functions the same as the Clear All Data toolbar/ribbon button to clear Explorations and reports from the workbook.
RefreshAllData() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data for Explorations and reports in the workbook.
RefreshAllDataAndFormat() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data and formats for Explorations and reports in the workbook.
UnlinkAllData() – Functions the same as the Convert to Static toolbar/ribbon button to unlink all data in the workbook.

If you want to Refresh or Rebuild a Dynamic Report in PAx (formerly Active Forms in TM1 Perspectives), you have to get more creative. To accomplish this, follow the steps to import the two modules discussed earlier and then add the below code to the CognosOfficeAutomationExample module:

Now you can call the RebuildDynamicReport() sub routine from a Command Button or other VBA macro to rebuild your Dynamic Report.

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Tech Bulletin: PAx Incompatibility Caused by Latest Microsoft Office Update

June 18, 2018 by Revelwood Leave a Comment

This is a technical bulletin from Revelwood’s Steve Ellis.

Did you know that the latest version of Microsoft Office 365 caused Planning Analytics for Microsoft Excel (PAx) to become unusable? I will show you how to determine if you have been impacted and how to resolve this.

Root Cause: This is not an issue with PAx. This incompatibility was caused by the latest Microsoft Office update in May 2018. IBM has logged the issue with Microsoft, and although Microsoft is working on a fix, there is not a resolution date at this time.

Symptoms: After connecting, your Task Pane will appear completely grey. You will not be able to navigate the Task Pane in any way. Please see the image below that illustrates the issue:

PAx Incompatibility Caused by Latest Microsoft Office Update

Resolution: You will need to revert your version of Microsoft Office 365 to the latest stable copy. To do so, please follow the below directions:

Turn off Automatic Excel Updates (File > Options > Update Options > Click “Disable Updates”)
Open the Command Prompt as Administrator
Paste this into the Command Prompt and then hit Enter: cd %programfiles%\Common Files\Microsoft Shared\ClickToRun
Paste this into command prompt and then Enter: exe /update user updatetoversion=16.0.9029.2253

The above actions will revert your Microsoft Office 365 to the last stable version, and PAx will now work as intended.

Need help resolving this issue? Revelwood is standing by and ready to assist you! Please contact us at info@revelwood.com.

Embracing Continuous Planning

June 18, 2018 by Lisa Minneci Leave a Comment

The old school annual budgeting and planning process is just that – old school. Today’s forward-thinking FP&A executives, managers, and analysts instead are embracing continuous planning and rolling forecasting. Rolling forecasting is the process of simulating profit and loss for a company on a rolling basis. Many FP&A executives who’ve led their company’s transition from the inefficient annual budgeting process to one of continuous planning and rolling forecasting will tell you it was one of the bst decisions they’ve made for their company.

One of the biggest challenges with the traditional approach of developing an annual plan or budget is that as the year goes on, you end up making decisions based on the reality of your business from two, three, or even more quarters ago. You are not able to factor in recent changes in your business or your market. You don’t have the ability to quickly react to disruptions in the market or to new opportunities that you did not foresee months ago.

Continuous planning gives you the ability to quickly react and respond to all the unforeseen changes in your business, and to do so in a way that reflects the current reality of your business. It provides a methodology for forecasting at frequent intervals throughout the year, and enables managers to see trends, patterns and “breaks in the curve” long before your competitors. It is the singular most powerful tool to help your line-of-business managers and senior executives make better informed decisions regarding your products and your market.

Jim Collins, performance management strategy executive at IBM and Guy Jones, worldwide technical sales executive, IBM Performance Management, will be discussing continuous planning and rolling forecasting in a webinar on Thursday, June 21, 2018 at 11am ET. They’ll explain how implementing a system of continuous planning and rolling forecasts can help your organization:

Increase the timeliness, accuracy and insightfulness of plans, budgets and forecasts
Address emerging threats and embrace new opportunities more proactively
Improve profitability and operating performance.

Jim and Guy will also show a demo of IBM Planning Analytics to show how multidimensional modeling is a key enabler of continuous planning, helping you update and revise your forecasts quickly and easily.

It’s time to leave the past behind and start your journey toward more efficient and effective business planning.

Learn more about continuous planning and rolling forecasting:

Beyond Budgeting: The Case for Rolling Forecasts

IBM Planning Analytics Tips & Tricks: How to Make Action Button Messages Dynamic

June 12, 2018 by Revelwood Leave a Comment

This is a guest post from Revelwood’s Jay Apwah.

Did you know that in Planning Analytics for Microsoft Excel (PAx) and Perspectives you can customize action button messages? Well you can go one step further and make those messages dynamic.

In this blog, we will walk through the following steps:

Setup cells in a PAx workbook for the custom messages.
Edit an action button to use the custom messages.

In this example, we have a PAx worksheet that allows users to click an action button to run a TI process using two parameters: the name of a file to load, the period to which the data will be loaded in a cube called bpmFinance. Whenever this process is executed, it logs statistics into a control cube: Number of records in the source file and Number of skipped records.

Below are the custom messages that we want to be shown to the user upon clicking the action button:

Confirmation message: “Are you sure you want to load the <file name> into the <period> period?”
Success message: “Process completed successfully – <X of X> records loaded”
Failure message: “Process failed. Records in file: <X>. Skipped Records: <Y>

Step 1: Setup custom messages

In any row that can be hidden, use a combination of Excel and TM1 worksheet functions to construct the custom messages. In this example, we will end up hiding rows 13-15. This is where our custom messages are constructed.

Figure 1: Example of formula to construct the confirmation message.

Formula:

=”Are you sure you want to load the “&C4&” file into the “&C5&” period?”

Figure 2: Example of formula to construct the success message.

Formula:

=”Process completed successfully – “&DBR(“<server>:bpmControls”, “Value”, “GL Actual File – Total Count”)&” of “&DBR(“<server>:bpmControls”, “Value”, “GL Actual File – Total Count”)&” records loaded.”

Note: The formula above retrieves the process statistic: “GL Actual File – Total Count” from a cube called bpmControls.

Figure 3: Example of formula to construct the failure message.

Formula:

=”Process failed. Records in file: “&DBR(“HQCorp:bpmControls”, “Value”, “GL Actual File – Total Count”)&”. Skipped records: “&DBR(“HQCorp:bpmControls”, “Value”, “GL Actual File – Skipped Count”)

Note: The formula above retrieves the process statistics: “GL Actual File – Total Count” and “GL Actual File – Skipped Count” from a cube called bpmControls.

Step 2: Edit action button to use the custom messages

In the properties of the action button that runs the TI process, click “Options”

The default success, failure and confirmation messages are displayed.

Since the custom confirmation, failure and success messages are in cells C13, C15 and C14, respectively. Change the default messages from hard-coded words to the appropriate Excel references as shown below.

Result

Dynamic confirmation message

Now when the “Run” action button is clicked, a confirmation message appears. The confirmation message is dynamic and picks up the parameter values entered/selected by the user.

Dynamic success message

When the confirmation is accepted, if the process completes successfully, the dynamic success message would appear. Notice how the success message displays the statistics that the process logged in the control cube.

Dynamic failure message

When the confirmation is accepted, if the process failed, the dynamic failed message would appear. Notice how the success message displays the statistics that the process logged in the control cube.

IBM Planning Analytics Tips & Tricks: Customizing PAW Directory Installation on Windows Server 2016

June 5, 2018 by Alex Needham Leave a Comment

Did you know you can customize IBM Planning Analytics Workspace directory installation? It’s getting more and more common to have a small C:\ drive for booting and then having a separate drive to store data. This approach makes it easier to have a backup work only with the relevant drive(s).

The Docker installation for Planning Analytics Workspace on Windows Server 2016 installs files to the C:\ directory without giving users the option to change the directory. Even if the PAW folder is unzipped to another drive, the images will still get loaded to the Docker default directory. This uses between 20 and 25 GB of storage when all is said and done!

You can, however, manually change these settings by manually creating a few files via the following steps:

Stop the docker service either via PowerShell command or in Task Manager.
In windows powershell, create a new daemon.json file by typing:
- New-Item -ItemType file -Name daemon.json -Path c:\programdata\docker\config
Open the daemon.json file and add the following content:{“graph”:”D:\\Docker”
}
- Note: Replace “D:\” with whatever drive you wish to use.
Restart the docker service either via PowerShell command or in Task Manager.Before:
After:

The main thing to note is that images will still be created in C:\ and then ported to D:\. For this reason, you’ll see ~20-25gb of caching when installing Planning Analytics Workspace. This means that you need at least 20-25gb of extra space on the C:\ drive. After the install, that space will open up again!

IBM Planning Analytics Tips & Tricks: Impact of Consolidations in Feeder Statements

May 29, 2018 by Revelwood Leave a Comment

This is a guest post from Revelwood’s Stephen Ellis.

Did you know writing feeders in TM1 is one of the more difficult tasks for any developer? One of the questions I am asked most often is “How do I know if I’m feeding this correctly?”

Today’s post will help you visualize the impact of utilizing consolidations in feeder statements. It will also help you interpret and review a feeder statement. These skills will help you develop rules within Planning Analytics. It can also assist you in reviewing your feeders with an eye for overfeeding.

The first example is for the feeder [‘Actual’,’Jan’] => [‘Forecast’,’Jan’]. This should be read as “Actual for January feeds Forecast for January.” Below is a visual representation of this feeder:

Learn about feeder consolidations in IBM Planning Analytics

Pretty straightforward, right? An individual month in one version feeds that same month in a different version (or Scenario).

If the feeder changed to [‘Actual’,’Jan’]=>[‘Forecast’,’Year’], which is read as “Actual for January feeds the Year for the Forecast,” what is the impact?

Learn the impact of consolidations on feeder statements

How did that one feeder statement create twelve separate feeders? Planning Analytics interprets any declaration of a consolidation in a feeder statement as an identification of every lowest level element within that consolidation. To clarify, in this example “Year” is the sum of all individual months.

Let’s review the previous statement. The feeder statement I previously declared was “Actual for Jan feeds the Year for the Forecast.” This can be rewritten as “Actual for Jan feeds all months within Year for the forecast.” Let’s do a few more examples to solidify this concept.

The next example is for the feeder “[‘Actual’,’Year’]=>[‘Forecast’,’Jan’]” This is read as “The year of actuals feeds January for Forecast.” Knowing that year is a consolidation of all months, we can change the interpretation to: “All months within year for Actual feeds January of Forecast.” Please see below representation.

Learn how consolidations impact feeder statements

This feeder statement also created 12 separate feeders, except that they all point to the same data point (January Forecast). In practice, this is an example of overfeeding, which can negatively impact the performance of your Planning Analytics application.

The last example is [‘Actual’,’Year’]=>[‘Forecast’,’Year’]. This is read as “The year of actuals feeds the year of Forecast,” and could be rewritten as “The months within Actuals feeds the months within Forecast.” This statement creates 144 (12×12) feeders, or the full combination of months. Below is an abbreviated representation of this feeder statement.

For illustration purposes, I’ve provided the first four months as impacted by this example.

With this understanding, you can read, write, and review your own feeder statements!

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Chart Types

May 22, 2018 by Revelwood Leave a Comment

This is a guest post from Revelwood’s Susan Musselman.

Did you know charts and graphs are an excellent way to illustrate relationships in data and highlight trends in IBM Planning Analytics Workspace? They are visualizations of data that allow end users to quickly analyze a large amount of data. To make the biggest impact, it is important to choose the right chart for the use case of the data. Following is a list of chart types available within Planning Analytics Workspace, along with use cases where one type might be better than another.

Area Charts

Chart types in IBM Planning Analytics Workspace

An area chart emphasizes the magnitude of change over time. It is good for showing trends. Example: show how sales values have changed over a number of years.

Bar Charts

Bar charts are used to compare different items, or to show a comparison of items over time. Compared to Column charts, Bar charts are good for displaying long data labels. An example would be a Top 10 Salesperson chart where the full names are listed.

Stacked Bar Charts

Stacked bar charts are designed to help you simultaneously compare totals and notice sharp changes at the item level that are likely to have the most influence on movements in category totals.

Column Charts

Similar to bar charts, but with a vertical orientation instead of horizontal. Column charts are good for displaying data sets with negative values. A good example would be a Regional Profit comparison chart where some regions have a loss.

Stacked Column Charts

Similar to column charts, but it shows multiple items in each column. Example: showing 4 quarters of Orders by Region.

Line Charts

Line charts show trends over time. The chart can show just one line or multiple lines, but the number of lines should be limited so the chart does not get too busy.

Map Charts

A map chart displays data based on geography. A good use of this chart type would be to show relative Revenue by State.

Packed Bubble Charts

packed bubble chart_ chart types in IBM Planning Analytics Workspace

This chart is a way to show relational value without regards to axes. The bubbles are in different sizes and colors. The x-axis represents one measure, the y-axis represents another measure, and the size of the bubbles represents the third measure. A good use would be Product Sales within Product Types. This chart is best for positive numbers.

Pie Charts

Shows each piece as a component of a larger part. Good for showing percentages as part of a whole. Does not show changes over time.

Point Visualizations

A point visualization uses multiple points to show trends over time. It is similar to a line chart, but without the lines; only the data points are shown.

Radial Charts

Shows progress toward a goal. Comparisons can be made by having multiple circles. Too many will be busy and ineffective.

Radial Bar Charts

This chart is pleasing to the eye, but it may be difficult to interpret. It is similar to a bar chart, but it is displayed in a circular fashion.

Tree Maps

A tree map identifies patterns and exceptions in a large, complex data set. It should not be used with negative numbers. It shows relationships among large numbers of components by using size and color coding in a set of nested rectangles.

Word Clouds

A word cloud is a visual representation of text values. The more frequently a text string occurs in your data, the larger the string appears in the word cloud.

When choosing which type of chart to use, don’t forget the purpose: to make it faster and easier for end users to understand the data. The less explanation it needs, the better.

IBM Planning Analytics Tips & Tricks: Regions with Planning Analytics Rules

May 15, 2018 by Lee Lazarow Leave a Comment

Did you know that comments are an important piece of good rule development in IBM Planning Analytics? Comments help to describe your logic and can make it easier to navigate through many lines of code. A special type of comment in Planning Analytics entails the definition of a region. The use of regions allows you to expand and collapse sections of rules in the Planning Analytics rule editor.

A region is defined by adding a comment that starts with #Region and ends with #EndRegion. The start of the region also allows you to describe the region via a RegionName.

Here is an example of a rule that includes a region definition. Note that the first line includes both the #Region comment and a description of the section.

Regions with IBM Planning Analytics Rules

Here is the same rule in the Planning Analytics rule editor. Note the grouping symbol (the triangle) next to line 6.

IBM Planning Analytics Tips & Tricks: Regions with Planning Analytics Rules

The section can be collapsed to make your rule easy to read. You can hover the mouse cursor over a collapsed region to show the contents as a tooltip.

This simple approach will allow you to make your rules easier to navigate and read when scrolling through many lines of code.

Top 5 IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: Planning Analytics for Microsoft Excel (PAx) VBA API Calls

Tech Bulletin: PAx Incompatibility Caused by Latest Microsoft Office Update

Embracing Continuous Planning

Learn more about continuous planning and rolling forecasting:

IBM Planning Analytics Tips & Tricks: How to Make Action Button Messages Dynamic

IBM Planning Analytics Tips & Tricks: Customizing PAW Directory Installation on Windows Server 2016

IBM Planning Analytics Tips & Tricks: Impact of Consolidations in Feeder Statements

Read more IBM Planning Analytics Tips & Tricks blog posts: