TM1 Forum

I'm having an issue with one of my templates, and I'm curious to hear if people have seen this before or if there's something I haven't considered that I'm doing wrong.

I have a cash flow data entry template with accounts as rows and periods as columns. The first row is the starting AP balance (DBR, consolidation) followed by a number of C/F accounts (DBR, n-level) below it and ending up at a closing balance (Excel formula). In a couple of hidden rows, I calculate the total movement and DBS it to a B/S movement account (n-level under the AP consol). TM1 has a rule to calculate and roll forward the new opening balance (for a number of reasons I have movement and balance elements for every B/S GL account). The workbook is set to manual calculation. There are no DBRWs or DBRSes anywhere in the workbook, only DBR and DBS. I know I could replace the DBS with a rule, but for now I prefer it a DBS.

My problem is this: when I make a change to a C/F account and hit F9, the balances do not propagate to all future periods until I hit F9 a second time. I have tried Ctrl-Alt-Shift-F9 to rebuilt the dependency tree without luck. Bug? Other user error?

Thanks,
Matt

So, you are reading from the cube, calculating something in Excel, sending back to the cube, and reading a new value back (that is dependent on what you just sent AND expecting TM1 to do all this in one F9? I'm pretty sure that takes more than one calc cycle. At least that has been my experience.

You are expecting too much Matt. A single calculation can only pull data from the server or push data to it. As you are sending data that then changes dependent cells and then want to pull these values back to Excel I think it is normal that this would need 2x recalc in Excel.

Maybe I am expecting too much, but I thought that was the whole point of using DBR/DBS instead of DBRW/DBSW: to allow dependant actions. If your understanding is correct, when a user enters cash flow updates for a whole year they're going to have to press F9 not just twice, but up to a dozen times, once for each period to propagate through all of the DBR+manual->DBS->DBR chains. If that's the case, I 'll have to reconsider my use of DBSes and put in rules after all. The idea of telling my users to "just keep pressing F9 until the checknumbers are zero" is not appealing.

Matt

In my experience, the benefit of using DBR versus DBRW in dependent formulas is all for READ. For example, a DBRW formula that has one of the variables being passed based on another DBRW, or DBRA. This is where putting in DBR everywhere will assure there are no problems. Once you introduce a writeback with DBS, expecting a calculation in TM1, then getting that result back, you can't get that done all in one round trip (hitting F9).

The only difference between DBRW and DBR is how it is handled from a network point of view. If you have 10 DBR forumlas each will fire a request to the server. 10 DBRW formulas have those requests grouped in to a package and then sent. DBRW was introduced to help network performance. As far as I am aware they act the same from a processing point of view in an excel sheet. The only way you can work round this sort of thing is to deploy a macro that stops stops a DBS until conditions have met.

jim wood wrote:The only difference between DBRW and DBR is how it is handled from a network point of view. If you have 10 DBR forumlas each will fire a request to the server. 10 DBRW formulas have those requests grouped in to a package and then sent. DBRW was introduced to help network performance. As far as I am aware they act the same from a processing point of view in an excel sheet.

Not exactly; there is one critical difference. DBRs are evaluated separately from (and apparently ahead of) the DBRW batches that you describe. I'm not trying to be pedantic, it's just that this has one vital practical effect; it's the key reason that if you use a DB formula as an argument to another DB formula then the former one must be a DBR instead of a DBRW. Since the DBR will be evaluated before the DBRW batch that you correctly describe fires, it's the only way that DBRW formulas which use DBRs as arguments can know what the values of the arguments are before the batch fires. (Otherwise it's trying to evaluate its arguments at exactly the same time as it's evaluating the value.) It's why if you put a DBRW as an argument to another DBRW you'll usually get key errors, even though when you independently evaluate the element arguments there won't seem to be anything wrong with them. (Which is really an expansion of what Tomok was getting at.)

Re Matt's issue, I'd agree with the general opinion that you can't mix and match this way by direct formulas. If it's a non-Web environment though, it's possible to use VBA to snapshot the completed input and send the snapshot up to the server. Instead of [F9] the users would need to use a [Commit] or [Update] command button to do that instead of just [F9].

Alan it's not like you to be pedantic!! But I agree, the DBR forumla does fire first. I've used this when combining DBR and DBSW.

Alan Kirk wrote:

jim wood wrote:The only difference between DBRW and DBR is how it is handled from a network point of view. If you have 10 DBR forumlas each will fire a request to the server. 10 DBRW formulas have those requests grouped in to a package and then sent. DBRW was introduced to help network performance. As far as I am aware they act the same from a processing point of view in an excel sheet.

Not exactly; there is one critical difference. DBRs are evaluated separately from (and apparently ahead of) the DBRW batches that you describe. I'm not trying to be pedantic, it's just that this has one vital practical effect; it's the key reason that if you use a DB formula as an argument to another DB formula then the former one must be a DBR instead of a DBRW. Since the DBR will be evaluated before the DBRW batch that you correctly describe fires, it's the only way that DBRW formulas which use DBRs as arguments can know what the values of the arguments are before the batch fires. (Otherwise it's trying to evaluate its arguments at exactly the same time as it's evaluating the value.) It's why if you put a DBRW as an argument to another DBRW you'll usually get key errors, even though when you independently evaluate the element arguments there won't seem to be anything wrong with them. (Which is really an expansion of what Tomok was getting at.)

Re Matt's issue, I'd agree with the general opinion that you can't mix and match this way by direct formulas. If it's a non-Web environment though, it's possible to use VBA to snapshot the completed input and send the snapshot up to the server. Instead of [F9] the users would need to use a [Commit] or [Update] command button to do that instead of just [F9].

Can't DBRs be stacked infinitely, though? i.e. one DBR feeds another DBR which feeds another DBR? This implies multiple, synchronous/in-order round-trips between the client and the server, not just that DBRs take precedence over DBRWs. If this is the case, why does it matter that a DBS is in the mix? If the Excel dependency tree has to be followed, shouldn't TM1 process each DBR or DBS in the order it's presented?

Which triggers a thought: each one of my columns stands alone. The opening balance at the top of each column is a DBR that's not linked via an Excel formula to the prior column. Because of this, Excel doesn't know that the DBR at the top of column B depends on the DBS at the bottom of column A firing first. TM1 knows, of course, but Excel is picking the order and without that information it may calculate in an arbitrary order column-to-column.