Building Active Forms with Hierarchies

IBM Planning Analytics (TM1) is always innovating on new concepts and features that can help developers and in v11 of the TM1 server, they introduced something called a hierarchy.  The idea was that you could add a new level between a dimension and its various elements.  One dimension could have multiple hierarchies within it and it serves to greatly improve the performance and flexibility of your application.

Unfortunately, the adoption of hierarchies has been relatively slow in the TM1 community because the related Excel functions just didn’t support this aspect of the software.  And with so many users relying on Excel reports, the function just didn’t get the attention and appreciation it deserved.

Until now…

We’ve been hard at work trying to solve this problem and we’re excited to say that with Slice, all of this is now possible!  Slice enables Excel reports to support hierarchies for the first time, making the most of functions like SUBNM, DBR, and so much more.

To illustrate the power of Slice, let’s look at how you can create an Active Form with hierarchies.  In this example, we’re going to use the Employee cube which has 5 different dimensions: ‘Version’, ‘Month’, ‘Currency’, ‘Employee’, and ‘Employee Measure’.

The ‘Month’ dimension has 4 hierarchies, and the ‘Employee’ dimension has 7.

If we look at the ‘Employee’ dimension, we can look at employees by region, status, or department without having to create a new dimension.

When you’re building a new view, you can choose between the 14 hierarchies even if you only have 5 dimensions.

You can then place hierarchies from the same dimension into rows and columns for analysis.  In the example below we have the ‘Department’ hierarchy of the ‘Employee’ dimension displayed in rows and the ‘Region’ hierarchy displayed in columns.

Leveraging hierarchies, we can use one continuous time dimension (‘Month’) with ‘Year’ and ‘Period’ as two hierarchies.

Now, let’s create an Active Form.  Simply click the ‘Export Active Form’ button from the Cube view as follows:

To enable you to work with hierarchies in an Active Form, Slice has introduced a new function called DBHW.  It works in the same manner as the DBRW function except that you can now put hierarchies in any order you want:

Slice supports all TM1 Excel functions.  So, if you open a report built with Perspective, it will work in Slice.

As you can see, Slice opens up the entire world of hierarchies and it’s definitely worth exploring as a means of improving your data wrangling and analysis.  It offers improved performance and flexibility without requiring that you give up your Excel reports.  With Slice, all your Excel reporting capabilities that you love in TM1 will work with hierarchies.

It’s going to take some time and effort to re-architect your applications to take advantage of these new hierarchy capabilities, but it is an investment that will pay off in the long term.  Your applications will become that much more future-proof so you can adjust and adapt to evolving business requirements.

The Slice Community Edition (which includes all features that you are familiar with in Perspectives as well as support for hierarchies) is free to download.  You can upgrade later to the Business Edition to get access to innovative features and product support.

To learn more about hierarchies, check out these posts:

• Mastering Hierarchies with IBM Planning Analytics
• The future to Time dimensions in the world of hierarchies

Explore the full list of Slice features