Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: Editing Chores While Active

September 29, 2020 by Lee Lazarow Leave a Comment

Did you know that IBM Planning Analytics Workspace (PAW) allows you to edit chores while they are active? Perspectives required you to de-activate a chore, make your modifications, and then re-activate it. However, the two “bookend” steps of this approach have been eliminated in the PAW environment.

To edit a chore, right click on the name of the chore and select the option to “Edit Chore.”

IBM Planning Analytics Tips & Tricks: Editing Chores

The chore will open to show you information about the chore. You will then be able to edit the details associated with the schedule and the specific processes to run.

You have the option to disable the schedule at any time, but this is no longer a requirement.

Editing chores in IBM Planning Analytics Workspace

This approach allows you to quickly modify your chores without having to perform the mundane steps of de-activating it and then re-activating it. This also means that you no longer need to remember to turn the chore back on after making your changes!

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 LET Function

September 8, 2020 by Lee Lazarow Leave a Comment

Have you ever created a formula that repeats another formula within a cell? Did you know that this repetition may be slowing down your reports?

For example, you may have a report where you want to pull IBM Planning Analytics data and replace zeros with the word “None.” You can write this as a formula via:

=IF ( DBRW(cube,dim1,dim2,dim3)=0, “None”, DBRW(cube,dim1,dim2,dim3) )

Within this formula, the IF check first looks at the DBRW formula to determine a value. If the value is not zero then the IF check needs to re-run the exact same DBRW formula to produce the value that will appear in the cell. This means that one cell potentially needs to run the same formula twice! While this doesn’t sound like a big deal, it can easily impact the performance of your report if you have many iterations of the formula.

Excel has introduced a new function called LET that will help optimize this situation. This function allows you to define a variable that can repeatedly be used within a calculation. It will help you improve performance since it will only execute the calculation once within the cell.

The syntax of the function is:

=LET (VarName, VarValue, Calculation)

VarName is the name of the variable to be used
VarValue is the value of the variable
- This can be a hard coded number or it can be a formula
Calculation is a formula that uses the variable name
- This can be a simple formula such as X+Y or it can use any existing Excel function

A simple iteration of this formula can be written as:

=LET (x, 5, x*2)

This formula would result in the number 10.

The LET formula also allows you to define multiple variables. A simple example of multiple variables can be written as:

=LET (x, 5, y, 2, x*y)

This formula would also result in the number 10.

Using this approach, our initial formula can be rewritten using the LET function:

=LET ( CubeVal, DBRW(cube,dim1,dim2,dim3), IF(CubeVal=0,”None”, CubeVal) )

This ensures that the DBRW pull is only executed once within the cell.

This functionality allows you to optimize your Excel based reports by minimizing the number of calculations that happen within your report.

IBM Planning Analytics Tips & Tricks: Control Space

September 1, 2020 by Lee Lazarow Leave a Comment

Have you ever been deep in thought while writing a rule or a process only to realize that you forgot the parameters for a specific command? IBM Planning Analytics Workspace (PAW) has solved this dilemma by giving you the ability to determine those parameters with a simple set of keystrokes.

PAW’s rule editor and process editor allows you to use Ctrl-Space (e.g., press the control key and the space bar at the same time) to determine the required values associated with any rule or TI command. For example, if I type ATTRS and then press Ctrl-Space, the following will appear on the screen:

I now know the parameters that are required to complete the code.

Ctrl-Space will also help you determine which function to use. For example, if I type ATTR and then press Ctrl-Space, the following will appear on the screen:

IBM Planning Analytics Tips: Control Space

I am now aware of all the possible attribute functions that are available and can double click on one of the results to have it automatically inserted for me.

If I do not know anything about the name of the functions to use, I can click on the fx button to see a list of the available functions, all grouped by category.

IBM Planning Analytics Tricks: Control Space

This approach allows you to quickly remember the functions and parameters that can be used in your code without having to shift your focus to another screen or manual.

IBM Planning Analytics Tips & Tricks: New PAx Feature – Double Click

August 25, 2020 by Lee Lazarow Leave a Comment

IBM Planning Analytics for Excel (PAx) contains a task pane that shows the cubes within your model. Double clicking on a specific cube expands the information associated with the cube, including public views, private views, and the dimensions. Version 49 introduced a new feature to the double click … the ability to launch an exploration browser within your Excel environment.

When double clicking for the first time, you will be promoted to define what happens upon double click. There are two options to select from: Expand and Launch

IBM Planning Analytics Tips & Tricks: PAx double click

Expand will show the nodes associated with the cube
Launch will open a view editor
Note: as of this writing, at least one view must be defined for the launch option to properly work. IBM is currently working to change this requirement.

After launching and modifying your view, you can then bring the results back to Excel by clicking on the Reports button and selecting the type of report that you want to convert your view into.

New IBM Planning Analytics PAx feature - double click

If you want to reset the action associated with double click, you can change it within the options dialog box.

New double click feature in IBM Planning Analytics for Excel

This feature allows you to quickly analyze data via a drag-and-drop approach and then bring the results back into a Planning Analytics for Excel worksheet.

IBM Planning Analytics Tips & Tricks: Home Button

August 18, 2020 by Lee Lazarow Leave a Comment

IBM Planning Analytics Workspace (PAW) offers an easy to use navigation menu that will quickly allow you to navigate to any open book. This is done by clicking on the menu at the top, center of your screen. Here is a sample menu that shows three open workbooks.

But did you know that you can also go back to PAW’s opening screen via a single click? A “home” button exists at the top, left corner that takes you directly to the welcome page.

This approach will allow you to save time by quickly navigating back to the start via a single click.

IBM Planning Analytics Tips & Tricks: PAW Pass Context

August 4, 2020 by Dillon Rossman Leave a Comment

IBM Planning Analytics Workspace sheets allow users to add buttons. These buttons can be used to navigate to different sheets or books, or even run processes. But did you know you can “pass context” from one book to another?

Before beginning, ensure everything on your sheets is synchronized. This is as easy as going to the synchronization properties of any views, visualizations, etc. on your sheets and enabling “synchronize dimensions.”

IBM Planning Analytics Tips & Tricks: PAW Pass Context

To enable “pass context,” navigate to the properties of your button and select “Button target.”

IBM Planning Analytics Tips: PAW Pass Context

Under “Other book” simply enable “Pass context.” Your button will now pass selections made from book to book.

IBM Planning Analytics Tricks: PAW Pass Context

This allows you make a selection in one book, such as a year, and then carry it over to another book.

IBM Planning Analytics Tips & Tricks: Change Connection used by Quick Report

July 28, 2020 by Thanh Chau Leave a Comment

When working with IBM Planning Analytics templates in Excel, there may be instances where you need to change the instance that the cube references to pull or send data. In Perspectives, updating the source was as simple as updating the first parameter of the DBRW or DBRA formulas. However, Quick Reports do not use formulas so there is a different approach to update the connection.

In the Quick Reports section of the Planning Analytics ribbon, click on the Properties icon.

IBM Planning Analytics Tips & Tricks: Change Connection used by Quick Reports

In the top section of the resulting window, you will see the connection detail for the selected Quick Report. Click the Update button to make changes to the connection.

How to change connection used by quick reports in IBM Planning Analytics

In the next window, select the Admin Host from the dropdown menu at the top labeled “System”. Expand the instances to view the cubes available in that instance and select the cube you want to use as the new source. When you click OK, the source will be updated.

Learn how to change connection used by quick reports in IBM Planning Analytics

This approach offers an easy way to use the same files across multiple environments.

IBM Planning Analytics Tips & Tricks: Edit Action Button

July 21, 2020 by Thanh Chau Leave a Comment

Action buttons in IBM Planning Analytics are a great utility that allow users to recalculate a sheet, navigate to another location, or run a TurboIntegrator process. As your models evolve, you may need to periodically revise your templates to maintain their functionality. This sometimes include the action buttons.

In Perspectives, updating an action button was as simple as right-clicking on it and selecting the Properties option. In Planning Analytics, the approach is a little different.

From the task pane, click on the tab labeled “Workbook.”

This section will show you all the different Planning Analytics objects that exist in your workbook. Expand the folder labeled “Action buttons.”

The name of each action button will be prefixed with the sheet name in which it is located. Find the action button that you’d like to edit, right-click it and select the “Edit” option.

This will open the properties window where you can make changes to the action button.

This approach will allow you to quickly edit all of your action buttons at the same time instead of having to navigate through your workbook and search for each button.

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.

IBM Planning Analytics Tips & Tricks: Editing Chores While Active

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel LET Function

Read more Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Control Space

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: New PAx Feature – Double Click

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Home Button

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Pass Context

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Change Connection used by Quick Report

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Edit Action Button

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Data Load Process

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Planning Analytics Tips & Tricks

Read more IBM Planning Analytics Tips & Tricks:

Read more Excel 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:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT