How does TM1 calculate long formulas?

[ioscat]

Hello forum!

As i found using rule tracer if we have several not nested IF statements in rules with constructions like IF(<...>,0,Continue), TM1 will calculate all IF-s (suppose we don't use skipcheck and feeders) until one of them return not special command "CONTINUE" but a value or command "STET". If we use rule tracer we will get the last IF that was calculated.

So does TM1 works same way in case of nested IF-s? Does TM1 work same way in case of multiplication by zero like A*B*C*D*E*...*0*K*L*M*... ?

[jim wood]

Rules always work from top to bottom. It will hit every if statement but not apply as the continue is triggered. Only when it hits the catch all stet will a rule apply to the cell. If it hits a condition before the stet catch all that will apply and all other are ignored. Skipcheck and feeders has nothing to do with that.

[jim wood]

As for a nested IF. That is one rule so TM1 counts if as one rule being applied to the cell which will therefore show in the rule tracer. Personally I've never used the rule tracer. I never liked it.

[rozef]

By chance I just found this documentation for CellGetN function which let me think that any TM1 instruction is executed in one block until there is a semicolon.

http://pic.dhe.ibm.com/infocenter/ctm1/ ... lgetn.html

[David Usherwood]

CellGetN is a TI keyword and you were talking about rules. Yes, the closing delimiter (;) is the same, and you can use most rules functions in TI but the two engines are so significantly different that you are heading into a lot of trouble if you confuse the two.

[ioscat]

Can't get what do you want to say so there are several possible reasons for example you are drunk (i'm not, sure) or my english is incredibly, deeply bad or maybe forum is occupied by aiens...

[rozef]

I am not drunk, I am seek but anyway.
Of course I know rules and process are different engine but developped for same product. So maybe the remark I pointed on TI doc can be true on rules.
Just a supposition but I suppose rules follow the same logic and each rule are executed as one instruction.

Now if you look to rule tracer you see that the rule applied to a cell is always showed in its entirety.
So even if when a condition is applied and a value is return the trace stop, each condition or calculation in a rule are highly probably executed in one piece of script by TM1 engine.

I am not a TM1 developper so I can't know exactly the truth. I hope it could have help you.
Sorry for my bad english.

[Alan Kirk]

Of course I know rules and process are different engine but developped for same product. So maybe the remark I pointed on TI doc can be true on rules.
Just a supposition but I suppose rules follow the same logic and each rule are executed as one instruction.

No, they really can't follow the same logic. Think about what the two things are doing for a moment.

CellGetN is simply retrieving a value from a cube. That value may be an input value, it may be calculated by consolidation, or it may be calculated by rules.

Rules on the other hand are values which are calculated entirely by... rules.

Retrieve a value on the one hand, calculate a value on the other.

As David said, the only thing that they share in common is that they both end with a semi-colon.

Now if you look to rule tracer you see that the rule applied to a cell is always showed in its entirety.

OK, but what exactly does that prove given that the whole purpose of the Rules Tracer is to show you the whole calculation so that you can understand where the values are coming from? You would expect a tool like the Rules Tracer to show all values, regardless of whether the rule itself needs to calculate all of those values in practice. The fact that the Rules Tracer shows all of the values doesn't give any insight at all into how the calculation engine works.

[tomok]

If you think about how rules work you will understand that it doesn't matter in what order the IF conditions are evaluated in a single rule statement. The rule syntax works like this:

[Area of cube to apply rule] = [Calculation]

There is no SELECT CASE type of statement in rules so the only way to have multiple calcs is to use IF, THEN, ELSE. A condition can only be either TRUE or FALSE, it can't be both. That means there can be no conflict as to which branch the particular cell in the cube belongs to and it makes no difference in what order they are evaluated. Rules can only overlap when there is on overlap in the [Area of cube to apply rule] in two or more different rule statements and in that case the order the rule appears in the list takes precedence.

People who have a programming background often have difficulty grasping this. They always think rules work like a VB sub-routine or function where you step through it to get to the result. Rules are more like cell formulas in Excel, they all happen at the same time (except for the aforementioned order precedence).

[ioscat]

Hello, tomok.
I think you nearly got my question. I posted similar question in thread http://www.tm1forum.com/viewtopic.php?f=3&t=8738 . Suppose we have syntax:

Code: Select all

[]=S:IF(!Year @= '2011', 'Hello, world', CONTINUE);
[]=S:IF(!Year @= '2012', 'Goodbye, world', CONTINUE);
[]=S:'123 test';

So for years before 2011 and after 2012 the number of calculation is the biggest. Isn't it?

So suppose we have a very complex calculation that consist of several very complex calcilations. []=N:<...Calc1...>*<...Calc2...>*... And if the first multiplicator is zero so all expressions will be zero. Does TM1 provide the same interpretation or it is just calculates all expression?

[tomok]

Hello, tomok.
I think you nearly got my question. I posted similar question in thread http://www.tm1forum.com/viewtopic.php?f=3&t=8738 . Suppose we have syntax:

Code: Select all

[]=S:IF(!Year @= '2011', 'Hello, world', CONTINUE);
[]=S:IF(!Year @= '2012', 'Goodbye, world', CONTINUE);
[]=S:'123 test';

So for years before 2011 and after 2012 the number of calculation is the biggest. Isn't it?

So suppose we have a very complex calculation that consist of several very complex calcilations. []=N:<...Calc1...>*<...Calc2...>*... And if the first multiplicator is zero so all expressions will be zero. Does TM1 provide the same interpretation or it is just calculates all expression?

It's very simple. Since all these rule statements apply to the same area, then they are all in conflict and the order of precedence takes over. The first rule makes 2011 equal to "Hello, world" and then nothing (the CONTINUE part) for the other years. The next rule makes 2012 equal "Goodbye, world, and then nothing for the other years. Since the first rule already made 2011 equal to "Hello, world", then the CONTINUE command in this rule statement is ignored for 2011. The last rule statement makes all years equal to "123 test". Since previous rule statements have already made 2011 and 2012 equal to a particular string, this rule statement is ignored for these years and any other year in the cube is made equal to "123 test".

As to your question about multiplication by zero, TM1 rules, like any programming language that I am familiar with, follows the rules of algebra, such that multiplication by zero ALWAYS results in zero.

If your question is about what order are the IFs evaluated in a single rule statement, then the answer is in the order they appear. As I stated before, an IF condition can only be either TRUE or FALSE. If it is TRUE then the TRUE branch is followed. If that branch takes you to another IF, then that logic is followed, and so forth. This is true in every programming language I have ever worked in. This is also how Excel handles it's IF function. I don't see what's so hard to understand about that.

[ioscat]

)))
No that, i know that 0*X = 0
=)
I mean will it spent time to calculate all parts of multiplication if one them is already know to be zero? When a man solve some equation he doesn't have to calculate it all if there is multiplication by zero.

[tomok]

I mean will it spent time to calculate all parts of multiplication if one them is already know to be zero? When a man solve some equation he doesn't have to calculate it all if there is multiplication by zero.

You're asking a question that I doubt anyone on this board is going to know the answer to. The internal code behind TM1 has never been public and never will be. Someone at IBM, who has knowledge of the actual C++ code would have to chime in and even they might not know that because it may be dependent on what C++ does when part of a multiplication formula evaluates to zero.

[cgaunt]

Hello Isocat,

Your thread has already attracted the attention of some of the big contributors, so adding another opinion may not be the most helpful thing, but I think your OP may not have been fully understood. Your point, if I am correct, is that if the value is generated on account of hitting the first rule, and then the second and third rule are apparently ignored. You are wondering if this is faster that first rejecting the condition of the first, then the second then returning a value on account of the third rule.

I am not from IBM, nor do I have any insider knowledge nor know anything about C++. But to the best of my knowledge, which in this case comes from conversations with some of the older ex-applix folk, the rules are applied to the cube as a whole once all the sum effect of the rule order is resolved, so resolving ['2011'] and being 'false', then ['2012'] and returning 'false' then resolving in the 'everything else' pot will be no less or more time efficient than resolving just ['2011'] and returning the 'true' condition result. That the order is critical is undeniable. Once a cell or an array of cells has a calculation assigned to it, I will not try to resolve again. Only once everything is resolved is the effect of the rules applied to the cube.

If a calc returns a zero at the end, then it does not retrospectively re-evaluate the rules ahead of that condition and say, dont bother the answer will only be zero anyway. It still upholds the rule order as it compliles the rules.

As a general observation I have never noticed a discernable difference in the speed to apply rules on account of the number of lines that have to be resolved to generate the desired result. Its a bit like comparing the speed of sports cars. Some are faster than others, but all of them are faster than a 1963 VW beetle. The time overhead has always been in the feeders, or the opening of the cube for the first time.

Hope this helps. Happy to be contradicted if anyone else has any evidence to the contrary.

[mattgoff]

Let me take a swing at this. I believe the question is: given the following two techniques, is one or the other faster or are they the same?

Code: Select all

[]=S:IF(!Year @= '2011', 'Hello, world', CONTINUE);
[]=S:IF(!Year @= '2012', 'Goodbye, world', CONTINUE);
[]=S:'123 test';

Code: Select all

[]=S:IF(!Year @= '2011', 'Hello, world', 
          IF(!Year @= '2012', 'Goodbye, world', 
                              '123 test'
            )
       );

I have never seen this tested, nor have I done it myself, but I'd be interested to see the results. My uneducated guess is that TM1 is effectively compiling both to the same thing, so performance would therefore also be the same. I'd also guess that TM1 is smart enough to evaluate expressions in left-to-right order, to stop evaluating once it hits a determinative test, and to skip evaluating any non-relevant branches.

Matt

[Alan Kirk]

If your question is about what order are the IFs evaluated in a single rule statement, then the answer is in the order they appear. As I stated before, an IF condition can only be either TRUE or FALSE. If it is TRUE then the TRUE branch is followed. If that branch takes you to another IF, then that logic is followed, and so forth. This is true in every programming language I have ever worked in. This is also how Excel handles it's IF function. I don't see what's so hard to understand about that.

So I'm taking it that you haven't started programming in .Net yet, where, for example, the IIF() function also calculates the False result / executes the False code even if the test evaluates to True.

Matt hit the nail on the head. The key to this question is whether TM1 only evaluates only what it needs to, which I, too, suspect that it does, though the "multiply by zero question" is certainly an interesting one. It certainly can't do it all at the same time given that a complex formula often depends on the results of other formulas, which would need to be evaluated in sequence to get the correct result. (Just as Excel formulas do come to that, even though that may not be obvious to the Excel end user.)

[jstrygner]

I mean will it spent time to calculate all parts of multiplication if one them is already know to be zero? When a man solve some equation he doesn't have to calculate it all if there is multiplication by zero.

As a first thought it would be tempting to go this road. Once calculation algorithm finds zero and knows the whole formula is "multiple" multiplication it could return zero without checking rest of multiplication blocks. But what to do in case when last of those blocks in fact returns something like #N/A or #DIV/0? In such a case the multiplication result will not be zero anymore.

That is why I am affraid still all multiplication blocks would need to be evaluated (but this is just my thinking aloud).

[Alan Kirk]

But what to do in case when last of those blocks in fact returns something like #N/A or #DIV/0? In such a case the multiplication result will not be zero anymore.

Good point; that thought has crossed my mind as well.

(Although (being a smidge pedantic) since rules lack error handling I would never let a rule intentionally return an error value. Hence I always use the \ operator (to return 0 if the value being divided by is 0) rather than the / one for division.)