Prior Year and Next Year in Rules: What is Faster?

[Adam]

Hello tm1f,

I reference this handy document often: 7 tips to writing faster IBM TM1 and Planning Analytics rules. Additionally, I've begun to use Cubewise Pulse (great tool!) which includes a nifty feature that scans your rules and compares it to their best practices.

There is a best practice recommendation that I wanted to run by tm1f.

We have a time dimension that is solely year based: 2020, 2021, 2022, etc.

• The way I approach previous year in a rule is: DB( ... STR(NUMBR(!Year)-1,4,0) ... )

• The way I approach next year in a rule is: DB( ... STR(NUMBR(!Year)+1,4,0) ... )

I approach it this way because I thought it would be better to calc it on the fly vs. a lookup.

However, the Cubewise Pulse best practice says that string functions are slow and I should instead use an attribute lookup.
So for 2021, store Next Year attribute of 2022, etc, then lookup e.g. DB( ... DB('}ElementAttributes_Year', !Year, 'Next Year') ... )

Question to tm1f ... how do you go about this, and what, in your experience is faster for the tm1 server?

Thanks!

[gtonkin]

HI Adam,

I use the attribute approach - find it easier for others to follow.
Cannot comment on the speed of one vs the other.

I also like having picklists attached to the attributes so that the values are aligned to my meta data and I do not have calculation running on something like 2025 where 2025 is not in the Year dimension.

[Wim Gielis]

Indeed, and there are other advantages as well, like MDX.
In general, additional calculations need to be done in the entire TM1 model, which can be avoided.
Debugging will be easier too with attributes rather than logic buried in rules and TI.

[Adam]

Thank you George and Wim!

We’ll adjust from the calc to a lookup and post our findings in terms of speed.

[PavoGa]

DB(), as in your example, is faster than ATTRS as well in every test I have ever performed.

[lotsaram]

I think that advice is a little out of date and has been debunked via analysis now available with }StatsByRule. An attribute lookup is not faster than a string rule or numeric to string conversion. However, in terms of readibility and maintainability it woudl still normally be the recommended approach unless maximizing calculation performance is the be all and end all.

[PavoGa]

I think that advice is a little out of date and has been debunked via analysis now available with }StatsByRule. An attribute lookup is not faster than a string rule or numeric to string conversion. However, in terms of readibility and maintainability it woudl still normally be the recommended approach unless maximizing calculation performance is the be all and end all.

Looking back, if this was directed at my post, I realized I was not entirely clear: DB('}ElementAttributes_cubename, !element, attr) is faster than the same ATTRS or ATTRN call if using the attributes to determine dimension navigation.

[Adam]

I think that advice is a little out of date and has been debunked via analysis now available with }StatsByRule. An attribute lookup is not faster than a string rule or numeric to string conversion. However, in terms of readibility and maintainability it woudl still normally be the recommended approach unless maximizing calculation performance is the be all and end all.

Looking back, if this was directed at my post, I realized I was not entirely clear: DB('}ElementAttributes_cubename, !element, attr) is faster than the same ATTRS or ATTRN call if using the attributes to determine dimension navigation.

Ty, I took lotsaram's post to mean the guidance that lookup cube in place of STR(NUMBR( is not sound, i.e. lookup cube is not actually faster. In a quick 15 minute test yesterday, I was able to see the same, but will need more time to prove it out.

[Harvey]

Using the string to numeric conversion method is bad practice for other reasons also.

For one, you're linking your naming convention to your rule. What if one day you want to expand your year dimension? What if your users insist on distinguishing between FY2020 and CY2020 ?

Yes you can work around this with aliases & pre-built subsets, or just making an entirely new dimension, but then you're working around inflexibilities in your own rules and introducing further inconsistencies.

And heaven forbid, another developer (who doesn't know about your rule dependencies) will be assigned to implement the new requirement and break everything!

PS: I did an old article on string vs numeric attributes. It's old information, but at the pace that TM1 evolves, it's probably still relevant!

[lotsaram]

I think that advice is a little out of date and has been debunked via analysis now available with }StatsByRule. An attribute lookup is not faster than a string rule or numeric to string conversion. However, in terms of readibility and maintainability it woudl still normally be the recommended approach unless maximizing calculation performance is the be all and end all.

Looking back, if this was directed at my post, I realized I was not entirely clear: DB('}ElementAttributes_cubename, !element, attr) is faster than the same ATTRS or ATTRN call if using the attributes to determine dimension navigation.

I was referring to the best practice advice hardwired into the rules analysis in Pulse to avoid string functions for performance. Yes it's true that a DB() to the element attributes cube is faster than ATTRS(). Most of the time though I would still favor using ATTRS just for readibility, and likewise using lookups rather than string/number manipulations for the reasons Harvey pointed out. Unless of course you are really trying to wring out every last ounce of performance.