PAx - DBRW/DBRS only when requested

Post Reply
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

PAx - DBRW/DBRS only when requested

Post by PTSD »

Hi,

I have an excel file with a lot of DBRW/DBRS formulas (roughly 1000 on each tab). Most of these are also nested in other standard excel formulas. The file works great, except can be very slow at times.

Is there a way to make BBRW/DBRS formulas work only when you want them to, and not contentiously?

Switching to "Manual Calculations" in excel works, but not ideal, since it disables all calculations. I want standard excel formulas to still work, while DBRW/DBRS disabled.

Disconnecting from TM1 server doesn't work because I get #N/A instead of static numbers.

Any ideas on a workaround?

Thanks!
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: PAx - DBRW/DBRS only when requested

Post by tomok »

PTSD wrote: Mon Jul 16, 2018 4:23 pm Most of these are also nested in other standard excel formulas. The file works great, except can be very slow at times.
Nesting DBRW/DBR stops Stargate views from working. That's why it is slow. Remove all the nesting and try again.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis »

Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

Thanks!
It is a bummer though, cause nesting gets me the efficiency. I'll try to reorganize everything and see if I can build it in a similar way with no nesting.
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: PAx - DBRW/DBRS only when requested

Post by tomok »

PTSD wrote: Mon Jul 16, 2018 5:50 pm Thanks!
It is a bummer though, cause nesting gets me the efficiency. I'll try to reorganize everything and see if I can build it in a similar way with no nesting.
Agreed. There are times when the nesting can be extremely helpful, especially when you are creating input forms. For data retrieval formulas you really should use it sparingly.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

Thank you.

It looks like I can easily unnest DBRWs.

Question on DBRSs - obviously hard-coding values is not efficient. If my DBRS formulas are referencing another cell for "send" value - is this considered nesting as well?

Thanks.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis »

PTSD wrote: Mon Jul 16, 2018 7:06 pm Thank you.

Question on DBRSs - obviously hard-coding values is not efficient. If my DBRS formulas are referencing another cell for "send" value - is this considered nesting as well?
What is the DBRS function in TM1 ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

Sorry, I meant DBSW. My bad.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis »

In that case, you will link to the cell that is to be sent to TM1. That's not considered nesting.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

Weirdly enough, I unnested 70% of the formulas and see no difference in performance. I think it is just the sheer number of formulas on a sheet that effects the performance, not nesting.
Edward Stuart
Community Contributor
Posts: 247
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Edward Stuart »

When using TM1Top/ Operations Console can you see how much activity is taking place on TM1 when refreshing these sheets? What kind of performance are you experiencing? (5 seconds, 60 seconds?)

Frequently I've encountered scenarios where the excel calculations/ network speeds have been the bottleneck as opposed to the DBS/R(W) performed by TM1
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

It is about 1 second delay every time I key in a number and hit enter. Doesn't seem like much, but it is. There are a few thousand formulas on the sheet, half of those are TM1.

Disconnecting from PAx doesn't change anything, which makes me thing it is excel. Never had this problem with SAP excel add-in, similar worksheet.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis »

Do you have calculations on Manual or Automatic ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
PTSD
Posts: 13
Joined: Sun Jun 24, 2018 12:58 am
OLAP Product: ABM Analytics
Version: latest
Excel Version: O365 V1805

Re: PAx - DBRW/DBRS only when requested

Post by PTSD »

Automatic. I do still want standard excel formulas to work.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis »

I see what you mean, and while I don't like it either, suggested mode is manual calcs.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply