New Hierarchy Level Behavior in Planning Analytics (TM1) Set Editor


Leaf Element Level naming convention is really changing...

It's when I first started teaching the Performance Modeler (PM) class and showing students how to create a TI Process with the Guided Import that I noticed in PM that the leaf level was not Zero.  The highest consolidated level was Zero.  At that time I just thought it was a fluke that PM was designed differently, but as for the rest of TM1, they were going to stick with the numbering convention that Architect used.  Wrong!

You have to be a 10 to be number one.  Huh???

Fast forward and I'm now working with Planning Analytics Workspace and PAx.  Like many consultants, we learned TM1 using Architect.  In Architect, level Zero was synonymous with the leaf level element of a dimension and we all know that TM1 holds numeric data only at the leaf level.  We've gotten very accustom to using the terms "leaf", "n-level" and "Zero-level" interchangeably.  In fact, my first dynamic subset was created using the Filter by Level option and choosing "0" for the leaf elements and even naming the subset "Level_0".

Being Level Zero is tops!

Historically TM1 has labeled its hierarchy levels where level 0 is the leaf level and the highest number is the top level.

This behavior persists today in Architect and Perspectives, but with the new set editor released in the 2nd version of Planning Analytics, the behavior in the set editor now follows industry standard MDX practices where level 0 is the highest level and the highest number is the leaf level. In other words, the reverse of what it was previously.

End users should be educated in this change as to avoid confusion when working with the data.

This issue is best highlighted when editing a set. First let’s look at Architect. In this example the plan_time dimension is opened and all elements are shown.

If we filter by levels as shown below, the 0 level will be the leaf level.

And if we filter the level on 2, in this case, it will return the highest level.

Conversely in the Planning Analytics Set editor in Planning Analytics Workspace or Planning Analytics for Excel, the reverse filtering method is true and is in alignment with standard MDX queries. In this example, we are filtering on level000 which returns the top level as seen below.

And if we filter on level002, it returns the leaf level as seen below.

The set editor also provides an easy method to filter on the leaf level by providing an easy to use Leaf Level option in the filter.

Before we get into some solutions to simplify the end user experience, remember that if users are proficient in MDX syntax including TM1-specific MDX functions, they can always quickly build their expression-based subsets by editing the MDX in the Set Editor.

For example, the following standard MDX expression would return all leaf level elements in our example:


If you wanted to return the top level elements, you could use the following expression:


Again, where 0 is the highest level and 2 is the lowest level in the example.

Conversely, you could use TM1-specific MDX functions to create the old behavior in the set editor’s MDX expression by using the following syntax which returns the leaf level:

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [plan_time] )}, 0)}

Or, the following to return the highest level in our example:

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [plan_time] )}, 2)}

Where 0 is the leaf level and 2 is the top level.

Methods to Simplify the User Experience

Create Named Levels

One method is to give your hierarchy levels meaningful names for your end users.

To do so:

In Architect, ensure you have enabled Display Control Objects under the View menu.

Find and open the }HierarchyProperties cube and then from the Dimensions drop down list, select the dimension you would like to override the naming convention for.

In this case we will select plan_time and provide the following level names.

Save the View as Default and not Private.

Now the plan_time dimension’s hierarchy needs to be updated. To do so, we will run a TI process. Right-click Processes in the left pane and click Create New Process.

Click the Advanced tab and then the Prolog tab.

After the Generated Statements section, enter the following syntax where DimensionName is replaced with the dimension you want to update. RefreshMdxHierarchy('plan_time');

Save the Process with an appropriate name and then click on Run to execute the TI process.

Now in the Planning Analytics Set Editor, the level names will appear in a more meaningful way.

Dealing with Ragged or Unbalanced Hierarchies

In some cases, the hierarchies may present elements at the same level as your consolidated levels which you may not want to display in your results when working in the Set editor.

In our example, we can see this with the Description element being displayed when filtering on the Year level.

A simple way to make the user experience more focused is by creating appropriate subsets.

In this case you could create and save a Public subset for the dimension that only displays the Years. In this case a subset called All Years.

With this selection, users have a focused list of elements to work with which they can also expand and there is no need for the extra clicks involved in filtering.

Notice description is no longer present.

So the moral of this story is that you may as well get on board with the new naming convention and start naming your subsets according to what "Level Zero" means going forward and not keep it as it's legacy term.  This will keep the new subset editor consistent with the way Performance Modeler treats levels with the guided import process of creating TI Processes too.

Get more out of your Planning Analytics/TM1 solution faster

Need to build more analytics into your model, but don't have the time.  Lodestar Solutions offers our Small Project Service.  Chipping away at your "To Build" list with our Small Project Services will get you results faster and cheaper than making a big production creating the "Next Phase Major Build" that usually gets kicked down the line due to cost and time.  If you have additional questions about this service or require assistance immediately, please contact Lodestar at (813)-415-2910 or at