TM1 Forum

Hi-

I am having a similar recalculation issue. When I open a DBRW linked file, sometimes the formulas will calculate instantaneously and pull from the cube, but often times, Excel just hangs. I am not pulling a lot of data - just a few columns worth. I end up having to close out Excel, re-log into the cube, and cross my fingers that the calculation will work.

Here is what I'm running:

Windows:
Windows 7 Enterprise
Service Pack 1
64-bit OS

Excel:
MS Office Professional Plus 2016
64-bit

Version:
PA 2.0.3

Any thoughts would be greatly appreciated.
Thanks!

jrock wrote: ↑Mon Sep 24, 2018 11:54 pm I am having a similar recalculation issue.

No you aren't. The thread that this post has been split from was about write-back issues, not calculation times.

jrock wrote: ↑Mon Sep 24, 2018 11:54 pm When I open a DBRW linked file, sometimes the formulas will calculate instantaneously and pull from the cube, but often times, Excel just hangs. I am not pulling a lot of data - just a few columns worth. I end up having to close out Excel, re-log into the cube, and cross my fingers that the calculation will work.

The usual suspects:
(a) Do you have a lot of calculated (especially rules-calculated) values? If so, the first time they are calculated they will take a long time, the next time they're calculated (assuming the underlying values don't change) it will be fast because the calculations are cached.
If so, look to your rules and feeders and see whether they can be better written.

(b) Do you have a lot of other workbooks open in the background? Excel does not always calculate efficiently or know where links may exist between workbooks, so at times if you have a lot of active workbooks it's a case of Excel saying to you "Go grab a coffee, I'll be back in half an hour".
If the answer is "yes", close them.

(c) Do you have a properly constructed View() function to specify the cube name so that TM1 minimises the amount of calculation that it does?
If the answer is no, create one.

(d) Do you have a lot of DBRA formulas? Get beyond a handful of those and they become murderous on performance, in my experience.
If the answer is Yes, look at using DBRWs to read from the element attribute cubes instead. I generally find that they are about twice as fast as DBRAs, not that I've done an in-depth study on it but every time I've had a sheet of DBRAs and attribute DBRWs and checked the releative speeds, I've found that to be more or less the case.

(e) Are you in fact using DBRWs instead of DBRs?
If the answer is no, change the formulas.

Hi Alan-

Thanks for the response. Here are my responses:
(a) - There are several rules-calculated values.
(b) - No, I close all other workbooks and just open the one I want calculated.
(c) - Not sure what this refers to...can you point me in the right direction?
(d) - No DBRA formulas, only sliced DBRWs.
(e) - Yes, only sliced DBRWs are used.

Thanks for your help!
J

(c) Do you have a properly constructed View() function to specify the cube name so that TM1 minimises the amount of calculation that it does?
If the answer is no, create one.
(c) - Not sure what this refers to...can you point me in the right direction?

Looks like that's your problem then. Create a slice matching your layout and have a look at the formula which delivers the cubename - that's what you need to have in your sheet. Without that, the sheet will calculate a cell at a time and will be much slower. This is called a Stargate view, a name given (allegedly) by an Applix marketing person when they first saw the performance boost.
Small caveat - I have seen statements by IBM that this is no longer relevant with the new frontends, specifically PAX. I have not tested this myself nor have I seen comprehensive test results by others. Colleagues and others in the field have other concerns about PAX, including (but not limited to) performance.