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!
Excel Recalculation Speed Issue
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Excel Recalculation Speed Issue
No you aren't. The thread that this post has been split from was about write-back issues, not calculation times.
The usual suspects: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.
(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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 9
- Joined: Thu Jun 28, 2018 10:56 pm
- OLAP Product: Planning Analytics
- Version: 10.2
- Excel Version: Excel 2010
Re: Excel Recalculation Speed Issue
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
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
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Excel Recalculation Speed Issue
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.(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?
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.