Excel Settings overriden by tm1p.xla

[geneticjim]

Hi everyone,

I did a search and wasn't successful on finding an answer so thought I would submit a question.

I have several spreadsheets that use a lot of DBRW formula's and as a result, the calc speed has been horrible. Thus, I went into Excel Options --> Advanced --> and scrolled down to "Enable multi-threaded calculation" and checked it. When I have this checked, it seems to be helping my calc speeds (I have spreadsheets that will spin calculating for minutes). However, what I have noticed is every time I open Perspectives, my setting is unchecked. I disabled my TM1 add-in and reopened Excel and it was not unchecked.

Excel Settings overriden by tm1p.xla.jpg (63.95 KiB) Viewed 9309 times

So, it appears Tm1p.xla is overriding settings?

I researched online and found the recommendation to create a 'Personal.xlsb' and use the code in the attachment in order to always have this checked. However, again, Perspectives is trumping this and turning it off - thus, this doesn't work either.

I am on Office 2010 with Windows 7 O/S. Our Tm1 version is 10.1.1 FP1.

Has anyone else experienced this and know a work-around or a fix? Thanks so much!

[lotsaram]

Are you using automatic or manual calculation mode in Excel?

[geneticjim]

Hi - I've tried it on both. Typically, when I reopen Excel it will change to Automatic even though it was on Manual prior to saving and closing. Really, it doesn't matter what I change an Excel setting to because the tm1p.xla seems to be changing or overriding it

[macsir]

Have you tried to run the same file on the server? Or use more powerful PC to open it? Or even redesign your DBRW sheets in a better way? The performance issue you have might not be due to Excel configuration. Usually I don't use that option.

[geneticjim]

I don't have access to the server, so I can't try that option

In regards to my PC, I have an Intel Core i5-4300U CPU @ 1.90GHz 2.50 GHz with 8GB or RAM (7.90 GB usable) and a 64-bit OS. Is that underpowered? (this is getting off topic of the original post).

When you say "redesign your DBRW sheets in a better way" - what do you have in mind? Would you mind providing an example or explanation of what you mean?

But, to circle this back to the original post - I can change my settings in Excel to whatever, but the moment the tm1p.xla is executed, then my settings get changed. Again, the tm1p.xla is trumping and changing my settings.

Thanks

[macsir]

I reckon your PC is still OK. Possibly divide one large DBRW sheet into several smaller ones?

[tomok]

Assuming network bandwidth is not an issue (meaning your PC is on the same network as the TM1 server) then the most common cause of poorly performing worksheets is missing or improperly constructed VIEW statements. Did you build these sheets from scratch or did you start with a slice? Do all the DBRW formulas point to a VIEW statement and not a hard-coded cube name?

[geneticjim]

I can't really divide my spreadsheet and need to have them together. My pc and server are both on the same network so I am thinking my view must be improperly constructed? I did just do a slice originally and then simply added a control tab to my spreadsheet and then tweaked the formula to read to the control tab. We had a consultant once that recommended doing that type of thing. I am all open to making improvements on my slice. Many of us here have the pain point of slowness when working with DBRW's so if I can help them, too, that would be great. Again, this is all off-topic of the original post but it's related and maybe I won't need to worry about multi-thread calc in the end. Thanks for your input. Below is a screenshot of a part of the spreadsheet and then the control tab that it's pointing to. To the right of the parts highlighted in yellow are the formula's.

Tomok - I wasn't sure what you meant by "do all the DBRW formulas point to a VIEW statement and not a hard-coded cube name" - is mine hard-coded because I have the name in the view formula?

Thanks!

View and formula example for Tm1Forum.jpg (76.95 KiB) Viewed 9208 times

[lotsaram]

Tomok - I wasn't sure what you meant by "do all the DBRW formulas point to a VIEW statement and not a hard-coded cube name" - is mine hard-coded because I have the name in the view formula?

I have troubleshooted reported poor performance in user generated worksheets many times and mostly the reason is either no view formula (usually people copy/paste values with server:cubename over the view formula because someone once told then "you don't need that") or an incorrect view formula. Hence the reason for Tom's question. So my elaboration would be: OK so you have a view formula but is it correct? As in is a view cache being created that is too big (too many "!" arguments vs. what is really needed by the sheet) or too small or incorrect?

Sometimes rather than having lots of "!" arguments (=calculate ALL POSSIBLE COMBINATIONS for this dimension) you might be better off having multiple view formulas, or even none. But this type of performance tuning is tricky and a little trial and error and needs experience.

Also I see in your sheet you are nesting formulas with the DBRWs. Best not to do this and have "clean" DBRW formulas as this performs better. Even if it means more formulas in more cells. e.g. for the cell in your screenshot IF(SKU="","","cubename") in one cell and the DBRW picks up the cell that either has a blank string or the cube name. (also if this is a reference for other cells then this should be a DBR not a DBRW).

Also give that you are on a LAN and your cubes don't have large dimensionality (5 is pretty minimal!) then there's also a fairly high chance that your performance issues in Excel are due to poor cube calculations and design - probably poorly performing rules. The way to check this is to compare the recalculation performance of an equivalent view in server explorer versus the excel sheet. If the pure view is also slow then the issue is in the model itself. If the view is fast and Excel is slow then the issue is in the design of the spreadsheet.

[geneticjim]

Thanks a lot lotsaram! I have gotten tied up with some other things but will most definitely circle around back to this and try your recommendations. I just wanted to say thanks and will post more later.

[bkkbasher]

Hi geneticjim,

Did you ever get this resolved? We have the same situation. Can't get that "enable multi-threaded calculation" to stick.

The book.xlt keeps some of the settings, but not this one. Whenever a workbook, new or existing, is opened it gets turned off. Even if i add Application.MultiThreadedCalculation.Enabled = True to Workbook_Open it still gets turned off. Only solution is to manually set it or trigger the VBA every time after a workbook has been opened, which is a bit of a nightmare.

Thanks

Running Cognos Express 10.2.2 through Excel 2013.

[lotsaram]

Hi geneticjim,

Did you ever get this resolved? We have the same situation. Can't get that "enable multi-threaded calculation" to stick.

The book.xlt keeps some of the settings, but not this one. Whenever a workbook, new or existing, is opened it gets turned off. Even if i add Application.MultiThreadedCalculation.Enabled = True to Workbook_Open it still gets turned off. Only solution is to manually set it or trigger the VBA every time after a workbook has been opened, which is a bit of a nightmare.

Thanks

Running Cognos Express 10.2.2 through Excel 2013.

I think tm1p disables this for good reason. All TM1 formulas are volatile which means on any recalculation all volatile cells are recalculated as Excel has no way to know if the value has changed or not. This is why it is much more efficient to use manual recalculation mode and have tm1p handle the recalculation events as automatic recalculation will be much slower with a heavy TM1 worksheet. All communication to the TM1 server to query the values of the TM1 cells needs to be single threaded to ensure data integrity so this is likely why multi-threaded calculation is not allowed.

Why do you think you need it?

[bkkbasher]

Thanks, lotsaram.

The manual calculation i understand and we have that set to manual as standard. The multi-threaded calculation i was unsure about and i realised there might be some issues with the flow of data.

We have just transitioned from CX 10.2.1 on Excel 32 bit 2010 to CX 10.2.2 on Excel 64 bit 2013 and found Excel to be running much slower than before. Also of note, we access Excel through Citrix. Rummaging through the internet and Excel options we have now disabled Excel animations and hardware graphics acceleration, which has helped. Both of these i have learned do not play nice with Citrix. While in the Excel options i noticed the multi-thread calculations and turned it on. With this enabled there was a big jump in the performance of Excel. However, TM1 is pretty determined when it comes to turning it off again.

Following this post earlier today, i submitted a PMR with IBM and they have said that multi-threaded calculations are not supported and so are disabled by the addin. They suggested i submit this as a requested enhancement, which i have done. I've asked whether i am running any risks by enabling it, because of the performance improvement, but they have not come back with anything yet.

I have now found a way to get it on automatically by adding the Application.MultiThreadedCalculation.Enabled = True VBA to the Workbook_Activate event (it won't work if in Workbook_Open). However, now i fear this might be risky, although i have not seen any problems with it yet.