Using hierarchies for PickLists

Aug 28. 20182 min read
Scott Wiltshire

PickLists, a quick recap.

As we know PickLists in Planning Analytics can be defined in 3 ways:

as static lists using the form static::item1:item2:item3
based on dimensions using the form dimension:dimName
based on subsets using the form subset:dimName:subName

PickLists can be defined either via naming a dimension attribute “PickList” and defining a string value for either a static, dimension or subset PickList against the element or/and by creating a PickList cube and defining PickLists in the cells of the resulting }PickList_ cube. Generally attribute defined PickLists are static strings and PickList cubes are defined via rules. Where both methods are used the value defined in the PickList cube takes precedence over the attribute value and a blank PickList cube value will default back to the attribute value (exactly like  cell security and element security.) To define a PickList for control cubes we must use a PickList cube,

But Houston we have a problem.

As you no doubt noticed the colon is used as the delimiter in defining PickLists.

With the introduction of hierarchies in Planning Analytics or TM1 v11 the colon is also the delimiter used to separate the dimension name and hierarchy name. This is a logical and sensible choice since this was already used as the dimension | element separator in TM1 rules. Hence where we had dimension:element we now have dimension:hierarchy:element.

Take for example a Product dimension with 2 alternate hierarchies defined; “by Size” and “by Color”. We would refer to these as Product:by Size and Product:by Color (and the “default” or “same named” hierarchy is disambiguated as Product:Product). In fact if we look in in the }Dimensions dimension we will now see 4 entries relating to the Product dimension; Product, Product:by Size, Product:by Color & Product:Leaves.

This creates an obvious problem for the dimension & subset PickList definitions as if we want to use an alternate hierarchy as the source for a dimension or subset PickList then we have an additional string delimiter due to the inclusion of the colon to separate the dimension and hierarchy names and the definition will fail.

Using alternate hierarchies for PickLists. Yes we can!

Luckily there is an easy solution to the problem. We need to escape the additional delimiter. The magic escape character in this case is our friend the backslash. Hence to define a dimension or subset PickList for a hierarchy we need.

and subset:dimName\:hierName:subName

And easy as that our problem is solved. Maybe at some point this will be updated in the IBM documentation but until then bookmark this post!

Tagged , ,