IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Workspace Settings – Dim Editor Index

April 1, 2025 by Lee Lazarow

Have you ever looked at all elements in a dimension (via the “All” button in Perspectives or any of the prebuilt “All xxx” sets in IBM Planning Analytics) and wondered how the resulting order was defined? This is a result of a behind-the-scenes setting called an Index. Every element is assigned an index. It is used to define the default sorting / appearance of the list.

So how do you see the index number in PAW when editing a dimension? There are two ways to do this. One approach is to manually turn on the option within the dimension editor. This is done by clicking on the settings icon and enabling the option for Element index.

Once enabled, the index number will appear directly to the right of the element.

The second approach is to always have this setting enabled. Users with the Administrator, Modeler, or Analyst role can use the Settings editor to define global settings. To enable this setting, click on your username at the top right corner of Planning Analytics Workspace, select the option for Profile and Settings, and then select the option for Settings. The index definition is found within the Dimension Editor section. After enabling this setting, the Element Index option will be selected by default within the dimension editor screen. Users can still manually deselect the option, if preferred.

By viewing the index, it will be easy to see the order that elements will appear for various default sets.

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 Dim Structure

December 17, 2024 by Lee Lazarow

Have you ever wanted to export a dimensional structure to an ASCII based text file in IBM Planning Analytics Workspace (PAW)? PAW version 99 introduced the ability to export a dimension with a single click.

To export a dimension, right-click on the dimension name within a modeling workbench and select the option to Export members.

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: VSTACK_HSTACK

July 30, 2024 by Ivan Cepero

In a previous blog, I wrote about the ability to convert a list into a set of smaller lists (create a link to the WRAPCOLS blog). But what if you wanted to do the opposite? What if you wanted to merge a set of smaller ranges into a single range? Excel’s VSTACK and HSTACK formulas are an easy way to combine several non-contiguous ranges into one range.

The syntax of the VSTACK formula is:

=VSTACK(array1,array2,…)

Each array parameter represents a range to be stacked

Using the following data as an example:

A screenshot of a computer

Description automatically generated

We can stack both ranges on top of each other using the following formula:

=VSTACK(A1:D4,A9:D11)

A screenshot of a data

Description automatically generated

This example was relatively simple since both ranges have the same number of columns. However, if one range has less columns then each cell mismatch will generate an error. For example, taking all three columns of the first data set but only two columns from the second data set will generate this result:

=VSTACK(A1:D4,A9:B11)

A screenshot of a table

Description automatically generated

We can remove the errors by nesting the VSTACK within an IFERROR formula:

=IFERROR(VSTACK(A1:D4,A9:B11),””)

We can also use the HSTACK formula if you prefer to append your data horizontally instead of vertically.

To combine both these tables into one table, this HSTACK formula will do the trick:

=HSTACK(A1:D4,G1:I4)

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!

IBM Planning Analytics Tips & Tricks: Excel’s WRAPCOLS Formula

June 4, 2024 by Ivan Cepero

Have you ever needed to modify a list in Excel without wanting to manually manipulate the data? Excel’s WRAPCOLS formula lets you reshape data without having to perform tedious copy and pasting.

The syntax of the WRAPCOLS formula is:

=WRAPCOLS(vector, wrap_count, pad_with)

Vector: the data to be modified
Wrap_count: the number of rows per group
Pad_width: the data to fill in for missing values

For example, suppose you have a list of 10 people that you want to split into three groups.

The manual way of copy/pasting would work for a small list like this, but this approach would not be efficient for larger lists.

To make a set of three groups, you can use this formula:

Excel will then determine how to fill the groups specified. However, the remaining blank cells appear as #N/A which may cause some user confusion. You can remove the #N/A data cells by using the pad width parameter. To display blanks instead of #N/A, the formula would change to:

This approach will allow you to convert a long list into a more easily readable set of smaller list, thereby making it easier to analyze the information.

IBM Planning Analytics Tips & Tricks: Identifying a Specific Dimension

May 7, 2024 by Lee Lazarow

Have you ever wanted to change (or delete) a dimension, but first wanted to know how this action will impact your model? IBM Planning Analytics Workspace (PAW) allows you to quickly identify all the cubes in a database that include a specific dimension.

Since this is a development task, this approach is defined within a modeling workbench. You can access this option by right-clicking anywhere you see a dimension name within the databases tree (cubes, dimensions, control cubes, and control dimensions) and selecting the option to “View related cubes.”

The resulting list will define the full dimension name (e.g., not the caption) and show all the cubes that include the selected dimension.

If you want to better understand the data in the cube, you can then review any of them by clicking on the cube name and then selecting the option to Open the cubes. An exploration view for each cube will open on separate tabs in the modeling workbench.

This approach will give you a fast way to determine the overall impact of a dimension change.

IBM Planning Analytics Tips & Tricks: Excel Substitute Function

October 31, 2023 by Ivan Cepero

Are you familiar with the SUBSTITUTE function in Excel? The SUBSTITUTE function in Excel replaces one value with another. This is similar to the REPLACE function, but SUBSTITUTE provides more flexibility.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(text,old_text,new_text,instance_num)

Text: This is the text to be evaluated and changed
Old_text: This is the subtext to be replaced.
New_text: This will be used as the replacement.
Instance_num (optional): This defines which occurrence of old_text to replace. If this value is blank then all occurrences of old_text will be replaced.

Here are two examples of the substitute function:

Example 1:

This formula removes the second dash from the account string.

Graphical user interface, application

Description automatically generated

Example 2:

To count the number of dashes in a cell, this formula subtracts the length of the cell without dashes from the original length of the cell.

IBM Planning Analytics Tips & Tricks: Cell Security in Planning Analytics Workspace

October 17, 2023 by Revelwood

In our latest IBM Planning Analytics tips & tricks video, Lee Lazarow, Revelwood’s IBM Planning Analytics practice lead, explains and demonstrates cell security in IBM Planning Analytics (PAW). Cell security is a critical aspect of data management, providing control access to specific data points within PAW cubes.

Watch this video and learn how to:

Determine the cube you want to add security to
Choose the dimensions
Create the cell security
Define security manually
Automate security with rules
Save and apply rules
Make modifications

In conclusion, cell security in IBM Planning Analytics is a powerful tool for managing data access. By following these steps, you can efficiently define and implement security measures that align with your specific needs. PAW offers a versatile platform for ensuring data integrity, whether through manual adjustments or automated rules.

IBM Planning Analytics Tips and Tricks: Trusting the Directory where the Planning Analytics for Excel xll file resides

October 3, 2023 by Marc Assenza

Have you ever received a notification from Excel stating that it was having an issue trusting the Planning Analytics for Excel (PAfe) Add-In? If so, you are not alone!

From time to time, it seems Excel needs to be reminded that the .xll file is a trusted file. To do this you will need to take the following steps:

Open Microsoft Excel and click on File, Options.

When the next screen appears, select “Trust Center” on the left-hand side pane and then “Trust Center Settings” on the right-hand side.

A screenshot of a computer

Description automatically generated with medium confidence

Next, click on the link that reads “Trusted Locations.”

A screenshot of a computer error message

Description automatically generated with low confidence

On the bottom right of the screen, click the button that says “Add new location.”

A yellow box with black text

Description automatically generated with low confidence

Browse to the drive and folder where your PAX xll file is located and define this as your path. Within the same window, also click the checkbox that allows subfolders to also be trusted.

A screenshot of a computer error message

Description automatically generated with medium confidence

Once complete, your Planning Analytics for Excel .xll file will be trusted.

IBM Planning Analytics Tips & Tricks: Adding Images to Charts

September 12, 2023 by Ivan Cepero

Charts are an excellent way to use visuals to convey information. Did you know that you can increase the effect of charts in Excel by adding custom images?

Consider this plain pie chart.

Chart, pie chart

Description automatically generated

We can insert a corporate logo to give the chart a greater visual appeal and branding.

1 – Click on the chart to select it

2 – Right click and select “Format Chart Area”

3 – Select “Picture or texture fill”

A screenshot of a phone

Description automatically generated with medium confidence

4 – Select “Insert” to insert an image from your computer or “Clipboard” to insert a previously copied image

When clicking “Insert” you will see a pop up letting you pick various image sources.

Text

Description automatically generated

5 – You can further refine the options by having the image tile, offset or scale.

Here is the updated chart with the Revelwood logo applied, tiled and set with transparency level 80%:

You can also apply custom images to a specific data point (e.g. a pie slice in this example) by initially selecting the data point instead of the entire chart, then following the steps outlined above.

IBM Planning Analytics Workspace Settings – Dim Editor Index

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Export Dim Structure

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: VSTACK_HSTACK

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel’s WRAPCOLS Formula

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Identifying a Specific Dimension

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Substitute Function

Example 1:

Example 2:

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Cell Security in Planning Analytics Workspace

Watch this video and learn how to:

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips and Tricks: Trusting the Directory where the Planning Analytics for Excel xll file resides

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Adding Images to Charts

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Planning Analytics Tips & Tricks

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Example 1:

Example 2:

Read more IBM Planning Analytics Tips & Tricks:

Watch this video and learn how to:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT