Multiple TM1 Formulae in a single cell

[ZuluBlue]

Is there a limitation to the number of DBRW formulae that can be used in a single cell? (eg IF THEN ELSE)

[rmackenzie]

The official limit is 64 levels of nesting. This level of IF nesting would be truly ridiculous - you should be aiming to write one DBR(W) per cell and to minimise use of any other logic in the formula itself and implement the logic in the cube rules themselves.

[declanr]

From memory the limit of nested IF statements in Excel is 14; you can of course extend this a bit by getting creative.
On another note it's 20 nested IF statements in TI on last count.

And I haven't looked in a while so Microsoft or IBM may have extended their limits in more recent versions, doubt it though.

[declanr]

I stand corrected... I was thinking nested IFs rather than IFs and Elses but I can't remember where I had 14 from...

[ZuluBlue]

The official limit is 64 levels of nesting. This level of IF nesting would be truly ridiculous - you should be aiming to write one DBR(W) per cell and to minimise use of any other logic in the formula itself and implement the logic in the cube rules themselves.

Thanks Guys!

Robin, is there a reason why I should be aiming for one DBR per cell?

[rmackenzie]

It's a matter of maintaining reports in a way that they're auditable and transparent, I guess. There's no reason that simple arithmetic can't be programmed into TM1 rules and dimension structures - meaning that the logic (and implied business rule) is captured and maintained in the TM1 server rather than in Excel spreadsheets which rapidly become a nightmare to version control and manage etc. Traditionally, a big part of the TM1 sell depends on preventing 'Excel hell' and using one DBR(W) per cell is the cornerstone of this.

If you find yourself writing IF statements or other formulas using different DBR(W)s then have a look at adding extra elements/ consolidations to your dimensions to see if you can split out the logic in the rules. E.g.

Code: Select all

=DBRW(server:cube,elem1,elem2)+DBRW(server:cube,elem1,elem3)

could be replaced by

Code: Select all

=DBRW(server:cube,elem1,elem4)

if elem4 is a consolidation of elem2 and elem3.

Make sense?

<edit>A better example with cube rules would be if you need to do a multiplication in the Excel formula. E.g.:

Code: Select all

=DBRW(server:cube,elem1,elem2) * DBRW(server:cube,elem1,elem3)

could be replaced by

Code: Select all

=DBRW(server:cube,elem1,elem4)

if a rule existed in the cube like this:

Code: Select all

['elem4'] = N: ['elem2'] * ['elem3'];

</edit>

[Alan Kirk]

It's a matter of maintaining reports in a way that they're auditable and transparent, I guess. There's no reason that simple arithmetic can't be programmed into TM1 rules and dimension structures - meaning that the logic (and implied business rule) is captured and maintained in the TM1 server rather than in Excel spreadsheets which rapidly become a nightmare to version control and manage etc. Traditionally, a big part of the TM1 sell depends on preventing 'Excel hell' and using one DBR(W) per cell is the cornerstone of this.

If you find yourself writing IF statements or other formulas using different DBR(W)s then have a look at adding extra elements/ consolidations to your dimensions to see if you can split out the logic in the rules. E.g.

Code: Select all

=DBRW(server:cube,elem1,elem2)+DBRW(server:cube,elem1,elem3)

could be replaced by

Code: Select all

=DBRW(server:cube,elem1,elem4)

if elem4 is a consolidation of elem2 and elem3.

Another reason for using Robin's second method is that the DBRW formulas (as opposed to DBRs) transmit and receive data to and from the server in batches, whereas DBRs do it one value at a time, making them much slower than DBRWs. My understanding is that when you put 2 or more DBRWs in the one cell, though, they revert to DBR behaviour. If I can find the reference to that I'll add it here but don't have it to hand at the moment.

[ZuluBlue]

I thought there might be an impact to workbook performance or getting incorrect values. As a matter of principle I totally agree that all logic should reside in TM1, but implementing this into production is quite expensive and more often than not users might not even need or use this new rule or calculation. I normally protype in Excel and after a period would migrate to TM1 rules or TI.

Thanks Robin & Alan!