Anyway to prevent Files to Accidentally DBSW ?

Post Reply
jboulay
Posts: 4
Joined: Mon Nov 13, 2017 10:04 pm
OLAP Product: TM1
Version: unknown
Excel Version: 2016

Anyway to prevent Files to Accidentally DBSW ?

Post by jboulay » Sun Dec 10, 2017 9:54 pm

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
Last edited by jboulay on Mon Dec 11, 2017 4:04 am, edited 1 time in total.

Wim Gielis
MVP
Posts: 1733
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Anyway to prevent Files to Accidentally DBRW ?

Post by Wim Gielis » Sun Dec 10, 2017 10:54 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

jboulay
Posts: 4
Joined: Mon Nov 13, 2017 10:04 pm
OLAP Product: TM1
Version: unknown
Excel Version: 2016

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by jboulay » Mon Dec 11, 2017 3:46 am

Wim Gielis wrote:
Sun Dec 10, 2017 10:54 pm
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.
Last edited by jboulay on Mon Dec 11, 2017 4:05 am, edited 1 time in total.

jboulay
Posts: 4
Joined: Mon Nov 13, 2017 10:04 pm
OLAP Product: TM1
Version: unknown
Excel Version: 2016

Re: Anyway to prevent Files to Accidentally DBRW ?

Post by jboulay » Mon Dec 11, 2017 4:03 am

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
MVP
Posts: 3077
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by lotsaram » Mon Dec 11, 2017 8:06 am

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.
jboulay wrote:
Mon Dec 11, 2017 4:03 am
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.
jboulay wrote:
Mon Dec 11, 2017 4:03 am
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Steve Rowe
Site Admin
Posts: 1776
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by Steve Rowe » Mon Dec 11, 2017 10:01 am

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.

User avatar
mattgoff
MVP
Posts: 511
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by mattgoff » Mon Dec 11, 2017 4:50 pm

jboulay wrote:
Mon Dec 11, 2017 3:46 am
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
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

jboulay
Posts: 4
Joined: Mon Nov 13, 2017 10:04 pm
OLAP Product: TM1
Version: unknown
Excel Version: 2016

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by jboulay » Tue Dec 12, 2017 8:57 pm

lotsaram wrote:
Mon Dec 11, 2017 8:06 am
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.
jboulay wrote:
Mon Dec 11, 2017 4:03 am
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.
jboulay wrote:
Mon Dec 11, 2017 4:03 am
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

User avatar
mattgoff
MVP
Posts: 511
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Anyway to prevent Files to Accidentally DBSW ?

Post by mattgoff » Tue Dec 12, 2017 10:13 pm

jboulay wrote:
Tue Dec 12, 2017 8:57 pm
I'm unclear with their =SUBNM("local:actvsbud","","SendW01") statement. What is this formula really doing?
https://www.ibm.com/support/knowledgece ... ction.html
jboulay wrote:
Tue Dec 12, 2017 8:57 pm
=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.
jboulay wrote:
Tue Dec 12, 2017 8:57 pm
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
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

Post Reply