TM1 Forum

I'm seeing some strange behaviour when calculating TM1 linked spreadsheets. TM1 version is 9.5.2 FP1 64bit server with same version client over both Excel 2010 and 2003. The spreadsheets are pulling values from a large cube, with daily time DIM, so are broken down into multiple feeder sheets each with a tight 'view' formula to improve performance.

The issue is that when run directly on the server itself and another PC, TM1 behaves as expected. In TM1Top, you can see the views being built and then values calculated and the spreadsheet calcs in around 2 mins. However, on other PCs this isn't the case, and TM1 behaves as though there is no 'view' formula. TM1Top shows the values being calculated as cube values, not view values, and the spreadsheet takes 30 mins+ to calc. This happens on both Excel 2003 and 2010.

I've tried uninstalling the client and reinstalling from scratch, trying the spreadsheets on different drives etc but still happening. Has anyone seen anything similar or have any suggestions for TM1/client/Excel etc settings I should check?

Thanks.

Sounds like your problem is actually excel in automatic calculation mode.

This is with manual recalc. and tm1rebuild set to 0 for these workbooks.

I think I have identified the issue.

Some of the users were recalcing the spreadsheets via the 'Calculate Now' option on the Excel menus, rather than by pressing F9. This makes it act as though the DBRW formula are not linked via the TM1 'View' formula, meaning it retrieves the values from the cube as a whole, hence the massive increase in recalc times.

All spreadsheets now have a recalc action button on the control tabs.