TM1

IBM Planning Analytics Tips & Tricks: Unpivot

January 21, 2025 by Ivan Cepero

Did you know you can unpivot data in IBM Planning Analytics?

Sometimes you’ll find yourself in a situation where you need to change the layout of data from a grid format to a vertical table format, this is called unpivoting the data.

I’ll show you how to do this via some new Excel formulas.

Consider this sample data set:

A screenshot of a spreadsheet

Description automatically generated

The goal is to have a table with three columns: Invoice, Month, Data. Here’s how to do that step-by-step.

Step 1

Use the EXPAND formula to create an array of Invoices. In this case, we are creating a 5 x 12 array (5 rows x 12 columns).

=EXPAND($A$2:$A$6,5,12)

This will generate this:

A white grid with black and green text

Description automatically generated

Step 2

EXPAND doesn’t know what to do with the other columns, so we can use IFERROR to fill in the errors with a specified value. In this case, if there’s an error then we want to replace the error with the first column value.

=IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6)

Now we have this:

A screenshot of a computer

Description automatically generated

Step 3

Now we use the TOCOL function to make the grid of invoice numbers into a column

=TOCOL(IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6))

That generates:

A number of binary code

Description automatically generated with medium confidence

Step 4

We repeat the previous steps for the months. We want an array of 12 x 5 for the months. The end formula is:

=TOCOL(IFERROR(EXPAND($B$1:$M$1,5),$B$1:$M$1))

Step 5

Use TOCOL on the data points to pivot them into a columnar format.

=TOCOL($B$2:$M$6)

Now we have a live, formula-based unpivot model that will refresh when the data is changed.

Bonus Tip!

You can make the formulas more dynamic by using COUNTA instead of hard coding the number of rows/columns in the EXPAND function.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: PAW Export Data

January 14, 2025 by Lee Lazarow

While IBM Planning Analytics is an excellent database that allows you to easily slice and dice your data, there may be situations where you want to export data from the model. Maybe you want to share information with someone who uses different software or maybe you want to use your data in another tool? PAW version 99 introduced the ability to export a data set from the PAW interface.

To export data, right-click on a cube within a modeling workbench, select the option to Import/Export and then select the option to Export Data.

Once selected, you will be prompted to define the name of the .csv file that will be created. You then can define the data to be exported by using an existing view and/or by defining the dimension details. For each dimension, you can define a hierarchy to use, a subset to use, or an element to use.

You also have the ability to define filters for your numeric and string data (example: only show values greater than or equal to 100) and the ability to skip consolidated values, rule calculated values, and zero/blank values.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

January 7, 2025 by Revelwood

Did you know that almost 80% of all New Year’s resolutions are associated with health? As an IBM Planning Analytics user, it’s important to also think about your system’s health! This is why you should put “upgrading Planning Analytics” on your list of resolutions. Here’s why …

IBM recently announced some changing timelines to the forthcoming deprecation of Planning Analytics 2.0.9.x. For background, read our blog post, IBM Planning Analytics Tips & Tricks: Upcoming Changes to Planning Analytics, or watch our on-demand webinar, Understanding the Migration from Legacy TM1 to Planning Analytics.

Key Updates

IBM is extending the general support period for Planning Analytics 2.0.9.x until October 31, 2025.
Users can purchase extended support from IBM for an additional year through October 31, 2026.
Planning Analytics 2.1.x is now supported until April 30, 2027.

Implications for Users

Staying on unsupported versions can expose organizations to security vulnerabilities, lack of technical support and missed opportunities for leveraging new functionalities. You should assess your current Planning Analytics environment and develop a roadmap for upgrading to supported versions.

Recommended Actions

1. Evaluate Current Usage: Understand how your organization uses Planning Analytics and identify any customizations or dependencies that may affect the upgrade process.
2. Plan the Upgrade: Develop a comprehensive plan that includes timelines, resource allocation and testing phases to ensure a smooth transition.
3. Engage with Revelwood: We can help you access support resources and documentation to assist with the upgrade process and address any challenges that come up.

By proactively addressing these end-of-support dates, organizations can maintain the integrity of their financial planning systems and continue to benefit from IBM’s advancements in Planning Analytics.Need help?

Send a quick note to: info@revelwood.com.

Once selected, you will be prompted to define which hierarchy to export and the name of the csv file that will be created. The resulting file will contain the following information for each element:

Name
Parent (root elements will have a blank parent field)
Type
Weight
All defined attribute names and values (the attribute name will be prefixed with ATTR_N or ATTR_S to define the attribute as numeric or string)

Here is a snippet of an export file that shows a header record, a consolidated element, and two children:

“Member”,”Parent”,”Type”,”Weight”,ATTR_S_Manager”,”ATTR_S_Currency”

“Total Company”,””,”Consolidated”,”0″,”Total”,””

“001”,”Total Company”,”Numeric”,”1″,”John Smith”,”USD”

“002”,”Total Company”,”Numeric”,”1″,”Jane Doe”,”USD”

This feature will allow you to export your dimensional structures to files that can be used for reporting, auditing, and other areas where you need to display your structural information.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: PAW Selector Menu

December 10, 2024 by Lee Lazarow

You may already know that you can create a selector widget in IBM Planning Analytics Workspace (PAW) by simply dragging a context dimension outside your existing view. Once synchronized, this approach provides an easy way to create independent selectors that can be used with your existing PAW data pulls.

But what do you do in a situation when you have already hidden your exploration overview or converted your exploration into a chart? One approach is to reset the visualization into a fully expanded exploration, drag the dimension, and re-format the visualization. But did you know that an easier way exists?

PAW version 99 introduced an option to add a selector directly from the data tree (e.g., the left pane). This can be accessed by simply right-clicking on a dimension in the tree. Once selected, there will be two options: add selector tile and add selector list.

There are three types of tiles: single tile, multiple tiles, and range selection tile. There are three types of lists: single list, multiple lists and multiple selection list.

Once created, the properties area will allow you to define appearance aspects such as colors, fonts, and header information.

This approach will allow you to create easy-to-use selectors without having to undo any existing formatting that has been created.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: PAW Thumb Cell Formatting

December 3, 2024 by Lee Lazarow

Have you ever created an exploration in IBM Planning Analytics Workspace (PAW) and noticed the arrows at the beginning of your rows and columns? These are called thumb cells and are used as the equivalent of a “select all” button. For example, if I click on the down arrow at the top of my rows, then all elements in the rows will be highlighted.

Below is an example of a view with two thumb cells: a row thumb that I clicked on (note the arrow pointing down) and a column thumb that I did not click on (note the arrow pointing right).

Sometimes a thumb cell is great since it allows you to reference all of the elements in your exploration, but sometimes you may not want to see these additional gray boxes on the screen. PAW version 99 introduced formatting options to modify the appearance of the thumb cells.

Within the format manager, you can modify the thumb options in both the column header and row header settings.

Below is the same view with the row thumbs not showing and the column thumbs not showing the icon. You can see that that there is no longer a gray box above the rows and there is no longer an arrow icon to the left of the columns.

A screenshot of a data

Description automatically generated

These formatting settings will allow you to customize the appearance of an exploration and, if preferred, eliminate boxes that may confuse your end users.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: High Availability in Version 12

November 19, 2024 by Revelwood

IBM’s Planning Analytics version 12 introduces a new feature known as high availability. It enables you to manage multiple database replicas in parallel, thereby enhancing both performance and reliability.

Revelwood’s IBM Planning Analytics Practice Director Lee Lazarow explains high availability in one of our IBM Planning Analytics Tips & Tricks videos.

How High Availability Works

Load balancing and fault tolerance is at the core of high availability. When a user submits a request—be it updating a data point, executing a TurboIntegrator script, or conducting some other processing—the request is directed to one of the database replicas, known as the leader replica.

1. Role of the Leader Replica:
- The leader executes all required changes, performs calculations, and manages processing tasks.
- Once the leader completes these tasks successfully, it shares/sends the changes to all other replicas, ensuring that they are kept in sync.
2. Load Distribution:
- By distributing user requests across multiple replicas, the system mitigates the risk of a single heavy processing task impacting overall performance. This load spreading enhances the user experience, particularly in environments with fluctuating demands.
3. Active Management of Replicas:
- The engine takes an active role in monitoring and managing the health of each replica. If the leader becomes unavailable for any reason, another instance is automatically promoted to take over its responsibilities, ensuring continuity of service.
4. Dynamic Scaling:
- One of the standout features of high availability is the ability to dynamically adjust the number of replicas based on current demands—all without requiring downtime. This flexibility is particularly useful for organizations with varying usage patterns:
  - During peak periods (e.g., month-end closings, budgeting cycles), you can scale up the number of replicas to accommodate increased user activity.
  - Conversely, during quieter periods, you can scale down, optimizing resource usage and costs.

Managing High Availability

Managing high availability in Planning Analytics is straightforward and user-friendly. Here’s a brief overview of how to configure and monitor this feature:

1. Accessing the Management Interface:
- Within the administration interface, you can easily enable or disable high availability by selecting the relevant checkbox.
2. Configuring Replicas:
- You can define the number of replicas you wish to create. As you increase the number of replicas, the system automatically adjusts memory usage based on your configuration. For instance:
  - If the original database requires 4 GB of memory, adding a replica would require an additional 4 GB.
3. Real-time Monitoring:
- The system provides real-time feedback on memory usage and warns you if you attempt to allocate more replicas than your available resources can support. This proactive management ensures that you maintain optimal performance without overloading the system.

IBM Planning Analytics version 12’s high availability feature is an advancement in how organizations can manage their Planning Analytics environments. Users achieve greater efficiency, reliability and scalability by leveraging multiple replicas.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: ASCIIOUTPUT

November 5, 2024 by Revelwood

One of our recent IBM Planning Analytics Tips & Tricks videos and blog posts focused on IBM Planning Analytics file manager, and how it can be used to export files. With IBM Planning Analytics, version 12, you can use that approach. But you can also still use ASCII output to create files.

The change with version 12 is that the output path is going to be a little different. Watch Lee Lazarow, Revelwood’s IBM Planning Analytics practice leader, explain how to use ASCII output in version 12.

Lee shows you how to:

Modify a TurboIntegrator script for specific paths
Create new folders with ASCII output

You’ll also learn the pros and cons of automatic folder creation.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: Unpivot

Step 1

Step 2

Step 3

Step 4

Step 5

Bonus Tip!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Export Data

Read more IBM Planning Analytics Tips & Tricks: