How to Use DBSW
How to Use DBSW
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?
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 544 times
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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).
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
www.infocat.co.uk
-
- 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
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?
- 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?
Re: How to Use DBSW
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
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
-
- MVP
- Posts: 3654
- 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
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.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
e.g. In Cell A1 =IF(bDoSend,"server:cube","")
In the DBSW cells =DBSW(nValue, $A$1, ...)
Re: How to Use DBSW
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
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 494 times
-
- MVP
- Posts: 3654
- 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
Walt,CiskoWalt wrote: ...
All of these method produce the same result because the DBSW formula depends on the DBRW formula
Does anyone use DBSW?
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.
-
- 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
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?
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?
Re: How to Use DBSW
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
In the second worksheet, I use DBSW formula to send
Pressing SHIFT + F9 in the send Worksheet will only provide RECALC-###
I too am curious why this did not works for me.
In one worksheet, I pull data using DBRW
In the second worksheet, I use DBSW formula to send
Pressing SHIFT + F9 in the send Worksheet will only provide RECALC-###
- Attachments
-
- TEST_OF_DBSW_DIFF_SHEETS.xlsx
- (25.17 KiB) Downloaded 405 times
-
- 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
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
How to Use DBSW
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.
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.
-
- MVP
- Posts: 3654
- 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
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.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.
- 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
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.
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)
(http://www.quotecounterquote.com/2012/0 ... great.html)
- 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
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:
Matt
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.
Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
- MVP
- Posts: 2832
- 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
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.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
-
- 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
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
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
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: How to Use DBSW
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.
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.
-
- 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
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
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
- gtonkin
- MVP
- Posts: 1202
- 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
I normally use something like:
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.
Code: Select all
=IF($C$4,DBSW(...),"No Send")
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: How to Use DBSW
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.