Data not being sent via DBSW

[kabooooooom]

Hi there, I'm a newbie to the forum.
I have a spreadsheet that simply does some calculations on one page and send the results to a cube using DBSW statements.

The issue I'm having is that when I press PF9 the spreadsheet calculates but only some of the values get sent. In some cases apparently random numbers are getting sent.
If I go into an offending cell and then calculate it works, but the cells that work/don't work is fairly random.

In the past I've rectified issues akin to this by creating a new version of the offending spreadsheet as sometimes TM1 doesn't seem to like "organic" spreadsheets, but this time rebuilding hasn't worked.

There is nothing wrong with the formulae so it looks like TM1 is getting confused in some way.

Any suggestions appreciated (noting I'm an experienced user as opposed to being a developer so nothing too techy please).

Thanks

[declanr]

Behaviour like this can occur if the formula in which you are trying to send is dependent on other TM1 formulas that have not yet calculated.

In your case are the send formulas dependent on others?

[Michel Zijlema]

The dependent formula mentioned by declnr is the most likely cause of your issue. If an DBSW formula depends on a DBRW formula than make the inner (DBRW) formula a DBR formula.
Another cause could be that you have multiple DBSW formulas writing to the same cell, where every consecutive formula that writes to the same cell will overwrite the value of the previous formula).

Michel

[kabooooooom]

Thanks for the suggestions.

The use of DBR rather than DBRW (or DBS rather than DBSW when used with DBRW) works but was too slow across our network.

I was already trying to ensure the calculations were performed before attempting to send (via recalc1).

In the end I've resorted to reading the data in using DBRW, perform the calculations required, paste these as values as an extra step and then use DBSW to send the pasted values. Not very elegant but faster than the alternatives.

[qml]

I'm glad you managed to fix your issue, but I just want to point out one more thing.

Namely, pulling data from TM1 into an Excel spreadsheet only to apply some calcs and then send the data back to TM1 is generally a bad idea. You could achieve whatever you want to achieve with a TI script and/or rules, which would be more robust and faster by many orders of magnitude. Why have (and pay the high price for) a powerful multidimensional server-side calculation engine of TM1 if you're using the client-side Excel calulation engine instead?

[mattgoff]

pulling data from TM1 into an Excel spreadsheet only to apply some calcs and then send the data back to TM1 is generally a bad idea. You could achieve whatever you want to achieve with a TI script and/or rules, which would be more robust and faster by many orders of magnitude. Why have (and pay the high price for) a powerful multidimensional server-side calculation engine of TM1 if you're using the client-side Excel calulation engine instead?

Well, not always. For example, a lot of our users have models they use for forecasting which combine a mixture of historical actual and previous forecast data extracted from TM1 with misc other data from outside of TM1. In these sorts of situations, it's definitely appropriate to mix DBR and DBS and do it in an Excel model instead of building it all in TI. This also makes it completely self-service, so each business unit owner can adjust their own model w/o needing to find an admin to change TI.

Matt

[kabooooooom]

Exactly that Matt.

We have a very complicated and slick package for setting claims reserves that is the perfect marriage of Excel and TM1.

No way rules or TI could replace this

[tomok]

Exactly that Matt.

We have a very complicated and slick package for setting claims reserves that is the perfect marriage of Excel and TM1.

No way rules or TI could replace this

That's a pretty bold statement for someone who didn't know you should never mix DBSW and DBRW formulas in the same sheet. I would counter that there's no way rules or TI could replace it given your knowledge of the product. I will give you that it's probably easier to build what you've done but I would also counter that you are missing the benefits of locking down the calcs by leaving some of them in Excel.

I've done a number of insurance models in TM1 and I've never left anything in Excel, other than input templates and active form reports.

[qml]

I've yet to be convinced that there are models out there where it might be a good idea to apply calculation logic in Excel and not in TM1. I have personally never seen a convincing case for that and I would argue that if it is in fact a better idea in some very rare and very specific cases, then maybe Excel should not be paired with TM1 for these cases in the first place.

[mattgoff]

I've yet to be convinced that there are models out there where it might be a good idea to apply calculation logic in Excel and not in TM1. I have personally never seen a convincing case for that and I would argue that if it is in fact a better idea in some very rare and very specific cases, then maybe Excel should not be paired with TM1 for these cases in the first place.

For permanent, static calculation logic, I'd probably agree with you. But many things aren't. Sure, I can't imagine logic which couldn't be built in TI/rules, but that would require developer time. Frankly, my time is much better spent writing code for processes which are static, not rewriting the forecast script for the 10th time when someone decides the logic needs to change for their 20 depts out of 2000. And that person wants to make the change NOW, not open a bug and wait for it to hit the top of my queue in two weeks. If I had a team of developers, or if the downside of a bad Excel model was greater (or if I wanted more job security) maybe I'd be in your camp. But if we insisted that ONLY TI could handle models our system would do a lot less. TM1 is all about self service. By allowing reports to be built in Excel, customized reports are more accessible more quickly to more users. I really don't see why that can't extend to calculation logic.

Matt

[tomok]

Matt, it appears you have the perfect storm for a powerful and flexibe planning environment; skilled and knowledable users and a rock-solid TM1 administrator watching their backs. Sadly, I would say you are in the extreme minority here. In my experience it is the exact opposite; inept users and a half-assed attempt to assign a TM1 administrator, who didn't really want to do it but was forced to by his boss. Most of the time you are better off locking down as much as you can. The more you leave open to the users the more things will go wrong and there will be zero audibility when manageemnt wants to know why a certain number appears out of whack.

[kabooooooom]

Exactly that Matt.

We have a very complicated and slick package for setting claims reserves that is the perfect marriage of Excel and TM1.

No way rules or TI could replace this

That's a pretty bold statement for someone who didn't know you should never mix DBSW and DBRW formulas in the same sheet. I would counter that there's no way rules or TI could replace it given your knowledge of the product. I will give you that it's probably easier to build what you've done but I would also counter that you are missing the benefits of locking down the calcs by leaving some of them in Excel.

I've done a number of insurance models in TM1 and I've never left anything in Excel, other than input templates and active form reports.

Rather than trade insults I'll explain further.
I've actually been using TM1 for around 15 years, in the early days helping to design stuff but more latterly as a user as I've progressed within the company.
Our use of Tm1 is along the lines of input cubes and output cubes. In the middle is a sophisticated modelling tool built in Excel that relies on the skill and judgement of business analysts to convert those inputs into sensible, meaningful outputs. I do not believe this middle stage could be replaced by TM1 cleverness.

[tomok]

Rather than trade insults I'll explain further.
I've actually been using TM1 for around 15 years, in the early days helping to design stuff but more latterly as a user as I've progressed within the company.
Our use of Tm1 is along the lines of input cubes and output cubes. In the middle is a sophisticated modelling tool built in Excel that relies on the skill and judgement of business analysts to convert those inputs into sensible, meaningful outputs. I do not believe this middle stage could be replaced by TM1 cleverness.

Sorry, not meant to be an insult, just an observation. If I had a dime for every post where someone is explaining a problem they are having with TM1 and they say, "it can't possily be x" and it actually ends up being because of x, I would be a rich man. I've been developing in TM1 longer than you've been using and I would never say "TM1 can't do it" because the chasm between what I do know about the tool is still far outweighed by what I don't know about it.