DBS/DBSW

[mattgoff]

Anyone have some best methods to share regarding model-building with DBS/DBSWs? We've been using simple input templates until now, but I'd like to get some of the more advanced users to tie their models directly into TM1. However, since these models get archived/reopened a lot, I'm concerned about old data getting inadvertently loaded into TM1 if someone drags out an old version to check a number.

Is it as simple as building a "load" flag in to the model and wrapping every DBSW within an IF? i.e.

Code: Select all

=IF(load_flag=1,DBSW(....),"n/a")

Matt

[Andy Key]

We use the =IF(load_flag...) method. Not only does it force the user to consciously click a button to save the data (which you can follow up with a Yes/No dialog to double check that they meant to click it), but it also gives you a chance to do some validation before the data is sent.

[jim wood]

Hi Matt,

The iF statement is the simplest way of controlling the send. If you combine it with a macro button it works well. As for DBSW. This sends once everything else has been calculated. Be careful when you use this combined DBR functions. Calculation order can be a killer. In one particular instance the macro I built used to calculate, copy and paste the DBR values to another sheet, load the calculate control marker, recalculate and send. Lastly it set the save control cel back to 0.

I hope that helps,

Jim.

[David Usherwood]

We've done a refinement of the IF approach using an Excel macro to turn the =DBSW( formulae into comments and back to active formulae. Less cluttered than the IF and nice and quick.

[mattgoff]

Thanks all for the ideas. I've gone with the IF + macro + warning dialog method-- seems like that's what you're all doing.

David: can you explain more about your approach?

Matt

[Steve Rowe]

Another good tip here is to add some VBA to the close / deactivate events of the work book that set the load flag to false. This avoids data being sent when the workbook is first opened and calculation is set to automatic.

Sorry for posting on old posts, my first time on for a while..
Cheers,

[Andy Key]

Matt,

To clarify David's approach, the idea is that if you know where all your DBSWs are, you can write two pieces of code to act on that range (or pass the range as a parameter). The first loops through the range and looks for =DBSW in a cells formula, if it finds it, it prefixes the formula with a Chr(34) so Excel treats it as a piece of text. The second does the reverse.

To send the data to TM1 you run the second piece of code i.e. turn all the pieces of text into formulae, then calculate, then run the first piece of code to turn all the formulae back to text.

This only really works if the DBSWs are out of view otherwise things look messy.

[Eric]

I go with 2 tabs. Tab 1 a user entry tab. Tab 2 all of my DBS/DBSW formulas. On Tab 1 I use the server name as a "flag"

Cell A1 = TM1ServerName

Cell A2 = DBS(SendValue, A1&":"&Cube,Dim1,Dim2,Dim3,etc)

Obviously is the server name is blank or wrong nothing is loaded. If it is filled in correctly it will load.

It is an easy solution: to audit, for end users to understand/recreate, and no programming required. Al l are pluses in my book!

[Martin Ryan]

In addition to these methods, you may want to also use security to ensure previous months/versions are locked so that users can't overwrite those numbers even if they do go past your macro.

Martin