Internal Rules Using DB

hb522422

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

fs111

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.

gaohua5201

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

rampagews3

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.

jxs0103

Herman Moller wrote:['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:

• write a rule using square-bracket notation
  
• save rule (if there was ambiguity in your rule, you'd get a warning now)
  
• add an element used in your rule to another dimension in the same cube (no warnings if ambiguity created in a rule)
  
• 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

酥糖公子

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

• write a rule using square-bracket notation
  
• save rule (if there was ambiguity in your rule, you'd get a warning now)
  
• add an element used in your rule to another dimension in the same cube (no warnings if ambiguity created in a rule)
  
• 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.

王山振

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

socaca

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.

chenbaolei

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

碧·染

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.