TM1 Forum

Hi all,

Can anyone advise please some areas to investigate that affect DBRW performance when refreshing (F9 Recalc) in Excel?

We have recently gone live with TM1 9.5.2 on Excel 2007, and one of the main reports is a single page in Excel with circa 1700 DBRWs all looking at one cube. An F9 refresh when we first started using the system, took around 10 seconds. Now we are averaging 75 seconds.

What i've tried so far:

1) Looking at TM1Top there are no processes running and no users performing any other calculations so happy it's not about locking.
2) The Ribbon is already disabled due to the 2007 Excel bug.
3) When I perform the same calculations in the cube viewer the result is almost instantaneuous, so this points to an Excel/DBRW problem rather than a more general cube issue.
4) The Slice I am using has a VIEW statement and there are no #REFs or bad links in this formula, as I know this can sometimes be a problem.
5) I have tested it on two other users PC's with different specs and still get a similar performance, so I don't think it's a local issue but also tried shutting down all other applications on my machine and running it on it's own, with no effect.

My next step was going to be rebuilding the Slice from scratch just in case there is anything sluggish in the Excel cache.

Does anyone else have any thoughts on what else to go after?

Thanks

conflax wrote: 4) The Slice I am using has a VIEW statement and there are no #REFs or bad links in this formula, as I know this can sometimes be a problem.

it's not enough to just have a VIEW statement, it has to be a properly constructed VIEW statement where the title elements point to cells with the specific dimensional value and there isn't an overuse of "!" (which means all elements in that dimension, which would be for the row and column dimensions). Try creating a slice with the same layout and then compare the VIEW statement created with the current one you have on the affected spreadsheet.

Thanks - hadn't considered that, will investigate.