TM1 Forum

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!

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.

The same message put differently:
http://www.tm1forum.com/viewtopic.php?f ... 136#p69780

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.

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.

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.

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 ?

Sorry, I meant DBSW. My bad.

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

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.

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

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.

Do you have calculations on Manual or Automatic ?

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

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