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 » Mon Jul 16, 2018 4:23 pm

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!

User avatar
tomok
MVP
Posts: 2483
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 » Mon Jul 16, 2018 4:49 pm

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: 1809
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis » Mon Jul 16, 2018 5:18 pm

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

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

User avatar
tomok
MVP
Posts: 2483
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 » Mon Jul 16, 2018 6:33 pm

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 » Mon Jul 16, 2018 7:06 pm

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: 1809
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis » Mon Jul 16, 2018 9:00 pm

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

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

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 » Mon Jul 16, 2018 11:03 pm

Sorry, I meant DBSW. My bad.

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

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis » Tue Jul 17, 2018 7:11 am

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

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

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 » Tue Jul 17, 2018 12:39 pm

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: 220
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 » Tue Jul 17, 2018 1:24 pm

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 » Tue Jul 17, 2018 6:27 pm

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: 1809
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis » Tue Jul 17, 2018 8:43 pm

Do you have calculations on Manual or Automatic ?
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

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 » Wed Jul 18, 2018 12:49 pm

Automatic. I do still want standard excel formulas to work.

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

Re: PAx - DBRW/DBRS only when requested

Post by Wim Gielis » Wed Jul 18, 2018 2:19 pm

I see what you mean, and while I don't like it either, suggested mode is manual calcs.
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

Post Reply