Internal Rules Using DB
-
- Posts: 70
- Joined: Thu May 22, 2008 3:38 pm
Internal Rules Using DB
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
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
- Site Admin
- Posts: 3953
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Internal Rules Using DB
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.
Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- Posts: 70
- Joined: Thu May 22, 2008 3:38 pm
Re: Internal Rules Using DB
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
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
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Internal Rules Using DB
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.
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
- MVP
- Posts: 516
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: Internal Rules Using DB
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.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."
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
Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Internal Rules Using DB
Exactly, which is why I do the same.mattgoff wrote: The only issue I have with square-bracket notation is that it's very easy to silently break your rules:
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.
- 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
Matt
- Mike Cowie
- Site Admin
- Posts: 482
- Joined: Sun May 11, 2008 7:07 pm
- OLAP Product: IBM TM1/PA, SSAS, and more
- Version: Anything thru 11.x
- Excel Version: 2003 - Office 365
- Location: Alabama, USA
- Contact:
Re: Internal Rules Using DB
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
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
Mike Cowie
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
- jim wood
- Site Admin
- Posts: 3953
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Internal Rules Using DB
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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- mattgoff
- MVP
- Posts: 516
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: Internal Rules Using DB
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
Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
- MVP
- Posts: 2832
- 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: Internal Rules Using DB
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
- Site Admin
- Posts: 482
- Joined: Sun May 11, 2008 7:07 pm
- OLAP Product: IBM TM1/PA, SSAS, and more
- Version: Anything thru 11.x
- Excel Version: 2003 - Office 365
- Location: Alabama, USA
- Contact:
Re: Internal Rules Using DB
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.tomok wrote: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.
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.
Mike Cowie
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!