Financial Performance Management

Technical Bulletin: Security Change Impacts MDX

June 11, 2020 by Lee Lazarow Leave a Comment

IBM has decided to revert the change in behavior that is described in this tech bulletin. The change will be made in version 2.0.9.2 and it is expected to be released by the end of July. For more information, please reference IBM’s tech note at https://www.ibm.com/support/pages/node/6226890

We have an important technical bulletin regarding IBM Planning Analytics and MDX.

Version 2.0.9.1 of Planning Analytics changes the way MDX expressions are generated for dimensions that have security defined. This change may have a significant impact on your existing models.

Details

Prior to 2.0.9.1, when generating a subset list based on an MDX expression, Planning Analytics would execute the dynamic subset and then introduce security. Elements in the subset would be determined at runtime even if the user using the subset did not have READ access to a member that is named in the MDX statement. The list of elements in the subset was determined and then filtered by security. This resulted in the user only seeing the elements that were allowed.

As of 2.0.9.1, the system introduces security and then executes the dynamic subset. Users will need to have (at least) READ access to members that are named in the MDX statement. Just as a user could not determine the descendants of a consolidated element in any other user interface (e.g., the hierarchy or set editor) they will not be able to determine descendants of the member by using MDX. This may result in users not seeing any elements in the resulting expression.

For example:

A model contains a dimension named “Company”. The dimension has a consolidated element named “Total Company” with child elements “A”, “B”, and “C”.
There is a dynamic subset which returns the base level descendants of “Total Company” via the following expression:

{ TM1FILTERBYLEVEL ( { TM1DRILLDOWNMEMBER( { [Company].[Total Company] }, ALL, RECURSIVE ) } , 0)}

A user only has security access to company A via element security.
- Prior to 2.0.9.1, the result would be that the user sees company A.
  - This is because Planning Analytics created the dynamic subset which included all the elements and then applied security to only allow the user to see company A.
- As of 2.0.9.1 the result does not include any elements in the subset.
  - This is because Planning Analytics tried to create the subset by starting with Total Company, realized that the user has no access to Total Company, and therefore did not have anything to expand.

Recommended next steps:

Revelwood recommends a review of your system to determine where MDX expressions are used. This includes all dynamic subsets and all dynamic reports/templates (i.e., Active Forms / Dynamic Reports).

Revelwood has created a Turbo Integrator script that will help you assess potential conflicts by analyzing combinations of element security and dynamic subsets. You can download the script for free.

Reviewing your system now will reduce the potential for your end users having problems seeing elements. By being proactive, you have the opportunity to stave off headaches for yourself.

Please contact John Pra Sisto at jprasisto@revelwood.com if you have any questions regarding this bulletin or the Turbo Integrator script.

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

June 9, 2020 by Lee Lazarow Leave a Comment

Many of you already know about Excel’s DATE function. The function is used to create a date by defining specific values for the year, month and day.

DATE(2020,7,20) gives a result of 7/20/2020.

And some of you know that you can also use formulas to calculate a date.

If a value of 5 exists in cell C4 then

DATE(2020,7,20+C4) gives a result of 7/25/2020.

But did you know that the formula will also check for valid dates and, if needed, shift the month automatically?

DATE(2020,7,35) gives a result of 8/4/2020

(since there are not 35 days in July!)

and

DATE(2020,7,30+C4) gives the same result

Not only can this formula be used to calculate variable dates, but it will also ensure that you don’t incorrectly calculate a value that doesn’t exist on the calendar. After all, we wouldn’t want Julius Caesar coming back to question our use of “his” month!

IBM Planning Analytics, which TM1 is the engine for, 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!

FP&A Done Right: What Must FP&A Do Differently to Make Planning a Success

June 5, 2020 by Revelwood Leave a Comment

This is a guest blog post from our partner Adaptive Insights, written by Anders Liu-Lindberg. Lui-Lindberg explains why FP&A can no longer take a narrow view of its own role.

FP&A is obviously concerned with financials; however FP&A can no longer take a narrow view of its own role. FP&A must go way beyond the financials to where the business happens to succeed in making planning a success!

We discussed in my previous post the notion of active planning and made it concrete using a specific example. Now we’ll take it one step further and discuss how you can only realize active planning if you integrate your planning process with business operations.

In the end we’ll tie it all together by explaining how you can now build a driver-based planning process that ties your strategic intent together with your daily execution. I know that’s a stretch to most FP&A professionals, but with active planning it doesn’t make sense any other way than to make your planning driver-based.

External factor to business drivers to financial drivers

I think we can all agree that business doesn’t start with financials. In fact, it ends with financials, when every transaction eventually gets recorded through debit/credit. So how could we ever start our planning process with the financials or think that by extrapolating current financials with a growth factor or similar that we would get a decent picture of what will happen in the future? No, we must flip our thoughts on planning around. Here’s how:

We must look at the external factors that impact our business and are documented as critical assumptions as part of our strategy
Next, we must look at the key business drivers that determine if we’re successful or not
Only then do we start to look at the financials, because they’re the most lagging indicator we have

In short, external factors are leading indicators to business drivers, which in turn are leading indicators to financial drivers. Now it’s important that you only select the most critical ones, say six to eight in each category, because otherwise you’ll have a hard time describing how each factor/driver impacts the other. You’ll also have a hard time producing any meaningful monitoring system or planning process.

It’s clear that the more variables you can add to the equation the more precise you’ll likely be; however, to exercise active planning, an 80/20 approach is much better than thinking you need 99% accuracy in everything you do.

Almost real-time driver-based planning

Now let’s connect the dots. You’ve defined six to eight drivers at each level of external, business, and financial. You should now connect these drivers so you have an idea about how a change in one will change the other. You might need to use some machine learning to build a proper model, but once it’s built, you just need to link the financial drivers to your P&L, balance sheet, and cash flow (depending on how much detail you want to plan for).

Now this is real active driver-based planning that essentially gives you an updated view on your business whenever something happens in your critical assumptions that are tied to your strategy. I can imagine an alarm bell going off in every CXO’s office every time any of the drivers moves outside the comfort zone. Luckily for the CFO though, sharing the financial impact of not acting is no longer a headache.

How does this compare to your own vision for creating an active planning process? Have you already started some sort of driver-based planning? How connected is it among the three levels? Now is the time to get this done so we can start to focus on making the right decisions given the change in assumptions. Are you on board with the needed change?

Anders Liu-Lindberg is a senior finance business partner at Maersk and the co-founder of the Business Partnering Institute. He is also the co-author of the book Create Value as a Finance Business Partner and a longtime finance blogger with more than 33,000 followers.

This blog post was originally published by Adaptive Insights.

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

June 2, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I demonstrated the concept of using words to manipulate your views. This approach also applies to entering data. All of you know that you can enter data by typing a number into a writeable cell.

IBM Planning Analytics Tips & Tricks: Using Words and Numbers

Some of you know that you can use commands such as “2k” to enter a value that will be converted into a number.

IBM Planning Analytics Tips: Using Words and Numbers

And some of you also know that you can type commands such as “grow10” on an existing number to populate the rest of the year.

IBM Planning Analytics Tricks: Using Words and Numbers

But did you know that you can merge all of this together into a single entry? For example, if I type “5kgrow20” in January then 5,000 will be input into January and a 20 percent growth rate will apply to each of the other months.

Using words and numbers in IBM Planning Analytics

The combination of numbers and phrases in a single cell will help you quickly and easily create plans for multiple time periods.

IBM Planning Analytics Tips & Tricks: The Data Load Process

May 26, 2020 by Lee Lazarow Leave a Comment

In an IBM Planning Analytics Tips & Tricks video, I showed how to use a drag and drop approach to load data into a cube. Since that time, the wizard has been updated to allow you to save your import as a new TurboIntegrator process and to also schedule the process within a chore.

After dragging the file onto the cube, the first screen that appears is a confirmation of the file with some file-based setup aspects such as the delimiter, the quote character and the number of header rows:

IBM Planning Analytics Tips & Tricks: The Data Load Process

After clicking the continue button, the second screen appears which allows you to define the columns and aspects associated with the data load (before and during):

The Data Load Process in IBM Planning Analytics

The bottom, right corner of this screen allows you to save the import as a process and/or a chore:

IBM Planning Analytics Tips: The Data Load Process

Once executed, the process will be saved and can be referenced using PAW’s process functionality:

IBM Planning Analytics Tricks: The Data Load Process

This approach within Planning Analytics allows you to use a drag and drop approach to create and save Planning Analytics code.

FP&A Done Right: 3 Words for a COVID-19 World – “Flexible Budget Variance”

May 22, 2020 by Revelwood Leave a Comment

This is a guest blog post from our partner Adaptive Insights, written by Bob Hansen. It is part of a series of blogs from Adaptive Insights designed to help customers weather the storm brought by the COVID-19 pandemic.

With the COVID-19 pandemic shredding budget forecasts and presenting FP&A professionals with actuals that are nowhere close to original expectations, now is the perfect time to get acquainted with a certain term: “Flexible budget variance.”

Sure, flexible budget variance might sound wonky. But now more than ever, it’s an essential tool for modern FP&A teams. Here’s why.

Flexible budgeting not only helps you stay current with the challenges and opportunities that surface throughout the year, but it can be a lifeline when your business is rocked by revenue shocks, drops in demand, workforce shifts, and whatever else a global event can toss your way. By updating budgets to reflect those changes, you can quickly course correct to improve efficiency or enhance performance.

What is a flexible budget variance?

Flexible budget variances are the differences between line items on actual financial statements and those that are on flexible budgets. Since the actual activity level is not available before the accounting period closes, flexible budgets can only be prepared at the end of the period. At that point, flexible budget variances can be useful in identifying any shortcomings or deviations in actual performance during a given period.

Though powerful anytime, you can imagine how useful this capability would be now, with so much disruption to normal course of business activity. And it’s a safe bet that business planning and budgeting overall will be subject to rapid and ongoing course correction for months to come.

Flexible budget variance is also beneficial during the planning stage at the beginning of the accounting period. By adjusting project budgets to a series of possible activity levels, Finance creates data that helps anticipate the impact of changes in activity levels on revenues and costs. This helps you make more informed decisions if (or when) adjustments are needed.

Taking a flexible approach to budgeting typically doesn’t mean you get a free pass when it comes to more traditional, static budgeting. In fact, the static budget is essential for establishing a baseline to measure performance and results and ultimately for calculating the variances that do occur throughout the year.

Save time by using the tools you have

The task of calculating, analyzing, and then clearly communicating budget variances and their implications can be a time-consuming task under any circumstances, and particularly stressful in times of disruption. But certain capabilities in Workday Adaptive Planning make it easier.

For instance, Workday Adaptive Planning’s data visualization software can speed much of that process. And when conditions change quickly, speed is a distinct advantage.

Even so, it’s important to keep in mind that not all line items in a budget can be flexible. For example, your company has many expenses that are likely fixed for the entire year, such as rent or contractual obligations.

Yet other expenses have considerable chance of varying to one degree or another. For instance, staffing projections may be dependent on an expected long-term contract being finalized, or economic stresses cause you to extend payment deadlines or loosen return policies. No matter what, flexibility serves you at the moment you need it—and pays dividends down the line.

Gain meaningful insights

Meanwhile, flexible budget variance analysis offers the ability to derive meaningful insights throughout the year, allowing for improved planning and budgeting for the future. The power and potential of flexible budgets are further fueled by technology platforms such as those offered by Workday that provide drill-down capabilities so you can quickly identify and analyze variances.

You can also use Workday Adaptive Planning to create a variance report that highlights the changes in dashboards, offering a range of visual options for presenting the numbers within highly accessible context.

And by relying on more timely and relevant budget numbers, you can use flexible budgets to provide senior executives and line of business managers with dynamic guidance on spending, investments, or where cost controls might be necessary based on the situation your business faces as days, weeks, and months progress.

You’ll get through this chaos by leveraging the benefits of flexible budget variance capabilities within Workday Adaptive Planning, you even might get through it in a stronger position than your competitors.

This blog post was originally published by Adaptive Insights.

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

May 19, 2020 by Lee Lazarow Leave a Comment

Rules in IBM Planning Analytics allow you to reference components of a cube as part of your formula … either from the cube you are currently in or from another cube. This is done via the use of the DB function. The syntax of the DB function is:

DB(cube, elem1, elem2, …)

Where each elem value references an element from the dimensions of the cube, in the order of the cube definition.

This is a great formula if you know all the dimensions of your cube in the proper order. But what if you don’t want to have to define every dimension in your cube?

Planning Analytics also allows you to use a formula called CellValueN. The syntax of the CellValueN function is the same as the other formula:

CELLVALUEN (cube, elem1, elem2, …)

… but there are some differences when using this formula.

You do not have to reference every dimension! Any undefined dimensions are the equivalent of using the exclamation point, often called a “bang.” This means that you only need to reference dimensions/elements that are different from the source value.
Since you are not putting your dimensions in order, you must reference the dimension name in front of each element. The dimension name must be written within a set of single quotes, a colon is then used without quotes, and the element is then written within another set of single quotes.

For example, account 605100 would be written as ‘Account’:‘605100’

There are different versions of the rule. One version is CellValueN which pulls numeric values and one version is CellValueS which pulls string values.

Here is an example of the rule. Note that the cube contains seven dimensions but the formula only references two of them.

IBM Planning Analytics Tips & Tricks: CellValueN Formula

This approach allows you to simplify your rules by referencing a data point without having to reference every dimension within the cube.

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

May 12, 2020 by Lee Lazarow Leave a Comment

Most of you know the concept of an IF statement within TurboIntegrator

IF (expression);
statement1;
ENDIF;

… and most of you also know the concept of nested-IF statements

IF (expression);
statement1;
ELSEIF (expression);
statement2;
ENDIF;

… and most of you also know the concept of generically using an ELSE command as a catch-all

IF (expression);
    statement1;
ELSEIF (expression);
    statement2;
ELSE;
    statement3;
ENDIF;

But did you know that Planning Analytics has a maximum number of nests that can be included within a TurboIntegrator process? You can nest up to 20 ELSEIF/ELSE statements in a process. If you exceed 20 nested statements then you will receive an error when attempting to save the process.

If you come across situations where you are using a lost of nested IF statements, then there may be a way to re-assess the expressions that are being used. Give us a call!

Tech Bulletin: New IBM Planning Analytics Features for 2020

May 11, 2020 by Lee Lazarow Leave a Comment

IBM introduces new features for Planning Analytics in releases throughout the year. We publish IBM Planning Analytics Tips & Tricks on these features every Tuesday in our Knowledge Center. If you would like to receive these in your inbox every Tuesday, you can subscribe here.

Here we list out which useful features have been released so far this year for PAx and PAW.

New PAx Features in 2020

2.0.50 (Feb)
Design Mode can be used to enable or disable the refreshing of an Exploration View upon every change. As of 2.0.50, you can now also use Design Mode to preserve or clear DBRW formulas when you are copying and pasting values into cells.

2.0.51 (Mar)
As of version 2.0.51, you can now bulk update the data source or package for multiple Exploration Views at the same time.

2.0.52 (Apr)
Various improvements were made to the set editor to make it easier to work with large dimensions. These include the ability to focus on one area at a time, have easy access to various functions, perform drag and drop functionality, and define default settings for the available members and current set panes.

New PAW Features in 2020

2.0.48 (Jan)
Ability to abbreviate numbers to thousands or millions within the Set Format options

2.0.49 (Feb)

Changed hierarchy sorting to an alphabetical approach
Ability to enable security access to processes

2.0.50 – 2.0.52 (Apr)

Set editor improvements (see Pax 2.0.52 for details)
The AgentActions.log file can be used to review the user and time associated with these database actions: start, stop, restart, and end process
Modified various icons and the settings within the refresh menu

If you have any questions about these features, please feel free to reach out to me at llazarow@revelwood.com.

Technical Bulletin: Security Change Impacts MDX

Details

Recommended next steps:

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

Read more IBM Planning Analytics Tips & Tricks:

FP&A Done Right: What Must FP&A Do Differently to Make Planning a Success

External factor to business drivers to financial drivers

Almost real-time driver-based planning

Read more guest posts from our partner, Adaptive Insights:

IBM Planning Analytics Tips & Tricks: Using Words and Numbers Together

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Data Load Process

Read more IBM Planning Analytics Tips & Tricks:

FP&A Done Right: 3 Words for a COVID-19 World – “Flexible Budget Variance”

What is a flexible budget variance?

Save time by using the tools you have

Gain meaningful insights

Read more FP&A Done Right posts:

IBM Planning Analytics Tips & Tricks: The CellValueN Formula

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Max Number of ELSEIF

Read more IBM Planning Analytics Tips & Tricks:

Tech Bulletin: New IBM Planning Analytics Features for 2020

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Financial Performance Management

Details

Recommended next steps:

Read more IBM Planning Analytics Tips & Tricks:

External factor to business drivers to financial drivers

Almost real-time driver-based planning

Read more guest posts from our partner, Adaptive Insights:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

What is a flexible budget variance?

Save time by using the tools you have

Gain meaningful insights

Read more FP&A Done Right posts:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT