At times, the “value” of the parent element needs to be referred to, to determine how a calculation applies to a specific element.
A simple everyday example would be of Balance Sheet Vs. Income Statement across the time dimension. The QTD for Balance Sheet Accounts is the
balance of the last completed month in the quarter and the summation of all completed months for Income Statement.
TM1 has a pretty good function ELPAR that would be of help in such cases. However, the following points attempt to elucidate the practical difficulties in using it.
- A single ELPAR function can only check for the parent 1 level up. So, if the parent is sitting at 5 levels above, we would have 5 nested ELPAR functions.
- In a ragged hierarchy, ELPAR will not always be effective. The rules/ TI process will need to be written to accommodate the nuances of the hierarchy.
- Also, the special handling would need to be repeated for every instance of usage.
ELISANC might seem like an answer. However, the way TM1 would behave is, it would look at every element above the level of current element level. This would make it less effective in large hierarchies.
Meaningful Alternative – ATTRS (a string attribute):
Store all parent values for an element as attribute and use it for all comparisons. This would be the fastest look up.
- The Attribute is set up once when the dimension is created/ modified.
- The rules/ TI look cleaner in many cases
- The processing is faster since it is a straight look up
- Attributes can be used in BI reporting E.G say the report is for all Midwestern states, the attribute value of “Midwestern States” can be used in reporting
Conclusion, while creating hierarchies, add the multiple level parents as different attributes.
