How to avoid that Excel turns into a bottleneck in TM1?

[craig_karr]

As we all know, TM1 has amazing performance thanks to the in memory technique. However in out first TM1 implementation we have used Excel and more specific active forms in Excel as the primary end user interface. We have significant performance issues and it seems like the bottleneck is not the server.

CPU usage and RAM usage on the server is very low compared to how much resources we have, but as soon as some enters data in the active forms in Excel and presses enter it takes forever before everything has been calculated and it seems like the client computer's cpu usage goes up to 100 percent and almost freezes.

What is the workaround for this? As I have understood it Excel is often used as end user interface for TM1 so it seems like we are doing something wrong. Could it be the design of the active forms?

[wissew]

This my be a silly question but is Automatic calculation disabled on the user side? If they have autocalc enabled prior to opening the form it will stay.

[craig_karr]

Hi, thanks for your answer and indeed the auto calc is enabled in Excel because that is how the users prefer to have it and since I'm new to TM1 I was not sure if they were supposed to be able to have autocalc on or if it is assumed that you should always use manual calc. Reading you answer I understand that manual calc is the way to go with TM1

[Wim Gielis]

Not really.

Autocalc off always gives the possibility of looking at wrong (i.e. not updated) data. Hence, wrong decisions or analyses.

It implies the TM1 developer / Admin person should strive to create easy, flexible (SUBNM's, cell references, ...), fast Excel sheets and reports. Not the sheets with about 30.000 DBRW formulas as we often encounter (unfortunately).

This being said, active forms might sometimes behave strangely with autocalc on, and lead to Excel crashes. Setting it to off might be better in some cases.

Wim

[jorelb]

Based on my experience, Excel performance on client machines have always been an issue specially with huge spreadsheets and not even active forms. As a fix, we let our users RDC to the TM1 box and leverage the server’s large memory and computing power. What would normally take minutes to load on the client side (especially if they connect wireless) will take seconds in the TM1 box.

[Martin Ryan]

I would disagree with you Wim. Even with smaller views, which are admittedly the exception rather than the rule, I think auto calc should always be turned off and users should simply get in the habit of pressing shift+f9 to recalc when they want to get the latest data. Constant recalculation puts an unnecessary burden on the TM1 server and the network, which can negatively impact performance for other users, especially if the model is being constantly updated with new numbers, so the pre calc'd data is constantly being thrown out.

On a very small model with a very small number of users perhaps it would not be a big deal, but I think the general rule of thumb is that auto calc should be off.

Martin

[Wim Gielis]

Okay Martin, I see your point. Let's hope that the habit of pressing Shift-F9 works (or using an Action Button to recalc).

Wim

[Steve Rowe]

Just to look at this issue from a bit of a different direction.

Craig is talking about a user entering data and then TM1 taking a long time (can you define a long time?) to update a report.

How many cells is the excel report? Is it DBR or DBRWs?

This could be expected behaviour, irrespective of the performance of the client box, it could still be the server side performance that is causing the slow reponse. Do you get the same behaviour if a user does not change data?

As a side point the auto-calculation on/off question is significant issue for TM1. In an environment where users only use Tm1 it's not too bad and they soon get into the habit of shift-f9. In the real world though many users work in a mixed environment of TM1 / plain XL / reports linked to their ledger. They expect Tm1 and their PCs to be able to cope with over a dozen worksheets open and calculation on automatic and get Excel crashes/non-responding. With my TM1 admin hat on my response is that they are using TM1 wrong or the client box is under resourced but I can see their point of view that it at least makes TM1 look "broken".

Cheers

[LoadzaGrunt]

Personally I am a 'turn off auto-recalculation' type of grunt.

However, you can subdivide all users into two camps - those who like TM1 enough to put up with the auto-recalc issue (and other things like Ctrl+C misbehaving) and those who don't really like TM1 enough to put up with the small constraints it makes.

For the first group, they have the add-in permanenly added in and learn to Shift+F9 really quickly. For the other group, you should educate them to use TM1 by using the Start->TM1->Perspectives route or ideally put a short cut on their desktop. Then they make the deliberate choice to enter the TM1 Perspectives environment and are likely to recall that the behaviour of Excel will be slightly different.

All that being said, I agree with Steve's point about checking out the impact of the writeback to see if it really is an Excel calc'ing issue. Also, check to see if it is a active form specific issue by rebuilding the template with a static/ classic slice and attempt to replicate the issue.

[Alan Kirk]

I'm manual recalc all the way... but with the proviso that I like to build =Now() functions into reports to show when they were last updated.

Clark or Caroline Clueless (as the case may be): "But these numbers are wrong?"
Me: "Did you recalculate the report?"
CC: "Yes, of course I did!"
Me: "So why does this 'As At' date, right up here at the top of the report in the bold lettering, show last Tuesday morning's time and date?"
CC: "Ummmmm...."

[belair22]

Seem to be missing talk of TM1 latency . Always best to benchmark performance on the server as a guide as to whether the issue is Rule Based, Excel Based, or Network based. Doesn't help that alternate versions of TM1 (even minor versions) can and do differ in performance.