Rule set value to zero

Post Reply
shane.james
Posts: 7
Joined: Wed Jun 03, 2020 12:04 pm
OLAP Product: Tm1/Planning Analytics
Version: 2.09
Excel Version: 365

Rule set value to zero

Post by shane.james » Tue Jun 08, 2021 9:22 am

Hi All

Needing some clarity here. I have had to take over some projects and on checking the rules I have seen on numerous occasions where there is a rule to set a value to zero (usually to ensure no one can capture a value in the cell). Now my understanding is that if that cell is not fed, then there is not a major issue, but if the cell is fed then there could be a huge overhead in terms of calculated cells having to be consolidated. Am I correct in saying that this is definitely not good practise to assign a zero value in a rule and to rather use different methods to ensure other values can't be in that cell.

I have always completely avoided assigning zero's unless absolutely necessary and only in small cubes. Just checking if my thinking here is right.

Wim Gielis
MVP
Posts: 2711
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: Rule set value to zero

Post by Wim Gielis » Tue Jun 08, 2021 11:16 am

I’m not sure if TM1 actually stores these feeders. Did you check this in the relevant }Stats cube ?
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

User avatar
PavoGa
MVP
Posts: 528
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: Rule set value to zero

Post by PavoGa » Tue Jun 08, 2021 12:13 pm

Feeders are independent of the value (or non-value) stored in the cell. Regardless of whether the cell is blank or zero, if it is fed, TM1 has to deal with the overhead slowing performance of the consolidations.

This is why feeder design is so important. There is nothing inherently wrong with storing the zeros as long as they are not fed. However, like you, I tend to avoid zeroes unless it is for the reason stated: to prevent user input for one reason or another and security management is not the best option. One additional bit of information is STET is supposed to perform slightly faster than storing a zero if the rule construct is a conditional, but you may already know that.
Ty
Cleveland, TN

tomok
MVP
Posts: 2808
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Rule set value to zero

Post by tomok » Tue Jun 08, 2021 12:15 pm

Fed cells are fed and take up memory, regardless of whether or not there is an actual value in the cube that has been fed. I am hesitant to declare it not to be best practice as there may be situations where it is the right answer. However, in most instances I would use STET instead of assigning a zero value unless the intent was truly to just lock a cell for input and I didn't feel like security was the right answer in the circumstance.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
gtonkin
MVP
Posts: 927
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: Rule set value to zero

Post by gtonkin » Tue Jun 08, 2021 3:47 pm

I tend to use the Global Security Overlays to block things that are not Group specific and cell security to open things that are Group specific and where input is expected.

If for example I do not want people to capture outside of my planning months on my rolling forecast scenario, I use the global overlay to block those areas first then worry about the planning periods and what is open or closed based on the Custodian's control.

Wim Gielis
MVP
Posts: 2711
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: Rule set value to zero

Post by Wim Gielis » Tue Jun 08, 2021 4:09 pm

You should just put it equal to zero but not feed the cells.
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

User avatar
paulsimon
MVP
Posts: 794
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Rule set value to zero

Post by paulsimon » Tue Jun 08, 2021 5:25 pm

Hi

As Wim says you should continue to use rules to set values to zero to prevent data entry, but you need to tune your feeders to avoid feeding them.

You can rely on security to prevent data entry. However, the issue there is that this will probably only block an end-user. It will not block an Admin user, or a TI process from writing to the cell. A rule setting the value to zero will, and if the business requirements are that no one should be able to enter a value into a particular combination then that is what the rules should do.

Regards

Paul Simon

shane.james
Posts: 7
Joined: Wed Jun 03, 2020 12:04 pm
OLAP Product: Tm1/Planning Analytics
Version: 2.09
Excel Version: 365

Re: Rule set value to zero

Post by shane.james » Wed Jun 09, 2021 4:26 pm

Awesome. Thanks guys. Definitely confirmed my thinking. Will need to play around with the feeders in these models as the zero cells were fed

Post Reply