Rule to STET static version affecting calculations

[dan.kelleher]

Hi,

I have a series of static versions within a Version dimension that are children of the 'Static Versions' consolidation. Non static versions have no parents.

After inserting the following rule directly below the SKIPCHECK; statement to STET n-level data for versions so they can be value pasted, figures for non-static versions appears to have changed:

Code: Select all

# STET static versions to allow for value pasted values at N level
[] = N: If(ELPAR('Version', !Version, 1) @= 'Static Versions', STET, CONTINUE);

Am I correct in thinking that this rule does not require the AllowSeparateNandCRules parameter to be set to T in the config file as it uses the CONTINUE command?

I know it is not best practice to set this parameter to T so want to avoid if possible.

Thanks,

Dan

[Kyro]

Aside from changing that rule to:

Code: Select all

[] = N: If( ELISANC('Version', 'Static Versions', !Version) = 1, STET, CONTINUE);

That rule should work, two things that could go wrong with the current rule is:

• The !Scenario being calculated exists as a child of another consolidation element first before being a child of 'Static Versions'
• The name of the consolidation element 'Static Versions' is actually 'static versions' (I can't recall if rule strings are case sensitive - someone confirm please?)

But other than that I have noticed some issues when using 9.0+ and a rule which has both a STET and also applies to [] - everything.

[dan.kelleher]

Rules are not case dependent.

I agree with the rule change, however, in my example, there is only 1 consolidation, and only static forecast versions are children of it.

Thanks,

Dan

[garry cook]

I've always done it the same way you've got it there (as do a number of folk). You've probably gone through this list already but a few things that have caused probs in the past -

Obvious

Rule precedence but you're saying it's first thing after skipcheck so can't be that.
Version moving in/out of the consolidation being used by the rule (usually by TI).
Users with security rights actually changing the numbers.
Rogue TI code.
Users frankenlinking an old sheet with DBS they're unaware of accidentally overwritting numbers without realising.
You're only locking down the N: level rules so changes to C: level rules will still affect static versions.
Elements that only have data on static versions (ie don't show on the more visible live forecast/budget scenarios) being deleted in a badly run archive clean up.

Less obvious

Users without security rights to the data calling a TI (thus bypassing security) which changes the data.
Someone unknowingly using spreading shortcuts in excel / cube viewer (eg, accidentally typing c into a consolidated cell to wipe everything below it when trying to do a copy / paste).
.xru / .rux conflicts if out of sync and RuleLoadFromFile command used in a TI.


Far from an exhaustive list but if there's an issue with a static version, I tend to find that it's one of the above. Obviously easier to narrow down if you've got logging on the cube in question.

[Catherine]

I know it is not best practice to set this parameter to T so want to avoid if possible.

Hi,
Could you tell me more about that? Why is it not the best practice to have this parameter set to T ?
Thanks

[qml]

It's quite simple really. Not allowing separate N and C rules means that when TM1 is scanning through the rule file (which it does every time you want to retrieve any numbers) and it finds a range that matches the cell value being requested (but not the type: N or C), it can stop scanning the rule file, knowing there cannot be more rules of different type assigned to that same range. When you allow splitting C and N rules, TM1 needs to keep scanning the rule file until it either reaches the end or finds a matching rule. So - in most cases calculation performance is better when separating N and C rules is not allowed.

[Catherine]

It's quite simple really. Not allowing separate N and C rules means that when TM1 is scanning through the rule file (which it does every time you want to retrieve any numbers) and it finds a range that matches the cell value being requested (but not the type: N or C), it can stop scanning the rule file, knowing there cannot be more rules of different type assigned to that same range. When you allow splitting C and N rules, TM1 needs to keep scanning the rule file until it either reaches the end or finds a matching rule. So - in most cases calculation performance is better when separating N and C rules is not allowed.

Thank you for your explanations.
I had never thought about that as a possible optimization.
I have two questions:
1. Has anybody really noticed better performance with the parameter not allowing to separate N and C rules ? Could you quantify the benefit ?
2. What if we use "continue" instructions in rules ? Do you think it is compatible with the fact of having the parameter AllowSeparateNandCRules to False ?

Thanks

[Gregor Koch]

It's quite simple really. Not allowing separate N and C rules means that when TM1 is scanning through the rule file (which it does every time you want to retrieve any numbers) and it finds a range that matches the cell value being requested (but not the type: N or C), it can stop scanning the rule file, knowing there cannot be more rules of different type assigned to that same range. When you allow splitting C and N rules, TM1 needs to keep scanning the rule file until it either reaches the end or finds a matching rule. So - in most cases calculation performance is better when separating N and C rules is not allowed.

qml,
Quite simple? Have you ever tested this?

Could you maybe point me to any documentation that would state what your are claiming about the separate N and C rules especially the part about "scanning through the rule file (which it does every time you want to retrieve any numbers)"???
I very much doubt that TM1 is scanning a (rule) file (uhm, memory based) every time you request a number but maybe you could enlighten me.

[Gregor Koch]

Catherine,

1. Personally I have not because I always set the Parameter to T. As mentioned above I am doubtful that there actually is a performance difference. IMHO this is all about syntax the way you see it before it gets compiled and not the way the calculation engine will evaluate whether and which rule applies to a cell or range of cells (in memory by the way and not in the rule file).

2. It is compatible.

Also maybe a have read in the Operation Guide (at least the version for 9.5.1) which lists an actual advantage (at least that is how I understand it) of using this syntax.
http://publib.boulder.ibm.com/infocente ... Rules.html

I would have hoped that IBM would have stated "Don't turn it on unless you want TM1 to scan the whole rule file and this will slow down your calculations" if it was the case.

There are other threads in this forum where this parameter is discussed. Have a search.

[qml]

Could you maybe point me to any documentation that would state what your are claiming about the separate N and C rules especially the part about "scanning through the rule file (which it does every time you want to retrieve any numbers)"???
I very much doubt that TM1 is scanning a (rule) file (uhm, memory based) every time you request a number but maybe you could enlighten me.

Well, I don't have any proof for that, let alone a documentation extract (but just because something is not documented doesn't mean it's not true, especially in TM1's case ).

However, it is obvious (and mentioned in the documentation) that rules precedence is defined by rules order in the rule file. I never said TM1 opens the file from the disk every time a value is requested. But it can be inferred that there is some sort of parsing going on every time a value is requested from a cube with rules. Whether this happens using plain text search or some pre-parsed code, doesn't really matter. TM1 will find the first rule that can be applied and will ignore the ones below.

Also, it is testable (try it yourself in a big model) that using CONTINUE instead of STET slows rule calculations down because of the very fact that the parser needs to keep looking for any rule that can be applied instead of stopping at STET.

Now, I have never actually tested what effect using separate N and C rules has on performance (it's probably measurable, but minuscule), but I honestly cannot think of any other reason for that parameter's existence than to be able to sacrifice performance to gain rule writing flexibility or vice versa. Can you, Gregor?

[Gregor Koch]

Well, I don't have any proof for that, let alone a documentation extract (but just because something is not documented doesn't mean it's not true, especially in TM1's case ).

I hear you on the latter part.

However, it is obvious (and mentioned in the documentation) that rules precedence is defined by rules order in the rule file.

That is correct, if subsequent definitions are for the same range of cells as only one rule can apply to a cell at a given point in time.

I never said TM1 opens the file from the disk every time a value is requested.

Apologies that I have interpreted what you wrote in that way.

But it can be inferred that there is some sort of parsing going on every time a value is requested from a cube with rules. Whether this happens using plain text search or some pre-parsed code, doesn't really matter. TM1 will find the first rule that can be applied and will ignore the ones below.

Let’s just say something happens in memory but we are not quite sure what it is and we are still speculating. But I’ll follow your lead about the ‘first rule’ and ‘ignore the ones below’.

Wouldn’t you agree that when you request a rule calculated cell say on C level and you have

[‘something’]=C:

and somewhere else

[‘something’]=N:

once you have hit the ‘first line’ (are there any lines of this in memory or is it just addresses, numbers and what nots?) TM1 will stop looking because it has found the first applicable rule on C: level. End of story.
If I further follow your argumentation about the ‘scanning the rule file’ this might be a problem if the rules are the other way around and thousands of lines of code apart and because TM1 has to scan every single rule in the ‘file’ and it takes time until it hits the proper definition. Right?
BTW, who would write the two lines far apart anyways?

If indeed TM1 really has to scan every single (line of) rule every time you retrieve a cell wouldn’t this mean that the last rule in the file will always perform the worst (negating all other differences between the rules) because TM1 had to scan everything before it? Mmmmh. This would certainly change my way of writing rules quite a bit. I'd go, hey, let's put the ones we don't need as much at the end of the .rux file with all that scanning going on.
Maybe, and just maybe, TM1 is smart enough to attach a rule to a cell or range of cell and knows that it applies because when compiling the rules it collects all the LHSs of the rules for that range and puts them together side by side in memory or something magical so it does not have to do all that scanning. Probably just wishful thinking, but you get my drift.

Also, it is testable (try it yourself in a big model) that using CONTINUE instead of STET slows rule calculations down because of the very fact that the parser needs to keep looking for any rule that can be applied instead of stopping at STET.

Is this really a test for the performance with and without separate N: and C: rules? I am not quite sure. Wouldn’t the test be to turn the setting on and off and have two sets of the (logically) same rules with and without separate N: and C: rules? Anyways, I am being very picky and it sort of makes sense. But is it proof of how the parser works or that the config setting leads to performance degradation, again I think no.

Now, I have never actually tested what effect using separate N and C rules has on performance (it's probably measurable, but minuscule),

Again
Because you previously mentioned that

So - in most cases calculation performance is better when separating N and C rules is not allowed.

I was asking, and really out of curiosity and because it was new to me, if there is any reference to this anywhere or maybe any personal experience.

but I honestly cannot think of any other reason for that parameter's existence than to be able to sacrifice performance to gain rule writing flexibility or vice versa. Can you, Gregor?

First I would ask why the Syntax was changed in the first place. Was it because the old Syntax (separate N: and C: rules, <V7) allowed a way of writing rules that is causing performance to degrade? Did the performance of rules improve with the new Syntax? Possibly, but I don’t know for sure.
Then I would ask why the Config setting was introduced? Probably it was because of all the old models that used the old syntax and to allow a more flexible way of writing the rule.
Your conclusion sort of makes sense to me but it is just a theory and I think it is fair for me to question it or raise doubts.

Cheers

[qml]

Ok, so...

I ran some test to prove myself right or wrong. I created a cube with a BIG rule file (16000 rule statements) and measured report refresh times for values calculated by the first rule in he rule file and by the last rule in the rule file. I will try and provide a walkthrough with screenshots in this thread a bit later, but the main assumptions were:

- all rules were covering mutually exclusive areas,
- there were no CONTINUEs or STETs,
- each rule was the same in terms of calculation complexity,
- report was built in Excel using DBRW() functions without using VIEW() and the median refresh time was close to 15 seconds,
- refresh times were measured using TM1Tools (to the nearest second),
- report was refreshed multiple times for each of the parameters that determined whether rule #1 or rule #16000 was used,
- after each refresh I made sure that calculation cache was emptied by changing all the underlying numbers used in calculations and therefore forcing TM1 to actually use the rule calculation engine every time,
- each refresh was executed multiple times to capture statistically significant differences between query execution times and also reduce the effect of poor measurement precision on the outcome.

Here are my findings:

1) There is a limit of 16383 of rule statements attached to a cube - any more and you get an error message.

2) There is a measurable difference between execution times for rule #1 and rule #16000. Across my trials there never was a time that rule #1 performed worse than rule #16000. In fact, the difference was never less than 2 seconds. The average refresh time for rule #16000 was around 20% longer than for rule #1.

So it does seem that rule ordering has an impact on performance. This impact is really minuscule unless using a really big rule file which I admit is rarely the case. However, it does seem that placing rules that are used more often earlier in the script can increase average cube responsiveness.

[Martin Ryan]

That's very interesting. I'd never really thought about it, but guess I'd had the same underlying assumption as Gregor that the rules got attached to a cell so order wasn't a big deal, but now that you mention and test it, it makes complete sense. Thanks for the effort.

[Gregor Koch]

That is very interesting indeed.
Do you mind posting the little test model?
Have you tried whether there is a difference of calc speed when you have AllowSeparateNandCRules turned on?

[rmackenzie]

Do you mind posting the little test model?

Yes... I'd quite like to see it as well... peer review being the cornerstone of modern science, and all that

[qml]

I will try and provide a walkthrough with screenshots in this thread a bit later

Don't worry, a promise is a promise. I will run some more scenarios and post everything here so that you can critique my methods and results all you want.

[Catherine]

Very interesting.
Thanks a lot