How to Use DBSW

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

How to Use DBSW

Postby CiskoWalt » Thu Aug 15, 2013 8:10 pm

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 144 times

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

Re: How to Use DBSW

Postby Steve Rowe » Thu Aug 15, 2013 9:52 pm

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).

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

Postby Christopher Kernahan » Fri Aug 16, 2013 2:23 am

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: 21
Joined: Wed Jul 02, 2008 1:20 pm

Re: How to Use DBSW

Postby Ken Vuong » Tue Aug 20, 2013 10:21 am

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: 2916
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365
Location: Switzerland

Re: How to Use DBSW

Postby lotsaram » Tue Aug 20, 2013 11:41 am

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

Postby CiskoWalt » Tue Aug 20, 2013 8:17 pm

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 103 times

lotsaram
MVP
Posts: 2916
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365
Location: Switzerland

Re: How to Use DBSW

Postby lotsaram » Wed Aug 21, 2013 7:01 am

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

Postby Christopher Kernahan » Thu Aug 22, 2013 1:36 am

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 3362 times

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

Re: How to Use DBSW

Postby CiskoWalt » Mon Aug 26, 2013 6:30 pm

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 3089 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 3089 times
Attachments
TEST_OF_DBSW_DIFF_SHEETS.xlsx
(25.17 KiB) Downloaded 57 times

declanr
MVP
Posts: 1475
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010 and 2013
Location: Manchester, United Kingdom
Contact:

Re: How to Use DBSW

Postby declanr » Mon Aug 26, 2013 7:00 pm

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.

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

How to Use DBSW

Postby CiskoWalt » Mon Aug 26, 2013 8:01 pm

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: 2916
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365
Location: Switzerland

Re: How to Use DBSW

Postby lotsaram » Tue Aug 27, 2013 6:16 am

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

Postby jpm_de » Fri Feb 17, 2017 1:35 pm

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/07/with-great-power-comes-great.html)

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

Re: How to Use DBSW

Postby mattgoff » Fri Feb 17, 2017 3:24 pm

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.

User avatar
tomok
MVP
Posts: 2214
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

Postby tomok » Fri Feb 17, 2017 3:41 pm

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.


Return to “Cognos TM1”

Who is online

Users browsing this forum: Google [Bot], Yahoo [Bot] and 4 guests