PAx - DBRW/DBRS only when requested
-
- 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
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!
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!
-
- 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: PAx - DBRW/DBRS only when requested
Nesting DBRW/DBR stops Stargate views from working. That's why it is slow. Remove all the nesting and try again.
-
- MVP
- Posts: 3120
- 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
The same message put differently:
http://www.tm1forum.com/viewtopic.php?f ... 136#p69780
http://www.tm1forum.com/viewtopic.php?f ... 136#p69780
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
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
-
- 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
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.
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.
-
- 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: PAx - DBRW/DBRS only when requested
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.
-
- 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
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.
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.
-
- MVP
- Posts: 3120
- 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
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
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
-
- 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
Sorry, I meant DBSW. My bad.
-
- MVP
- Posts: 3120
- 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
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
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
-
- 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
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.
-
- Community Contributor
- Posts: 248
- 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
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
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
-
- 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
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.
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.
-
- MVP
- Posts: 3120
- 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
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
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
-
- 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
Automatic. I do still want standard excel formulas to work.
-
- MVP
- Posts: 3120
- 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
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
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