Alternate Hierarchies in Canvas

Probably the most exiting feature in IBM Planning Analytics is alternate hierarchies. Using alternate hierarchies will enable you to potentially reduce the number of dimensions in your cube and therefore improve cube size and performance.

For example you could merge the Year and the Month dimension into one Time dimension:

Enabling Alternate Hierarchies

Alternate hierarchies are available since IBM Planning Analytics. To turn on alternate hierarchy, you need to add the new parameter EnableNewHierarchyCreation=T into the tm1s.cfg file and restart the TM1 instance.

DBRs

Support for alternate hierarchies has been added from Canvas 2.0 with tm1-ui-dbr. To get values using alternate hierarchies, you can now use the following syntax within the tm1-elements:

  • "Hierarchy1::Element1 && Hierarchy2::Element2"

For example, to get the value from 2009 Jan on the Time dimension, you will need to use the following when referring to the intersection of these hierarchies:

  • "Year::2009 && Month::Jan"

An example of DBR with alternate hierarchies can be found in the Components page of the samples application:

  • http://localhost:8080/samples/#/sample/component/dbr-hierarchy

MDX

Another way to get data using alternate hierarchies is to use an MDX query. To specify an element on another hierarchy within the same dimension, use the following format:

  • [Dimension].[Hierarchy].[Element]

For example to get the element All Months which is in the Month hierarchy of the Time dimension:

  • [Time].[Month].[All Months]

One of the ways to show data from an MDX query within Canvas is using a Named MDX. For more advance scripting, you can also create your own MDX query and pass it on one of the Canvas scripting service - cubeExecuteMdx(). Additional information on this and other available Canvas scripting services can be found on you application's Help pages.