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

Post Reply
Adam
Posts: 47
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

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

Post by Adam » Fri Apr 30, 2021 7:48 pm

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!
Take care.
Adam

User avatar
gtonkin
MVP
Posts: 911
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin » Sat May 01, 2021 2:12 pm

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
MVP
Posts: 2684
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Sat May 01, 2021 6:34 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Adam
Posts: 47
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

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

Post by Adam » Sun May 02, 2021 3:34 am

Thank you George and Wim!

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

User avatar
PavoGa
MVP
Posts: 516
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

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

Post by PavoGa » Mon May 03, 2021 1:05 am

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

lotsaram
MVP
Posts: 3489
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post by lotsaram » Mon May 03, 2021 8:12 am

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
MVP
Posts: 516
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

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

Post by PavoGa » Tue May 04, 2021 4:03 pm

lotsaram wrote:
Mon May 03, 2021 8:12 am
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
Cleveland, TN

Adam
Posts: 47
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

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

Post by Adam » Tue May 04, 2021 4:38 pm

PavoGa wrote:
Tue May 04, 2021 4:03 pm
lotsaram wrote:
Mon May 03, 2021 8:12 am
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.
Take care.
Adam

User avatar
Harvey
Community Contributor
Posts: 229
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

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

Post by Harvey » Wed May 05, 2021 12:35 am

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! :D
Take your TM1 experience to the next level - TM1Innovators.net

lotsaram
MVP
Posts: 3489
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post by lotsaram » Wed May 05, 2021 8:51 am

PavoGa wrote:
Tue May 04, 2021 4:03 pm
lotsaram wrote:
Mon May 03, 2021 8:12 am
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Post Reply