An unintended benefit of hierarchies

Jan 11. 20192 min read
Scott Wiltshire

The Leaves hierarchy

When an alternate hierarchy is added to a dimension in TM1 for the first time a Leaves hierarchy is also automatically created in the background. The Leaves hierarchy has some special properties as it contains only leaf elements (inserting consolidations is not possible). The maintenance task of inserting new elements into the Leaves hierarchy is not necessary but happens automatically when leaf elements are added to other hierarchies. Various alternate hierarchies may contain non-overlapping or not completely overlapping collections of leaf elements but the Leaves hierarchy contains a complete master set of all leaf elements from all hierarchies of a dimension.

Although new elements are added automatically to the Leaves hierarchy, deleting a leaf element from other hierarchies does not delete it from Leaves. Element delete actions from the Leaves hierarchy must be explicit. Moreover deleting an element from the Leaves hierarchy will also automatically delete the element from all other hierarchies where it is present. This is the recommended way to remove leaf elements.

Benefit 1 of Leaves

This presents a new data integrity mechanism for TM1. As numeric data is stored only against leaf elements it is now possible to clear all elements from a hierarchy without causing any loss of data. In many production systems dimension maintenance where DimensionDeleteAllElements is used to clear a dimension prior to updates has historically been avoided due to the risk of data loss should the data source for the update be truncated and not include all leaf elements. Although the risk of a truncated data source or interrupted network connection during a dimension load may be small it exists. For this reason unwinding all C elements is usually preferred over DimensionDeleteAllElements as this avoids any possibility of data loss. However, for very large dimensions the unwinding process can be very time consuming leading to long dimension updates.

Benefit 2 of Leaves

For systems with large complex dimensions where unwinding or C element deletion strategies are used we now have the potential to perform a risk free HierarchyDeleteAllElements and/or DimensionDeleteAllElements instead. Note the DimensionDeleteAllElements function acts only on the same named hierarchy and will not remove elements from any other hierarchies. As data is stored against leaf elements and the Leaves hierarchy is not affected no data will be lost. This can result in a huge saving of processing time during dimension updates as well as simplifying processing logic. This alone may be a sufficient reason for implementing an alternate hierarchy in a large dimension even if hierarchies are otherwise not used in the model.

And there you have it. Two significant benefits of the new hierarchies feature which you may not have considered when weighing up the benefits of upgrading to PA and hierarchies!

Tagged , ,