How to work with hierarchies in rules

This article is a little longer than normal, but we do go into quite a bit of detail with some examples of how the hierarchy syntax works in various situations. It’s step-by-step so should be not problem for anyone with an understanding of TM1 rules to follow. All code examples have extra spacing added for readability.

If you are using alternate hierarchies then you are aware that dimensions are no longer the containers for elements and that this role is now taken by hierarchies leaving dimensions a more abstract role as the container for hierarchies. You should also know that leaf elements act as shared resources across all hierarchies of a dimension and must be uniquely defined as well as having unique attribute and cube values. However, the same is not true for consolidated elements which are constrained to a single hierarchy. Moreover, consolidated elements are not bound by uniqueness; a same-named consolidated element may exist in multiple hierarchies with a differing definition of descendants and therefore rolling up to different totals.

The fact that elements are now unique to hierarchies and not to dimensions has necessitated an expansion of the rules language to cater for identifying a unique consolidated element within a hierarchy and being able to uniquely define calculations for such elements.

This article builds on our series of upgrading from TM1 to Planning Analytics and instructs on how to adjust your model, and particularly your business rules calculations, to cope with introducing hierarchies into your dimensions.

Figure 1: Old Paradigm vs. New Paradigm

At a basic syntax level not much has changed. The old ‘dimension’:‘element’ now becomes ‘dimension’:‘hierarchy’:‘element’. For example, we have a dimension “Product” which has in its same-named hierarchy a simple flat rollup of all leaf elements to “Total Product”. The dimension also has 2 alternate hierarchies “by Brand” and “by Category”. Each alternate hierarchy is structured differently with various numbers of levels however each ends with a root element called “Total Product” representing the sum of all leaf descendants.

The best way to think of hierarchies for people familiar with TM1 is as dimensions which can be swapped out or added to a cube on the fly. Previously when writing a rule, although it was a recommended best practice to always include the dimension name in square brackets, unless the element name existed in multiple dimensions and was therefore ambiguous from a practical perspective this was infrequently done. However, with hierarchies, it becomes much more imperative to address both the dimension and hierarchy names. Take our Product dimension example, although it may be obvious which dimension the element “Total Product” belongs to, an element of the same name exists in 3 different hierarchies and is therefore ambiguous without specifying the hierarchy. So in such a situation, we have no choice but to specify the hierarchy or hierarchies. Let’s clarify the syntax for this example.

[ ‘Total Product’ ] = C:      … or (better)      [ ‘Product’ : ‘Total Product’ ] = C:

[ ‘Product’ : ‘Product’ : ‘Total Product’ ] = C:

Note: the area statement above will apply the rule only to the Total Product element in the same named hierarchy. If we want the rule to apply to all instances of the element(s) named “Total Product” then we need the following syntax.

[ ‘Product’ : { ‘Product’ : ‘Total Product’, ‘by Brand’ : ‘Total Product’, ‘by Category’ : ‘Total Product’ } ] = C:

Notice that the curly array brackets contain a list of hierarchy:element pairs. It isn’t possible to list hierarchies in the curly braces separately from the element names.

[ ‘Product’ : { ‘Product’, ‘by Brand’, ‘by Category’ } : ‘Total Product’ ] = C: This syntax is invalid! It won’t compile.

Also note that although in MDX and in TurboIntegrator script although we can refer to a concatenated string of dimension:hierarchy as a shorthand to address a hierarchy this isn’t possible in rules. It’s very important that the joining colon be outside of the single quotes. Otherwise it will be treated as part of a literal string and not as the dimension/hierarchy delimiter.

[ ‘Product:Product’ : ‘Total Product’ ] = C: This syntax is invalid! It won’t compile as no dimension named “Product:Product” exists.

[ ‘Product’ : ‘Product:Total Product’ ] = C: This syntax is invalid! It won’t compile as no element named “Product:Total Product” exists.

Similar to the square bracket notation we address the hierarchy within a DB() function by prefixing the element name with a string for the hierarchy. In most cases the hierarchy will be identified by a literal string but not necessarily. You could also look up a hierarchy name by nesting an AttrS or additional DB() function.

For example the “Sales” cube includes our Product dimension and is dimensioned Sales(Version, Time, Region, Customer, Product, Measure). The alternate hierarchies would be addressed in the Product dimension as follows for a fixed reference:

DB( ‘Sales’, !Time, !Region, !Customer, ‘by Brand’ : ‘Total Product’, !Measure )

Or for a variable element reference as follows:

DB( ‘Sales’, !Time, !Region, !Customer, ‘by Brand’ : !Product, !Measure )

Or the hierarchy could also be looked up by a nested DB(). Say for example which product hierarchy to reference is dependent on the customer:

DB( ‘Sales’, !Time, !Region, !Customer, AttrS( ‘Customer’, !Customer, ‘ProdHier’ ) : !Product, !Measure )

Note that only the hierarchy name is prefixed to the element argument. The dimension name is excluded, this is because unlike in [ ] the dimension ID is already encoded by the position of the argument withing the DB() formula.

Also note there is no use of the pipe symbol when concatenating hierarchy with element name in DB() formulas! This is because the same comments regarding literal strings apply to DB() as to [ ] references.

DB( ‘Sales’, !Time, !Region, !Customer, ‘by Brand:Total Product’, !Measure ) This syntax is invalid! Unlike a [ ] reference this will compile just fine as DB() references are only evaluated when queried there is no validation warning for invalid string literal elements. This will therefore be a “silent failure” (as the calculation engine is trying to find an element named “by Brand:Total Product” in the Product hierarchy of the Product dimension. This element doesn’t exist.)

DB( ‘Sales’, !Time, !Region, !Customer, ‘by Brand:’ | !Product, !Measure ) This syntax is invalid! Likewise this will compile just fine but will fail silently when evaluated by the calculation engine as the lookup is directed to element names with “by Brand:” concatenated to them within the Product hierarchy rather than elements within the by Brand hierarchy.

On the left hand side of the equals or the “area definition” we are defining a slice, or slices of the cube to which the rule applies. Therefore just as we can use array notation for elements, so we can also use array notation for hierarchy:element tuples. On the right hand side of the equation we are defining point differences or lookups to different cell locations versus the cell address from which the rule is being evaluated. Therefore array references on the right hand side of a rule are invalid. In addition to elements this now holds for hierarchies as well. So really there is no change here, nor would we expect it.

The only exception to arrays being allowed on the right hand side of a rule is with feeders where we have two ways of achieving this; either by comma separation of [ ] or DB() targets or by referencing a consolidated element within a [ ] or DB(). Again no change here.

Some astute observers have probably noticed that we now have a bit of a conundrum which didn’t exist before and it has to do with the increased risk of ambiguous element names invalidating rules.  If we clone an existing hierarchy, for argument’s sake the same named hierarchy, into a new alternate hierarchy and any of the existing consolidated elements have C rules associated then the cube rules will be immediately invalidated due to the element names becoming ambiguous. This is not good. Of course we could avoid this risk by pre-referencing elements in C rules with dimension and hierarchy. To go back to our example …
[ ‘Product’ : ‘Product’ : ‘Total Product’ ] = C:
Using this syntax even in cases where an alternate hierarchy doesn’t exist does save us from rule invalidation on metadata change (and the result that none of our rule calculations work), but now we have a new problem, namely our rule applies only to the defined hierarchy and won’t be applied to the element in the new hierarchy which we have just created. To extend the scope of the rule to the new hierarchy we have no choice but to edit the rule file and add the new hierarchy to the area definition.

It isn’t going to be convenient to enforce unique naming of C elements across all dimensions and all hierarchies. Again, back to our simple example. It makes sense that the “Total Product” element may exist in multiple hierarchies and have different rollup structures but ultimately it represents the same concept; the sum total of all leaf product elements. Although it is possible, and some would argue better, to name the elements uniquely per hierarchy “Total Product by Brand”, “Total Product by Category”, etc. this actually leads to a doubling of the terminology as the hierarchy is then identified twice, in the element name as well as the hierarchy itself.

Moreover, in such a situation where a same named C element exists in multiple hierarchies chances are if a C level calculation is needed for the element then the calculation will be the same for all affected hierarchies. Not to mention that in cases where there are a lot of hierarchies the requirement to specifically reference each one is quite tedious.

The answer to this conundrum is to introduce a syntax for “unspecified hierarchy” just as we have the !dimension syntax for “unspecified element”. (Hopefully IBM is listening.) It might look something like this …

[ ‘Product’ : ! : ‘Total Product’ ]

On the left-hand side this would be interpreted as apply this rule to any element named “Total Product” in all hierarchies of the Product dimension where a such named element may exist.

On the right-hand side this would be interpreted as look up the “Total Product” element in whatever hierarchy of Product dimension the rule is being evaluated from.

In a DB() it might look like this, with the same interpretation as for square bracket hierarchy wildcard.

DB( ‘Sales’, !Time, !Region, !Customer, ! : ‘Total Product’, !Measure )

Obviously, this concept doesn’t yet exist in the TM1 rules language. But the need is there so let’s hope this gap is addressed!

What hierarchies allow us to do in terms of cube design are simply awesome, but this does come at a cost of additional complexity in terms of managing rules. Here are the take-away points from this article:

• The syntax for addressing hierarchies is straightforward. Simply prefix the element name with the hierarchy name delimited with a colon, e.g. [‘dimension’ : ‘hierarchy’ : ‘element’]
• Pay attention not to concatenate the hierarchy name with the pipe “|” or to include the hierarchy name or colon as part of the literal string of an element name reference
• If same named consolidated elements exist in alternate hierarchies, there is a much increased risk of rules encountering ambiguous name errors
• Hierarchies must be individually specified by name, which can be tedious if there are many hierarchies. No wildcard syntax currently exists for hierarchies

Although we have given the syntax for referencing hierarchies on both sides of the equal sign be aware that the calculation engine currently has a bug in all versions of PA 2.0 through to the current 2.0.6 where the shorthand [ ] hierarchy notation does not work correctly on the right-hand side of rules. So currently the DB() syntax must be used if referencing alternate hierarchy elements in a calculation! Hopefully this will be fixed in 2.0.7