How to Use DBSW

Post Reply
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

How to Use DBSW

Post by CiskoWalt »

Group.

See attached.

I know that this topic has come up a few times. How do we use DBSW with DBRW functions in the same workbook?

When you wish to optimize the users' financial models for the WAN, we are told to use the TM1 functions: VIEW, DBRW, and DBSW.

I am able to use the VIEW function and the DBRW function; however, the majority of the time I am not able to use the DBSW function because a DBSW function cannot reference/depend on a DBRW function.

It does not state this clearly in IBM's document titles " Slow TM1 Performance"
"8. Make sure to use WAN-optimized functions in the sheets. For example, use DBRW/DBSW, instead of DBR/DBS. Keep in mind that a DBRW function should not reference another DBRW cell, so there are situations where you will need to keep a DBR in a sheet, but the bulk of them can be changed to DBRWs for better performance. "

The Modeling Process
1. Most of the models pull data from a TM1 cube using DBRW.
The user pulls actuals for closed months (J16=1) and applies some logic (Prior months value * a growth rate) for months that have not closed.

2. When the user is satisfied with he result, the data for the actual and projected future values are sent to a future time periods
2014 Jan ...2014 Dec.....Jan 2015....Jan 2018...Dec 2018

There are approximately 20,000 send formula. It takes 30 minutes over the WAN

=IF(J$16=1,DBRW($B$2,$A$5,J$5,$B$1,$A28,J$1,J$2,J$6,J$3)/1000,I28*(1+DBRW($B$2,$A$5,J$8,$B$1,$A28,J$1,J$2,J$6,J$3)))

Some solutions:

1. One of the solutions that was mentioned in this forum is to make the calculation a rule in TM1. In this way the only thing sent over the WAN is the growth rate.
I do not know if this method can be used in every financial model.

2. A second solution mentioned is to convert the DBRW to values and then send the data using DBSW. Can the TM1 Tools add-in do this?

Is there a better solution that I am not seeing?
Attachments
TM1_SomeTips_08_15_2013_WJC.pptx
(1.29 MiB) Downloaded 522 times
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: How to Use DBSW

Post by Steve Rowe »

Perhaps a VBA approach?

Push out your DBSW space as a text file to a load area and then read it in with a chore / TI running on a short cycle (or an action button if you prefer).
Technical Director
www.infocat.co.uk
Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: How to Use DBSW

Post by Christopher Kernahan »

If I'm not missing something, can you not;

- put the DBRWs and DBSWs on separate tabs,
- replace the DBRW references in the DBSW formula with Excel references to the DBRWs on the separate tab,
- refresh the DBRW tab,
- and then refresh the DBSW tab to load the data?
Ken Vuong
Posts: 29
Joined: Wed Jul 02, 2008 1:20 pm

Re: How to Use DBSW

Post by Ken Vuong »

I would just have the DBRW and DBSW on the same sheet.

Then put an if statement to flag a certain cell (Y or N) so DBRW only activates if the cell matches the criteria.
Something like =if(C4="Y",DBSW(....), "not sent")

Can even go further to have an Import button that activates the flag, recalculate to do the DBSW and change the flag back to N, so DBSW is not active.
More control that way.

Hope this helps
Ken
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to Use DBSW

Post by lotsaram »

Ken Vuong wrote:I would just have the DBRW and DBSW on the same sheet.

Then put an if statement to flag a certain cell (Y or N) so DBRW only activates if the cell matches the criteria.
Something like =if(C4="Y",DBSW(....), "not sent")

Can even go further to have an Import button that activates the flag, recalculate to do the DBSW and change the flag back to N, so DBSW is not active.
More control that way.

Hope this helps
Ken
From a performance point of view its much better to keep the DBSWs clean and not mix with any boolean or any other formulas. The best way to do this is to have the trigger condition/logic within the cell to be used as the server:dimension reference and return a blank string if the condition is not met.
e.g. In Cell A1 =IF(bDoSend,"server:cube","")
In the DBSW cells =DBSW(nValue, $A$1, ...)
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

Re: How to Use DBSW

Post by CiskoWalt »

Folks,

I have attached a workbook that makes use of the TM1 sample database named "PDATA"

I inserted an element in the actvsbud dimension named SendW01.

the workbook just pulls data from the Actual elements and attempts to send it to the SendW01 element.

Have tried some of the suggestions:

1. have the DBRW and DBSW in the same sheet. - see sheet named "DBRW_and DBSW"
2. have a send flag
3. put the DBRWs and DBSWs on separate tabs, -- See sheets named STAGE and DBS_FROM_STAGE
- replace the DBRW references in the DBSW formula with Excel references to the DBRWs on the separate tab,
- refresh the DBRW tab,
- and then refresh the DBSW tab to load the data?

All of these method produce the same result because the DBSW formula depends on the DBRW formula

Does anyone use DBSW?

Thanks,

Walt
Attachments
TEST_OF_DBSW_DIFF_SHEETS.xlsx
(30.54 KiB) Downloaded 475 times
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to Use DBSW

Post by lotsaram »

CiskoWalt wrote: ...
All of these method produce the same result because the DBSW formula depends on the DBRW formula

Does anyone use DBSW?
Walt,
you_can't_have_a_DBSW_depend_on_a_DBRW (at least not on the same sheet). For that matter you can't have a DBRW depend on another DBRW either. This is pretty elementary TM1/Excel stuff and is to do with the way that the "W" formulas are calculated via batch, the client (Excel Perspectives) bundles all the DBRW/DBSW formulas into batches and requests the values from the server for the entire arrray of cell value queries in the batch. While the client is waiting for a response from the server all the values the waiting cells display an intermediate value of "RECALC". You can't send a value of "RECALC"to a numeric cell, nor is "RECALC" going to be a valid element ID if DBRWs are attempted to be used to supply address coordinates - hence your problem.

To make it work either the DBRWs need to be DBRs or the DBSWs need to be DBSs. (Since DBR and DBS are calculated one_by_one). However doing that will have some pretty dire performance implications in a large worksheet or workbook or any situation for use over a WAN.

It seems to me more a question of design. I see no reason WHY you would want to pull values from one cube with DBRW and send to another cube or section of the same cube with DBSW? This would be much more efficiently done with either TI (or rules if it is a permanent 1:1 relationship). Giving your users an action button with filter parameters of copy from / copy to would seem to be much more efficient if the data transfer is on as as needs basis.

Do people still use DBSW?
Sure.
Do DBSW formulas work and work efficiently?
Sure. (if they are set up correctly and used appropriately)

But I think DBSW is used to a much lesser degree than 10 years ago. For data input applications most folks have migrated to using TM1 web (or even TM1 Applications/Contributor) where there is no need for DBSW as values can be copy/pasted to a whole range of cells which was always the drawback in Excel. We also have TI now so any bulk movement or transformation of data is much better done using the right tool. For any new implementation in the last couple of years I would doubt DBSW has been used much if at all. Others may disagree but that has certainly been my experience.
Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: How to Use DBSW

Post by Christopher Kernahan »

Walt,

I've loaded a value using a DBSW that relies on a DBRW formula on a separate sheet. It requires that the sheets are not refreshed together.

- Shift + F9 the DBRW sheet, THEN,
- Shift + F9 the DBSW sheet.

So I'm curious as to why this doesn't work for you?
DBSW.png
DBSW.png (16.83 KiB) Viewed 19668 times
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

Re: How to Use DBSW

Post by CiskoWalt »

Thanks was on vacation for a few days.

I too am curious why this did not works for me.

In one worksheet, I pull data using DBRW
PULL_DBRW.png
PULL_DBRW.png (159.62 KiB) Viewed 19395 times

In the second worksheet, I use DBSW formula to send

Pressing SHIFT + F9 in the send Worksheet will only provide RECALC-###
SEND_DBSW.png
SEND_DBSW.png (150.16 KiB) Viewed 19395 times
Attachments
TEST_OF_DBSW_DIFF_SHEETS.xlsx
(25.17 KiB) Downloaded 379 times
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: How to Use DBSW

Post by declanr »

Pressing SHIFT + F9 in the send Worksheet will only provide RECALC-###

I recreated your cube as per shown in your attachment and it worked perfectly well when I pressed shift F9 on the Read tab and then moved to and pressed shift F9 on the send tab.
I receive the same errors you do if I just press F9 (without the shift i.e. a full recalc.)

A/ Are you positive that you pressed Shift + F9, not just F9?
B/ Is it possible that you have used hotkeys to manually assign Shift + F9 to something different so that the "tm1recalc1" doesn't initiate.


A possible way to test Shift + F9 operates correctly is by recording a macro then pressing it... from memory an F9 shows as "tm1recalc" and Shift + F9 shows as "tm1recalc1"... I tend to not do too much work in macros if it can be avoided though so my memory may be hazy if anyone wishes to correct me.
Declan Rodger
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

How to Use DBSW

Post by CiskoWalt »

Declan Rodger...THANK YOU !!!!!

The SNAG IT application mapped my SHIFT + F9 Keys to something else!!!

http://feedback.techsmith.com/techsmith ... t_f10_keys

Reason: Shift-F9 and Shift-F10 are hard-wired to video capture start & stop, which I never use. However, there are multiple applications in which I use Shift-F9 and Shift-F10 constantly. So I can't use those applications anymore when Snagit is working on my computer.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to Use DBSW

Post by lotsaram »

CiskoWalt wrote: The SNAG IT application mapped my SHIFT + F9 Keys to something else!!!

http://feedback.techsmith.com/techsmith ... t_f10_keys

Reason: Shift-F9 and Shift-F10 are hard-wired to video capture start & stop, which I never use. However, there are multiple applications in which I use Shift-F9 and Shift-F10 constantly. So I can't use those applications anymore when Snagit is working on my computer.
Well, no. I've had this annoyance with Snagit before but the hot keys are not "hard wired" they are configurable. You can go to the Snagit options and choose other accelerator hot keys. Or in the case of video capture just delete the entry as this is a feature probably only 0.01% of Snagit users ever use.
User avatar
jpm_de
Posts: 22
Joined: Thu Jun 10, 2010 5:19 pm
OLAP Product: TM1
Version: 10.2.2 FP3
Excel Version: 2010

Re: How to Use DBSW

Post by jpm_de »

True,
I mainly see DBS and DBSW in heritage solutions, or solutions built by or for pre-TI folks.

Using rules or TI is faster and more reliable, e.g. to do simulations or to limit typical Excel cell slips.
Nevertheless, in some cases, there is some complex Excel logic involved, which you might not want to rebuild using rules.

Anyway and as already mentioned, it is strongly recommend not to build formulas around a DBRW or DBSW in order to ensure correct bulk processing. Therefore, put the IF around the server. You can use two Excel cells to store the same cube reference, one for the DBRW and one for the DBSW area.
And make sure to familiarize yourself with the correct use of VIEW formulas as a Stargate to your data.
Why TM1? Because ...with great dimensionality there must also come -- great responsibility!
(http://www.quotecounterquote.com/2012/0 ... great.html)
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: How to Use DBSW

Post by mattgoff »

First, this thread is 3.5 years old, but OK.

Second, I wouldn't say it's accurate that all logic and data manipulation should be built using rules and TI. A non-exhaustive list:
  • Forces all logic changes to go through an administrator which breaks the user-centric, self service nature of TM1 and slows down iterations.
  • Requires that all model input data be stored or easily accessible to TM1.
  • Obfuscates logic to end users, particularly if they don't have access to the scripts and (more likely) don't know how to program or the nuances of TI scripting and rules.
There are many instances where TI and/or rules are the right solution, but I don't agree that DBSW should be relegated to the "legacy/avoid" bin.

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to Use DBSW

Post by tomok »

mattgoff wrote: There are many instances where TI and/or rules are the right solution, but I don't agree that DBSW should be relegated to the "legacy/avoid" bin.
Matt
Agree wholeheartedly! DBS can be an extremely useful tool in your arsenal when crafting input templates in TM1 and you want to conditionally send data from the template to TM1. Wrapping a DBS inside an IF statement lets you control what is sent to the cube which can be useful when you want certain input values to pass custom validation routines that are too complicated for simple Excel data validation.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to Use DBSW

Post by Mark RMBC »

Hi,

I just want to add my observations to this and to be honest looking for some reassurance! I would appreciate anyone who disagrees to please say so!

I am currently setting up some journal templates for the web. The contents of the template will end up in a holding cube and then be sent into the main cube via a TI process, so the users will click an Journal upload button on the web once the data has been sent to the holding cube.

I had the option to create an active form direct from the holding cube and allow the users to copy straight in, but I decided to go with creating an excel sheet with DBSW’s. The DBSW includes an if statement where the data will only be sent to the holding cube if:

• Each rows data is valid (based on lots of if and or logic etc in the excel sheet)
• Every row is valid (so no single row will be sent unless every row passes validation)
• Where the users has input Yes into a send data column.

So the formula on each row is:

=IF(AND($J9="Yes",$R9=1,$T$4="Valid"),DBSW(C9,$C$4,$C$5,$B9,C$8),"")

This formula extends across to a number of columns, so each column of data is captured and sent to the holding cube.

I believe in this case this approach is better because if I allowed users to input direct into the holding cube then I would have to add lots of process quit logic to the actual TI process which loads the data into the main cube and then I would have to return an error message on the web informing the users that the process failed for reason x, y or z. So the user would have to look at the data in the cube, figure out exactly what was wrong and retry to run the journal upload. Whereas using the DBSW allows the user to ensure all the data is correct before it ends up in TM1 and I think this is simpler for the end user, and means I don’t have to work out all the process quit logic in the TI and then deal with the consequence of the process quit. I think the DBSW will reduce data quality issues, whereas the alternatives will increase the likelihood of incorrect data passing to the main cube, and even if I added loads of logic to prevent data quality issues I think I would have to inconvenience the users to do this.

So I would say DBSW definitely still has its place in model design unless I have missed something?

cheers, Mark
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: How to Use DBSW

Post by David Usherwood »

I agree that DBSW still has a part to play - but with your testing logic I don't believe you will get the Stargate VIEW() performance win.
Given that you don't want any data to go unless your complicated tests are passed, I think you would be better off changing your cubename cell ($C$4) to be a formula which returns the cubename if all your tests are successful, and "" otherwise. The main DBSW would then be unconditional, but if the cubename is "" will do nothing. This will still deliver what you want.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to Use DBSW

Post by Mark RMBC »

Hi David,

big thanks for that.

I have updated my sheet to include the if statement in cell C4 and remove the if from the DBSW's as per your suggestion.

When doing that it suddenly occurred to me that I didn't need an if(and statement, just an if to say if $T$4="Valid", because cell $T$4 will only be valid if those other conditions are met! Too many if and or logics sending me crazy!

The only issue is that each DBSW cell now shows Key_Error, which is not a big issue as I was hiding those cells anyway.

cheers, Mark
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: How to Use DBSW

Post by gtonkin »

I normally use something like:

Code: Select all

=IF($C$4,DBSW(...),"No Send")
C4 would have an expression to return True or False or just have a data validation. When TRUE, the DBSW is executed otherwise cells show "No Send". Works for me...and seems like it works for others too after reading right from the top.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: How to Use DBSW

Post by David Usherwood »

I'm prepared to be corrected, but my belief is that if you wrap DBRWs or DBSWs in other functions, the performance benefit from using Stargate Views(via VIEW()) does not apply.
Post Reply