IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: HTTPPORTNUMBER

June 6, 2023 by Marc Assenza

Did you know that when you create a new TM1 / IBM Planning Analytics Local (PAL) instance that the HTTPPORTNUMBER parameter is required? This setting is placed within the TM1S.cfg file for your model.

Some things to note when defining the HTTPPORTNUMBER parameter:

1. The valid port values are between 5000 and 49151.
2. The configuration like will look something like this:

HTTPPORTNUMBER=5005

3. The port used must be unique on the server for any service, not just TM1/PAL database services.

4. If you forget to assign a value within the TM1S.cfg file, then Port 5001 is automatically assigned to the model. This is important for multiple reasons:

a. Port 5001 may already be being utilized by another service which will prevent the TM1/PAL server instance from starting

b. If you have multiple TM1/PAL models on the server and none of the TM1S.cfg files have the HTTPPORTNUMBER parameter defined, then the first server instance will start (if port 5001 is not currently being utilized). Every other TM1/PAL instance will then fail because each will also try to use Port 5001, but the port is already used.

That’s it! Just something to keep in mind when creating new TM1 / PAL models on your server!

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: Dynamic Subsets Based on a Cube

Did you know you can use MDX to create dynamic subsets based on cube values in IBM Planning Analytics? Read this post as we explain an example where we will only show companies with net sales greater than 30,000,000 in the selected year.

IBM Planning Analytics Tips & Tricks: Garbage Memory

A client asked us why Windows was showing a much larger memory footprint than expected from their cube sizes. We analyzed the model and found a large amount of garbage memory. Which leads to the question “what is garbage memory?”

IBM Planning Analytics uses various approaches to minimize the amount of RAM needed for a model. Read this post to learn how much memory is being used by your cubes.

IBM Planning Analytics Tips & Tricks: Adding and Editing Connection URLs in Planning Analytics for Excel

Read this post to learn how to add a new Connection URL or to edit an existing Connection URL in Planning Analytics for Excel (PAx).

IBM Planning Analytics Tips & Tricks: Run TI Processes from PAx Task Pane

May 9, 2023 by Revelwood

Did you know that you can run TurboIntegrator (TI) processes via the task pane in Planning Analytics for Excel (Pax)? Within the task pane, you can choose to display processes by clicking on the “Show and Hide” button and selecting the option to “Show processes.”

Once enabled, the processes will appear at the bottom of the task pane.

You can run the scripts by right-clicking on a process and selecting the option to “Run process.”

If any parameters are needed, a popup box will appear.

After the process has completed, you can expand the details by hitting the drop-down symbol on the right. This will summarize whether the process ran successfully or whether there were any errors. You can then get further details about the error(s) via the message log.

IBM Planning Analytics Tips & Tricks: TI Dimension Functions

May 2, 2023 by Marc Assenza

Have you ever updated a dimension in the Prolog of your TurboIntegrator (TI) script and then received errors when making further changes to the dimension in the Metadata section? This is because certain dimension altering functions such as DIMENSIONELEMENTINSERT are not evaluated until the Data Procedure runs.

Dimension updates in IBM Planning Analytics / TM1 follow a specific pattern: duplicate the current dimension, make changes to the duplicated dimension, and then apply the changes to the actual dimension. The applied changes to the actual dimension do not occur until the Data procedure begins (e.g., when all Metadata scripting is completed).

Let’s say that you placed code in the Prolog to add elements using the DIMENSIONELEMENTINSERT function. If you then try to interrogate or manipulate those elements in the Metadata tab, the script would not be successful because the changes have not been fully completed. This can be resolved by instead using the DIRECT version of dimension manipulation functions, such as DIMENSIONELEMENTINSERTDIRECT. The direct version of the functions applies changes immediately to the current dimension and does not require the entire Metadata processing to be completed.

This approach will allow you to properly manipulate dimension in both the Prolog section and the Metadata section of the same script.

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 4

April 25, 2023 by Revelwood

Revelwood’s IBM Planning Analytics team has created numerous tips & tricks focused on helping people use Excel. For the past few weeks, we’ve been highlighting some of the most popular and interesting posts on Excel. If you’ve missed our earlier posts, read part 1, part 2 and part 3.

Here are some other Excel tips and tricks from our team of experts.

IBM Planning Analytics Tips & Tricks: Excel YEARFRAC

Some Revelwood clients often build models that define the fraction of a year for a given month. For example, January is approximately 8.5% of a calendar year (31/365) and September is approximately 11.5% of a working day’s calendar (30/260). What happens in situations where you want to calculate the percentage of a year where the days are not the first or last day of the month? This is where Excel’s YEARFRAC formula can help.

IBM Planning Analytics Tips & Tricks: Unhide Multiple Excel Sheets

As you know, an Excel workbook can consist of multiple sheets. There are many instances where you may want to hide some of the sheets. It’s very fast and easy to hide sheets, but Excel historically allowed you to unhide only one sheet at a time. Microsoft changed that approach and now allows you to unhide multiple sheets at once.

Learn how to unhide multiple Excel sheets.

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

Excel is, and always will be, a powerful tool for the finance team. With so much power, there are many great Excel formulas that are rarely used. Here’s a situation – imagine you wanted to easily replicate a sheet and have each sheet use an MDX expression that referenced a different dimension name. You want to use a formula to determine the sheet name.Excel’s CELL function returns information about a specific cell within an Excel file. Read more to learn the syntax of that function.

Read part 1, part 2, and part 3 below:

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 1

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 2

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 3

IBM Planning Analytics Tips & Tricks: PAW Users and Groups

April 18, 2023 by Lee Lazarow

Do you know how to manage your users and groups in IBM Planning Analytics Workspace (PAW)? As of version 84, PAW now allows you to edit this information via a single click.

To open the users and groups editor, right-click on a database within your modeling workbench and select the option for Manage users and groups.

Once selected, a grid showing the users on the rows and the groups on the columns will appear. This layout mimics the approach used in Perspectives.

The following functions are available to maintain your user security:

To assign or remove a user to a group, right-click at the intersection of a user and group name, then click Assign user to group or Remove user from group.
To assign all users to a group, right-click the group name, then click Assign group.
To remove all users from a group, right-click the group name, then click Remove group.
To assign a user to all groups, right-click the user name and then click Assign user.
To remove a user from all groups, right-click the user name and then click Assign user.
To remove a group from the database, right-click the group name and then click Remove group.
To remove a user from the database, right-click the user name and then click Remove user.

You can also use the editor to import users and groups from Planning Analytics Workspace or to create a new group on the Planning Analytics database.

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 3

April 11, 2023 by Revelwood

Revelwood’s IBM Planning Analytics team has created numerous tips & tricks focused on helping people use Excel. Over the next few weeks, we’ll be highlighting some of the most popular and interesting posts on Excel. If you’ve missed our earlier posts, read part 1 and part 2.

IBM Planning Analytics Tips & Tricks: Map Charts

Map charts is a nifty feature in Excel that will help visualize your data. It enables you to compare values and show categories across geographical regions.

Our blog post explains how you can use the Map Charts feature.

IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions

In recent years Microsoft introduced a new version of the CONCATENATE functional called CONCAT. Their purpose is similar in that the new function still combines text from multiple ranges and/or strings. CONCAT will eventually replace CONCATENATE.

The TEXTJOIN function joins text from multiple ranges and/or strings but includes two additional parameters. These parameters allow you to specific the delimiter and determine if empty cells are ignored.

Learn more about Excel’s CONCAT and TEXTJOIN functions.

IBM Planning Analytics Tips & Tricks: Excel’s IFS Function

Excel’s IF function has been around for quite a while. Back in 2020, Excel introduced the IFS function. This function checks whether one or more conditions are met and then returns the value associated with the first TRUE condition. The main objective of IFS is to simplify an expression that would have previously required multiple nested IF functions.Read our blog post to learn the format of the IFS function.

IBM Planning Analytics Tips & Tricks: UNIQUE Function in Excel

April 4, 2023 by Revelwood

Have you ever needed to produce a list of unique values within a cell range in Excel? If so, the UNIQUE function can do this.

The example below shows a simple list of countries that includes some duplicates:

Table

Description automatically generated

To produce a unique list, simply pass the cell range of the original list into the UNIQUE function.

Application, table, Excel

Description automatically generated

The unique list of values will “spill” down from the original UNIQUE formula.

This approach will give you a quick and easy way to get unique values from a cell range.

IBM Planning Analytics Tips & Tricks: On-Demand Webinars, Part 2

March 28, 2023 by Revelwood

Did you know we have a wide variety of IBM Planning Analytics webinars on-demand? We showcased two of our most popular webinars – Best Practices when Using Planning Analytics Workspace Charts and Creating PAW Charts Using the New Experience – in a recent blog post.

Here are two more of our popular IBM Planning Analytics / TM1 on-demand webinars.

Best Practices for Using TurboIntegrator

This webinar showcases some features of TurboIntegrator that you may not be using. We cover a series of functions and offer best practices for using them. We describe each function, explain how we use the function when creating models, and discuss the benefits you get by using these approaches.

Here are a few of the topics we review:

Making calls outside of a process
Modifying dimension structures
Creating views and subsets

Best Practices When Using Hierarchies in IBM Planning Analytics

In this webinar, we explain the concepts, the creation and the things to consider when using hierarchies within their existing models. We show examples of hierarchies that Revelwood clients use and how they create new reports and analyses.

IBM Planning Analytics Tips & Tricks: HTTPPORTNUMBER

Some things to note when defining the HTTPPORTNUMBER parameter:

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Popular Posts, Part 1

IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube

IBM Planning Analytics Tips & Tricks: Garbage Memory

IBM Planning Analytics Tips & Tricks: Adding and Editing Connection URLs in Planning Analytics for Excel

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Run TI Processes from PAx Task Pane

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: TI Dimension Functions

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 4

IBM Planning Analytics Tips & Tricks: Unhide Multiple Excel Sheets

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

Read part 1, part 2, and part 3 below:

IBM Planning Analytics Tips & Tricks: PAW Users and Groups

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel Tips, Part 3

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: UNIQUE Function in Excel

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: On-Demand Webinars, Part 2

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Planning Analytics Tips & Tricks

Some things to note when defining the HTTPPORTNUMBER parameter:

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:

IBM Planning Analytics Tips & Tricks: Unhide Multiple Excel Sheets

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

Read part 1, part 2, and part 3 below:

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:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT