One of the reasons why IBM TM1/Planning Analytics is so fast at performing complex calculations is because it gives developers many options to optimise the calculation rules. The way developers write a rule has a huge impact in terms of performance.
Thanks to the }StatsByRule cube introduced with IBM Planning Analytics, we can now find out the evaluation time of each rule and give definitive answers to common performance questions.
1. Use CONTINUE over IF
To improve the performance of a complex rule, try to simplify it. If you see multiple nested IF statements in a single rule, split it into multiple rules with CONTINUE statements.
The time to evaluate two simple rules with CONTINUE is 40% quicker than one rule with two nested IF statements. However, too many CONTINUE statements will also create performance issues.
2. Simplify the comparison
Numeric comparison is generally much faster than string comparison. The length of the text also has an impact:
When comparing ATTRN, ATTRS and SUBST: the SUBST function which returns a sub-string of the element is 75% faster than ATTRN!
3. Replace ATTR functions with DB
Using ATTRS(dimension, element, attribute) looks like the logical way to get an attribute value, but the DB function reading directly from the }ElementAttributes_ cube is 70% faster:
Key differences: ATTRN returns numeric, ATTRS returns string, DB can return either. Using ATTRS for an alias returns either the alias value or the principal name if unpopulated. DB only returns alias when populated.
4. Replace DB with DIMIX
To compare elements (especially dates), using DIMIX(dimension, element) to get the index number can reduce evaluation time by 50%:
Use DIMIX with care — only when you can guarantee the index of the element won't change.
5. Dimension relationship functions are faster than DB on small dimensions
ELISCOMP vs ELISPAR vs ELPAR:
ELPAR is 60% faster than ELISPAR because ELISPAR iterates over all parents while ELPAR checks only the first parent:
DB vs ELPAR: For small dimensions (<100,000 elements), ELPAR is 60% faster than DB. However, for dimensions with >100,000 elements, DB becomes faster by more than 10%.
DB vs ELISANC: ELISANC is 40% faster on small dimensions, but DB is faster by 12% on large dimensions.
6. Replace DB with ['element']
The short form ['element'] is 40% faster than the DB function because TM1 only retrieves elements specified in the current cursor, whereas DB builds a completely new cursor:
7. Replace ['element'] with ['dimension':'element']
Specifying the dimension name in area statements can decrease evaluation time by 10% because it constrains the element search to a single dimension:
For cubes with very big dimensions, always qualify the dimension. A nice side effect is that it improves the readability of the rule.
Conclusion
The }StatsByRule cube is a very powerful feature for testing different rule expressions. Remember that a TM1 developer should balance three core principles: Design for Functionality (make the solution intuitive), Design for Maintainability (adhere to coherent design standards), and Design for Performance (leverage TM1's unique strengths).