DBS/DBSW

Post Reply
User avatar
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

DBS/DBSW

Post by 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
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: DBS/DBSW

Post by 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.
Last edited by Andy Key on Tue Jun 10, 2008 8:58 am, edited 1 time in total.
Andy Key
User avatar
jim wood
Site Admin
Posts: 3951
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: DBS/DBSW

Post by 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.
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
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: DBS/DBSW

Post by 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.
User avatar
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: DBS/DBSW

Post by 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
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: DBS/DBSW

Post by 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,
Technical Director
www.infocat.co.uk
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: DBS/DBSW

Post by 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.
Andy Key
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: DBS/DBSW

Post by 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! ;)
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: DBS/DBSW

Post by 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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Post Reply