Internal Rules Using DB

[Herman Moller]

All,

I am having a debate with a colleague regarding writing TM1 rules in a internal cube.

We have a Demand cube:
Version,
Year,
Month,
Products,
Cost_Centre,
Measures: Operating Volumes, Operating Units and Demand

I would write a internal rule the following way:

['Demand']=n:['Operating Volumes']*['Operating Units'];

My colleague would write the rule the following way:

['Demand']=N:
DB('Demand',!Version,!Year,!Month,!Products,!Cost_Centre, 'Operating Volumes')*
DB('Demand',!Version,!Year,!Month,!Products,!Cost_Centre, 'Operating Units');

His argument is the following:

"This is a practice I developed on the strength of a long-lost paper explaining the TM1 consolidation algorithm: Square-bracket notation does not cause problems when reading leaf elements, but in some cases rules inadvertently (or intentionally) reference consolidated source cells and square-bracket notation in this case causes TM1 to change the evaluation order and execute a slower consolidation calculation. Using DB references allows TM1 to continue to use its fast consolidation algorithm even when reading from a consolidated intersection.
Consequently, I always use DB notation."

Any thoughts on this argument?

Thanks

Herman

[jim wood]

It's a new one on me. I used to work for Applix (back in the day) and they never told any of us about that. I'm not saying he's wrong mind, But I would never use DB internally uless I was doing something more than just a straight element call like that,

Jim.

[Herman Moller]

Jim,

Been working with TM1 for a number of years and have never heard this argument. My colleague comments are:

" I found it five or more years ago, and at the time the practice did indeed produce favourable results in testing, but I have since lost track of the original source of the information. It is certainly possible that the behaviour has changed in later versions of TM1, since it is not explicitly documented, but I have nevertheless continued to use DB references."

Thanks
Herman

[lotsaram]

New to me. I have always believed that tghe square brackets notation was just shorthand for the developer and that the rule is effectively converted to a full reference when the engine evaluates it.

I can't see any benefit in using full DB refs where not necessary (e.g. string rules and when nesting another function within the DB(). Although I do believe in always qualifying element references withing the square with the dimension reference without fail.

[mattgoff]

['Demand']=n:['Operating Volumes']*['Operating Units'];

His argument is the following:

"This is a practice I developed on the strength of a long-lost paper explaining the TM1 consolidation algorithm: Square-bracket notation does not cause problems when reading leaf elements, but in some cases rules inadvertently (or intentionally) reference consolidated source cells and square-bracket notation in this case causes TM1 to change the evaluation order and execute a slower consolidation calculation. Using DB references allows TM1 to continue to use its fast consolidation algorithm even when reading from a consolidated intersection.
Consequently, I always use DB notation."

There are a lot of cargo cult guidelines to TM1, but this one seems particularly strange to me. In particular, your example is an N: rule so it should never touch a consolidated cell. Should be easy enough to test though-- fire up your model on dev, pick a particularly expensive rule, and run/time it both ways.

The only issue I have with square-bracket notation is that it's very easy to silently break your rules:

1. write a rule using square-bracket notation
2. save rule (if there was ambiguity in your rule, you'd get a warning now)
3. add an element used in your rule to another dimension in the same cube (no warnings if ambiguity created in a rule)
4. rule silently breaks, you'll only be alerted when you next open the rule in the editor

It's an easy fix: just define the dimension name in the square-brackets (e.g. ['dimname':'elname']) but you rarely see this referenced in the docs or even here, it's always the extra-short form w/o dimname. I've been burned enough on this that all of my square-bracket rules explicity define the dimension now, no matter how unlikely I think it is that an element name will ever be duplicated in another dim.

Matt

[lotsaram]

The only issue I have with square-bracket notation is that it's very easy to silently break your rules:

1. write a rule using square-bracket notation
2. save rule (if there was ambiguity in your rule, you'd get a warning now)
3. add an element used in your rule to another dimension in the same cube (no warnings if ambiguity created in a rule)
4. rule silently breaks, you'll only be alerted when you next open the rule in the editor

It's an easy fix: just define the dimension name in the square-brackets (e.g. ['dimname':'elname']) but you rarely see this referenced in the docs or even here, it's always the extra-short form w/o dimname. I've been burned enough on this that all of my square-bracket rules explicity define the dimension now, no matter how unlikely I think it is that an element name will ever be duplicated in another dim.

Matt

Exactly, which is why I do the same.

[Mike Cowie]

This long-lost theory is news to me, as well.

I even remember running into a situation with a customer (this is going back to version 7 of TM1) where we replaced DB() references with equivalent internal [] references (wherever possible) and saw a measurable improvement in performance. I've not tested this result with newer TM1 versions - hopefully the Rules compiler is now smart enough to treat equivalent DB() and [] expressions in the same way.

That past performance experience and overall readability have been enough to convert me to use [] wherever possible. I agree completely on qualifying element names with the dimension names, too.

Regards,
Mike

[jim wood]

Ambiguity can be a pain. It's something that is easily avoided but some times you just don't have a choice espescially when doing the likes of allocations. I don't do the ['dimname':'elname'] by default either but knowing about it does help. The other thing I notice the other day is that how to append text (single pipe) is not in the help files either. Very shoddy.

[mattgoff]

Duh, obviously it could touch a consolidated cell on the RHS. Still not sure why that would matter unless there are special optimizations for DB that don't exist for bracket notation. I always assumed that bracket was just a compiler shortcut and that post-compilation they end up the same.

Matt

[tomok]

I may be wrong but I seem to remember an Applix person telling me years ago that the DB() and [] syntax was treated the same by the compiler, once the syntax of the rule was verified. Made perfect sense to me.

[Mike Cowie]

I may be wrong but I seem to remember an Applix person telling me years ago that the DB() and [] syntax was treated the same by the compiler, once the syntax of the rule was verified. Made perfect sense to me.

Makes sense to me, too, but I swear at one point (going back to at least 2000 or so) I ran into a situation where it didn't appear to be the case based on a measured performance difference. I honestly haven't seen anything like that since then, but then I've tried to avoid DB() when it isn't needed and the TM1 engine has obviously changed a lot since those days. I can't seem to dig up my old test results around that, either - maybe they're in the same place as that long-lost paper.

Also, the compiler does see [] a little differently in that it only sees [] notation as something returning a numeric value on the RHS of a rule, and you are forced to use DB() on the RHS if you need to pull through a string cube value for a string (S:) rule, even if a [] reference seems plausible. I've always found that to be really annoying. That still doesn't mean the compiled result is actually different, but it's one place where the compiler interprets them differently.