TM1 Forum

Hi,

I have never been busy a lot with the 'excel side' of TM1 but recently I was contacted by a client who built a lot of macro's on TM1 and who was testing an upgrade from 9.0 to 9.5

To make a long story short comparatively their macro's were performing 200-400% slower on 9.5.

So we did a number of tests together and I think the cause is the TM1p.xla and mostly the TM1recalc function.

Testing the same macro's on exactly the same client setup, a muuuch better server for the 9.5 version and similar network latency to the 9.0 server and the 9.5 server was giving results in the range above.

Furthermore disabling screenupdate doesn't seem to work anymore with the 9.5 add-in. (Found a post about that here already)

Trying to find exactly what piece of code was so much slower I think it is the recalc function. We settled on a test whereby we were just looping over a dimension in excel, getting the level of the element, recalc then next element.

Results were about this
----------------------- 9.0 9.5.2
screenupdate on 25s 95s
screenupdate false 17s 95s (just doesn't work anymore)
excel minimized 25s 45s

Now we are logging this with IBM to try to get some idea of what the hell they did but in the meantime I am wondering what kind of code we could put in the macro's to get some better performance out of 9.5.2. when using macro's.

Thanks for any input,

Jeroen

Hi Jereon,
I'm not on 9.5 so can't help much but did you try substituting the TM1Calc and TM1Calc1 for worksheet.calculate?

Cheers,

9.5 turns screen updating on, as such disabling screen updating in your macro has no effect. Use the following in your macro with screen updating off:

Application.Run ("TWEVRECALC1") instead of TM1RECALC1
Application.Run ("TWEVRECALC") instead of TM1RECALC

Several Excel functions under 9.5 are much slower than under 9.0, 9.5.2 FP2 is much better than 9.5 but still not as good as 9.0 was.
Did you try to turn off the ribbon bar by renaming "tm1pRibbonX.xlam" in bin folder on client machine. This gives back the 9.0 look and feel and avoids a lot of trouble, also from performance point of view.