Budgeting Planning & Forecasting

IBM Planning Analytics Tips & Tricks: Workarounds for Unsupported Excel Functions in TM1 Web

August 7, 2018 by Nina Inverso Leave a Comment

Did you know there are a number of workarounds for unsupported Excel functions in TM1 Web?

Have you ever published a TM1 Web report, only to see repeating #NAME errors when you access the report online? These are typically caused by unsupported Excel functions. While TM1 Web does support most Excel functions, there are some that it does not. Here, I’ll demonstrate ways to work within these limitations by providing examples of workarounds for commonly-used unsupported Excel functions.

Function: ISBLANK

Description: Returns TRUE if the value is blank, FALSE otherwise

	A	B	C	D
1	Input	Original	Result	Compatible with TM1 Web
2		ISBLANK(A2)	TRUE	IF(A2=””, TRUE, FALSE)

Function: ISERROR

Description: Returns TRUE if the value is any error value, FALSE otherwise

	A	B	C	D
1	Input	Original	Result	Compatible with TM1 Web
2		IF(ISERROR(A2/A3), 0, A2/A3)	0.5	IF(A3=0, 0, A2/A3)

Function: ISEVEN

Description: Returns TRUE if the number is even, FALSE otherwise

	A	B	C	D
1	Input	Original	Result	Compatible with TM1 Web
2	1	ISEVEN(A2)	FALSE	IF(MOD(A2,2)=0, TRUE, FALSE)

Function: ISODD

Description: Returns TRUE if the number is odd, FALSE otherwise

	A	B	C	D
1	Input	Original	Result	Compatible with TM1 Web
2	1	ISODD(A2)	TRUE	IF(MOD(A2,2)=1, TRUE, FALSE)

Function: TRUNC

Description: Truncates a number to an integer

	A	B	C	D
1	Input	Original	Result	Compatible with TM1 Web
2	1.75	TRUNC(A1,1)	1.7	ROUNDDOWN(A1,1)

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!

IBM TM1 Tips & Tricks: When to Build Multiple Cubes

July 31, 2018 by Lee Lazarow Leave a Comment

When working with clients to design a TM1 system, I sometimes have clients ask about an approach of building one cube for a model vs. building multiple cubes for a model. What do I mean by that? A cube consists of a single table/database of data whereas a model consists of a series of cubes that are linked together.

TM1 is designed to quickly and efficiently link a series of small cubes together, but this style of thinking contradicts the approach that you may be used to within Excel. An Excel spreadsheet is a single table of data that offers the ability to link together more data via multiple tabs and multiple workbooks. This approach easily gives you the ability to start with a single set of data and then expand it.

What happens though, when you take this approach, is that you end up with your TM1 cube looking like your attic might if you just toss all your stuff in it. You might have old skis, camping gear, baby keepsakes, and more all together in one room. It makes it hard to find what you need and, in some cases, hard to know what you even have up there.

When you build multiple cubes, it’s like having an organized system to store all your stuff. You might have a closet dedicated to your sporting equipment. Camping gear may live elsewhere in a storage unit. Your family heirlooms and keepsakes are kept in water-proof containers in the garage. It’s much easier for you to find what you are looking for.

Revelwood’s approach to building TM1 models is to build small, efficient cubes that each have a separate role and purpose. These cubes link together to make a model. For example, say we are going to build a TM1 model to calculate compensation costs for a mid-sized national technology business. That model has three main components:

Data that gets entered by the planning team
Lookup tables for various rates and dates
Calculated results

Each cube stores only the data that is needed for its specific function and task. For example, there may be one rate cube for Federal tax rates and another cube for state tax rates. Everyone in the company has the same Federal tax rate for a single year whereas each state has a different rate. If you were to have one large cube for all tax rates – Federal and individual states – it would be hard to navigate and may require workarounds to make the data fit (for example, creating a “federal” state).

Building smaller cubes versus one large cube has benefits beyond those that are just about organizing your data. In fact, the organization part is really just a side benefit. When you have a model that consists of smaller cubes, you have a system that is not wasting space and provides you with a flexible foundation for your TM1 applications. Because your cubes are smaller, the entire system also performs faster.

Chances are you bought TM1 because you weren’t happy with Excel. Many Excel “systems” grow into multiple workbooks with many linked spreadsheets, which turns into a messy behemoth. Why would you want your TM1 environment to look like that?

IBM Planning Analytics Tips & Tricks: Upgrading from TM1

July 24, 2018 by John Pra Sisto Leave a Comment

Did you know the window of time for upgrading from TM1 to IBM Planning Analytics is shortening quickly? You need to upgrade to IBM Planning Analytics in order to complete your 2019 budgeting and planning activities. While that is likely the biggest reason to begin your upgrade now, there are many additional reasons to move to IBM Planning Analytics.

A new whitepaper from IBM outlines the powerful new features and enhancements you’ll experience when upgrading from TM1 to IBM Planning Analytics. Here’s a quick look at some of the compelling aspects of IBM Planning Analytics:

IBM Planning Analytics Workspace – the interface that revolutionizes TM1

IBM Planning Analytics Workspace is a customizable user interface that enables users to combine data from any cube in any TM1 database into a single view using a viewer, TM1 websheets, charts or cell widgets. You’ll gain productivity and save time by eliminating time-consuming data conversions.

Additionally, Planning Analytics Workspace (PAW) gives you:

Three forms of data selectors: tiles, lists, and sliders
The ability to have report authors and report consumers filter data-rich “books” with easily
Simple and streamlined navigation through multi-dimensional cubes
The ability to convert “data exploration” into charts you can use for presentations and to share with others (see our blog post IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Chart Types)

More flexible analytics with hierarchies

IBM Planning Analytics has a new “hierarchies” capability that delivers both technical advantages and end-user benefits. Hierarchies expands the traditional definition of cubes, letting you perform more in depth, granular analysis using “attributes” to describe the characteristics of customers, products, projects, and more.

Hierarchies enable you to create “virtual dimensions” that gives you:

Faster calculations, thanks to smaller cube sizes
The ability to create ad hoc hierarchies, which you can immediately introduce into Excel, web or mobile, for quick and convenient analysis
The ability to see the intersections of several hierarchies within the same dimension, enabling you to drill down to find data points never before possible

IBM Planning Analytics Tips & Tricks: Cleaning Up Your TM1 Environment

July 17, 2018 by Lee Lazarow Leave a Comment

Did you know there are some simple steps you can take to clean up your TM1 environment? A quick clean up gives you easier navigations for ad-hoc analysis, as well as smaller dimensions.

In today’s world, memory is cheap. Hard drives that used to be measured in KB quickly became MB, GB and TB. RAM chips have become larger and computers have become virtualized. This means that it’s easier to quickly expand the environment where your TM1 model is stored.

This also means that it’s easier to become lazy in terms of cleaning up your TM1 environment. You may often have “discussions” with your children about cleaning their room, so why not take the same approach with your TM1 environment?

There are many ways to quickly clean up your model. Some simple approaches can include:

A reduction in the number of scenarios/versions. Do you really need to save weekly forecasts from five years ago?
The removal of old data within a cube. Do you really need to reference your 2010 budget?
The removal of old cubes. Are there cubes that had a one-time purpose that are no longer used?
The removal of old processes. If you initially named a process “TEMP – xxx” then it was meant to be a temporary process.
The removal of “one off” reports. We worked with one client that trimmed a set of 1,000+ reports down to under 100 after analyzing which reports were being used.

Cleaning up your model will make your TM1 environment faster and easier to use. Tasks that take minutes can easily be transitioned into seconds. A smaller set of cubes can simplify the navigations for ad-hoc analyses and smaller dimensions can simplify your reports.

All of this leads to a better user experience… and happier users!

Excel Users Embrace IBM Planning Analytics

July 16, 2018 by Lisa Minneci Leave a Comment

Microsoft Excel is the tool CFOs and finance professionals love to hate. The problem is often a result of trying to use Excel for complex financial planning and analysis. Excel is simply the wrong tool for that job. In fact, a BARC survey found that specialized planning solutions offer users the following over Excel:

57% improved integration of planning with reporting and analysis
57% increased transparency of planning
53% better quality of planning results
51% more precise/detailed planning
43% increased planning frequency or faster planning
43% improved integration of different sub-budgets

Why use Excel for the wrong job? With IBM Planning Analytics, you get to keep Excel, but have a much more comprehensive, secure, and purpose-built solution for continuous planning, ad hoc analysis and more. According to a new report from BARC, “The Planning Survey 18,” IBM Planning Analytics delivers business benefits that include “flexible and comprehensive planning functionality with complementary options for Excel-based reporting and OLAP analysis.”

The report, which is the industry’s most comprehensive global survey of planning software users, was conducted from November 2017 to February 2018. In total, the company received responses from 1,465 and had 875 respondents answering a series of detailed questions about their usage of one of 17 named products or product groups. 126 people provided feedback on their use of IBM Planning Analytics.

The BARC research concluded IBM Planning Analytics is “Top-ranked” in 25 key performance indicators (KPIs) and a “Leader” in 13 KPIs. The report found that “better quality of planning results are achieved by an above-average proportion of Planning Analytics users compared to other planning products.” One of the 25 KPIs for which IBM was ranked on top is “project success.” The analysts at BARC state “the flexibility of Planning Analytics as an Excel-based development environment targeted at power users might also contribute to the success of projects because many different planning and OLAP analysis requirements can be addressed with the product.” They also cite the following reasons why users select IBM Planning Analytics:

Flexibility, performance and scalability, which led to a high overall product satisfaction rate
The ability to manage multiple sub-budgets, consolidating those results, and then running simple-to-highly complex calculations
It’s open for all kinds of applications, including planning, analysis, and strategy/management
Good coverage of planning-specific requirements
Customers can implement both centralized top-down planning and decentralized bottom-up planning
It provides functionality for various planning tasks and use cases, including creating random planning models on different aggregation levels.

It turns out that Planning Analytics is not only an ideal solution for those who hit the wall attempting to use Excel for planning, but it is also easy to implement and own. According to the analysts at BARC, “Resources and expertise for Planning Analytics are widespread in the market place. Many partners provide knowledge and consulting services for customers, and they seem to do a great job in their implementation projects… The company [IBM] empowers its implementing partners in a way its large international competitors such as SAP or Oracle cannot match.”

Lastly, the BARC research concludes that “problems encountered with the product are quite rare” and that 33% of customers say they have “no significant problems at all with the product.”

Nucleus Research Finds IBM’s Corporate Performance Management Solutions Focus on Personal Functionality and Usability

July 11, 2018 by Lisa Minneci Leave a Comment

Nucleus Research’s “CPM Technology Value Matrix 2017” named IBM a leader in Corporate Performance Management (CPM) solutions and found that “recent changes to its CPM products focus both on personal functionality and usability, empowering a greater diversity of individual users with more tools.”

Based on its assessment of 17 vendors offering CPM products, the research firm determined that there are two types of CPM products: “those that are tightly integrated with Excel and those that try to reimagine CPM through a different interface.” Nucleus Research considers IBM among those vendors who offer up an Excel-like experience, noting that IBM’s CPM offerings have “tighter Excel integration” and that “IBM has attempted to keep the comfort of Excel with the improved power of the IBM engine.”

The other vendors named as leaders include Adaptive Insights, Anaplan, Host Analytics, Info, Oracle and Vena Solutions. The report also covers Axiom Software, SAP, SAS, BOARD, CCH Tagetik, insightsoftware.com, Centage, Longview, Prophix and QPR.

IBM Planning Analytics Tips & Tricks: Year-to-Date Hierarchies

July 10, 2018 by Lee Lazarow Leave a Comment

Did you know you can create a single hierarchy that contains all year-to-date values within an entire year?

As you know, TM1 is a consolidation tool which is designed to sum together a series of values. There are many ways to create the consolidations using a combination of grand totals and sub totals. This blog specifically addresses the year-to-date (YTD) hierarchy associated with a time dimension.

Many clients prefer to create a single hierarchy that contains all YTD values within an entire year. The following image shows an example of this for a model that separates month and year as two separate dimensions:

Creating year to date hierarchies in TM1

This approach allows users to drill into the values one month at a time to show both the previous YTD value and the value for the month. But what happens if you want to see all of the details associated with YTD-Dec? If a user wants to see each of the 12 months and the total then the user is required to drill 12 times and also see a series of subtotals along the way.

Instead of creating a single hierarchy, Revelwood recommends that a series of YTD calculations are created. Each consolidated total consists of a single consolidated elements with a series of base level values below it. The YTD totals are then consolidated into a grouping-consolidation called “Total YTD”.

For example, a group called “Total YTD” will contain children called Jan YTD, Feb YTD, etc. Jan YTD will consist of one base level element (Jan) and Feb YTD will consist of two base level elements (Jan and Feb). The “Total YTD” element will never be used for reporting since it serves solely as a grouping element, but the other various YTD elements will allow user to click once to see all of the details within the total.

Learn how to create year to date hierarchies in TM1

This approach shows the same subtotals as the initial approach while simplifying the drilling requirements. As your users become more involved with ad-hoc analysis, they will appreciate the simplicity to dig into the details via a single click.

This method can also be expanded to use other time consolidations such as fiscal year (FY), ROY (rest of year) and TTM (trailing twelve months).

IBM Planning Analytics Tips & Tricks: Three Reserved Local Variables in TurboIntegrator

July 3, 2018 by Revelwood Leave a Comment

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

Did you know that there are reserved local variables that you can use in TurboIntegrator scripts?

Reserved local variables are variables that do not need to be defined and are destroyed when the process finishes executing. This blog will describe three variables: Value_is_String, nValue, and sValue. You will not find these on the variables tab and you cannot re-define them, but they are accessible. These three variables can be accessed within the Metadata or Data tabs of TurboIntegrator.

Value_is_String

This local variable can be used to test if the value of the data source cube view record that is currently being processed is a number. Value_is_String returns 0 (zero) if the value is a number. Otherwise, the value is a string.

NValue and SValue

In the variables tab of a standard TurboIntegrator process, you can define each variable as numeric or string. With this approach, however, you must set the variable type of the value to string. This is shown in variable #7 of the image below:

Example

In this example, assume the source view contains string and numeric measures. Rather than creating separate views that contain only numeric values or only string values, you can check to see if each value is a string or number. Line 7 in the image below shows an example of the check. Lines 8 and 10 show the appropriate functions and reserved variables to use. Note the distinction between sValue and nValue.

Reserved local variables in TurboIntegrator

In the variables tab, it was ok to set the variable type as string because TM1 knows that “sValue” means string and “nValue” means numeric and will therefore perform any required conversions.

As you can see, you can utilize the Value_is_String, nValue, and sValue local variables described above to create a more compact, efficient and less error-prone approach.

IBM Planning Analytics Tips & Tricks: TM1 Web Writeback Protection

June 26, 2018 by Revelwood Leave a Comment

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

Did you know that you can prevent data entry to TM1 in TM1 Web websheets? Here are a couple of different ways to do this.

The first approach can be used for input templates. Use this approach when you want your users to be able to enter data for certain cells/columns, but not others. By default, every cell in an Excel worksheet has the ‘Locked’ property set. This property has no effect on the cell unless the sheet or workbook is protected. In this example, we can select certain cells of an input template to be enterable and certain cells to be locked down or protected. To unlock certain cells in the template, right click the cells and select Format Cells. In the Format Cells dialogue box, select the Protection tab, uncheck the Locked checkbox, and select OK.

Now, in the ‘Review’ ribbon in Excel, select Protect Sheet, and then click OK.

Learn about TM1 Web writeback protection

Your worksheet is now protected. Any cell with the Locked property checked will prevent data entry to TM1 in TM1 Web. Any cell with the Locked property unchecked will allow data entry to TM1 in TM1 Web.

The second approach to disallow data entry to TM1 in TM1 Web can be used for reports. Use this approach when you don’t want your users to be able to enter data at all. To do this, in TM1 Server Explorer, expand Applications and navigate to the report you want to protect. Right click the report and select Properties.

In the TM1Web Properties box, there is a checkbox called ‘Allow Cell Write Back from Web’ that is checked by default. Unchecking this box will make the file read only and will prevent writing any data into TM1. Once you’ve made your changes, select OK.

Learn how to prevent data entry in TM1 Websheets

After making any of these changes, you must close the websheet in TM1 Web, refresh the navigation tree and reopen your report.

IBM Planning Analytics Tips & Tricks: Workarounds for Unsupported Excel Functions in TM1 Web

IBM TM1 Tips & Tricks: When to Build Multiple Cubes

Read more blog posts with TM1 tips & tricks:

IBM Planning Analytics Tips & Tricks: Upgrading from TM1

Read more blog posts on upgrading from TM1 to IBM Planning Analytics:

IBM Planning Analytics Tips & Tricks: Cleaning Up Your TM1 Environment

Read more blog posts with IBM Planning Analytics Tips & Tricks:

Excel Users Embrace IBM Planning Analytics

Read more analysts’ assessments of IBM Planning Analytics:

Nucleus Research Finds IBM’s Corporate Performance Management Solutions Focus on Personal Functionality and Usability

IBM Planning Analytics Tips & Tricks: Year-to-Date Hierarchies

Read more IBM Planning Analytics Tips & Tricks: