Anyway to prevent Files to Accidentally DBSW ?

[jboulay]

Hello. Help.

I accidentally wrote to TM1 database when my excel file containing DBRW functions was simply opened to review. Currently , unless I manually tell Excel to not automatic calculate, when I open a file, it writes to database if DBrw functions exist. Or if you have many files open and meant to Shift F9 or your did not hit shift as intended, but F9 instead , all sheets with DBRW functions would just write to DB?

How can we make this not so easy to avoid errors , mistakes loading to DB or make it one more step to confirm? Just like now when you sign on to Online banking, there's a second step to confirm/verify. It can be as a step going to File, data, click to send to DB, or just a pop up box that says you're about to send data are you sure?

Right now its just too easy to write to DB by simply by just excel calculating, it's just too easy...so many things could be opened causing to load wrong data. Maybe newer TM1 version has something else? I'll tell you later which TM version I'm using. I think I'm using version 10.2xxx. I'll confirm.
Thanks
Jean

[Wim Gielis]

Jean,

DBRW formulas on their own will not write data to a cube. Not in manual calc mode, not in automatic calc mode. So it has to be the case that you or the user enters a value over a DBRW. Or do you use a DBS function ?
A DBRW formula will only show what is currently in the cube, if you do not enter a value.

One way to approach this is a kind of confirmation or password in a different cell, say A1. If A1 matches the password, show the DBRW, if not, show nothing. So all affected DBRW formulas have to be nested in an IF - impacting speed of recalc. Or, in the properties of the websheet, disable write back.

[jboulay]

Jean,

DBRW formulas on their own will not write data to a cube. Not in manual calc mode, not in automatic calc mode. So it has to be the case that you or the user enters a value over a DBRW. Or do you use a DBS function ?
A DBRW formula will only show what is currently in the cube, if you do not enter a value.

One way to approach this is a kind of confirmation or password in a different cell, say A1. If A1 matches the password, show the DBRW, if not, show nothing. So all affected DBRW formulas have to be nested in an IF - impacting speed of recalc. Or, in the properties of the websheet, disable write back.

Hi Wim, I meant to say DBSW. I updated topic title. I'm new to this.

[jboulay]

Some people like in this thread below would put data in one excel sheet tab and DB formulas in separate sheet tab with if statements, if yes, then send to DB.

http://www.tm1forum.com/viewtopic.php?t=9317



But isn't this a headache? What if you have 20 divisions? you would have to duplicate each division? one sheet for data calculation and one sheet to send data? This is I think a design flaw of TM1. Why is TM1 using the F9 to send? the F9 serves its own purposes and that it to calculate, why is it also being used to send to DB? Sometime you just want sheets to be calculated and that's it.

Couldn't TM1 have simply created a menu item such as go to file, data, click option1 to send active sheet to DB and click option2 to send entire workbook to DB ? and disable the ability to send to open excel files to DB...since I don't think anyone would want to send all open files at once to db to avoid mistakes.


for now, how about a data validation list drop down box with correct cube names and incorrect cube names? Once correct cube name is selected, it sends active sheet data to DB? I'm just trying to find a best practice technique that works well. Anyone else have any ideas?

[lotsaram]

DBS, DBSS, DBSW formulas are a bit of a hanglover from the 80s & 90s. Back in the days when TM1 didn't have any ETL capability this was the only way to send data to the TM1 database. These days they aren't used that much.

Why is TM1 using the F9 to send? the F9 serves its own purposes and that it to calculate, why is it also being used to send to DB? Sometime you just want sheets to be calculated and that's it.

Nothing magic here. And no dubious design either. Calculating is actually all that is happening. It just so happens that the role and reason for being of a DBSW formula is to send data and on a recalculation event that's what the formula does.

for now, how about a data validation list drop down box with correct cube names and incorrect cube names? Once correct cube name is selected, it sends active sheet data to DB? I'm just trying to find a best practice technique that works well. Anyone else have any ideas?

What you are suggesting is pretty much the standard way of solving this problem. Typically you have a master cell with a true/false or yes/no flag with a caption next to it like "Send data?" then you have a cell holding the server:cubeName string with an IF formula along the lines of ...
=IF(bSend=TRUE,"server:cubeName","")
.. and then you link the cube name argument of all the DBSW formulas to this cell. That way if the send flag is off the cube name is blank and the vales have nowhere to go hence no updating.

[Steve Rowe]

A small extension to the conditional DBS is to put some VBA in the auto open event of the work book that turns the flag off and/or the before close event.

That way the workbook always requires the flag to be set manually and it doesn't matter if the workbook is saved with the flag on or the workbook is opened with automatic calculate on.

[mattgoff]

I meant to say DBSW. I updated topic title. I'm new to this.

You're new to TM1 and you just made a mistake that caused you a lot of heartache. But the root cause is your inexperience with TM1, not TM1's design. Everything in TM1 happens instantly by default, by design. This ensures that, with few exceptions, what you see in Excel is what's stored in TM1. This is an important feature-- relying on a "submit" button risks causing a mismatch between the data in your local instance of Excel (and being used for knock-on calculations in your model) and what's stored in TM1 and available for concurrent users.

It sounds like whomever built your spreadsheet did it wrong. There's a good chance that you don't even really need DBSW, and your spreadsheet should have been built with DBRWs. If DBSWs are necessary, it's trivial to build in a load flag if you want to prevent accidental loads. Depending on the situation (e.g. versioning) you should also probably be protecting data with server-side security as well.

Apologies if this sounds condescending or dismissive. I think as you work more with TM1 you will see that the current behavior is preferred to your proposal. If there is a problem in TM1 it's that it's very easy to make a small mistake that causes a huge headache. System design is always a balance between flexibility and safety, and TM1 is far to the side of flexibility, one reason it's so powerful.

Matt

[jboulay]

DBS, DBSS, DBSW formulas are a bit of a hanglover from the 80s & 90s. Back in the days when TM1 didn't have any ETL capability this was the only way to send data to the TM1 database. These days they aren't used that much.

Why is TM1 using the F9 to send? the F9 serves its own purposes and that it to calculate, why is it also being used to send to DB? Sometime you just want sheets to be calculated and that's it.

Nothing magic here. And no dubious design either. Calculating is actually all that is happening. It just so happens that the role and reason for being of a DBSW formula is to send data and on a recalculation event that's what the formula does.

for now, how about a data validation list drop down box with correct cube names and incorrect cube names? Once correct cube name is selected, it sends active sheet data to DB? I'm just trying to find a best practice technique that works well. Anyone else have any ideas?

What you are suggesting is pretty much the standard way of solving this problem. Typically you have a master cell with a true/false or yes/no flag with a caption next to it like "Send data?" then you have a cell holding the server:cubeName string with an IF formula along the lines of ...
=IF(bSend=TRUE,"server:cubeName","")
.. and then you link the cube name argument of all the DBSW formulas to this cell. That way if the send flag is off the cube name is blank and the vales have nowhere to go hence no updating.

Thank You lotsaram,

Also, in this post http://www.tm1forum.com/viewtopic.php?t=9317 ,

I'm unclear with their =SUBNM("local:actvsbud","","SendW01") statement. What is this formula really doing?


and there's an excel attachment with two formulas below:


Formula 1 :

=IF($D$4="YES",DBSWStage!C7,$B$1,$D$3,$A7,$B$2,C$6),"NO SEND")

..in this case the formula is reading from another sheet name state. My question here is this formula 1 would cause problems since since they're putting something inside the brackets of the DBSW formula? and TM1 does not support this?


Formula 2:
=IF($D$4="YES",DBSW(B7,$B$1,$D$3,$A7,$B$2,B$6),"NO SEND")

..In this case the formula should be ok since there's nothing foreign inside the normal DBSW formula?

Thanks

[mattgoff]

I'm unclear with their =SUBNM("local:actvsbud","","SendW01") statement. What is this formula really doing?

https://www.ibm.com/support/knowledgece ... ction.html

=IF($D$4="YES",DBSWStage!C7,$B$1,$D$3,$A7,$B$2,C$6),"NO SEND")

..in this case the formula is reading from another sheet name state. My question here is this formula 1 would cause problems since since they're putting something inside the brackets of the DBSW formula? and TM1 does not support this?

Why do you think you cannot put "something" inside the parenthesis? Excel will evaluate everything inside the TM1 function prior to sending the arguments to the TM1 server, so you can put whatever you want inside the function as long as every argument resolves to a valid reference.

Formula 2:
=IF($D$4="YES",DBSW(B7,$B$1,$D$3,$A7,$B$2,B$6),"NO SEND")

..In this case the formula should be ok since there's nothing foreign inside the normal DBSW formula?

What do you mean by "foreign"?

Have you considered doing some new user training? There are online courses available that are a good investment of your time.

Matt