All,
When I try to open a multi-tab Excel report (created using a slice not an Active report) it takes forever (more than 5 minutes) to open the report. I notice that in the bottom of the Excel page it says "Calculating..." with a percentage beside it. This percentage moves very slow. We are on Excel 2003 but have Excel 2007 as well.
Any thoughts?
Thanks in advance.
-Dan
Performance Issue in Excel
-
CognosDeveloper
- Posts: 2
- Joined: Mon Jan 10, 2011 3:42 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: Excel 2003
-
lotsaram
- MVP
- Posts: 3706
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Performance Issue in Excel
Make sure Excel is in MANUAL calculation mode.
-
Martin Ryan
- Site Admin
- Posts: 2003
- Joined: Sat May 10, 2008 9:08 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Wellington, New Zealand
- Contact:
Re: Performance Issue in Excel
You probably have auto-calc turned on. Turn it off.
How many tabs do you have? As a rough rule of thumb I try to stick to a maximum of five. And you should never have multiple reports that have the same structure but are just showing different cost centres/departements/regions/whatever, that's defeating the purpose of having TM1.
Martin
How many tabs do you have? As a rough rule of thumb I try to stick to a maximum of five. And you should never have multiple reports that have the same structure but are just showing different cost centres/departements/regions/whatever, that's defeating the purpose of having TM1.
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Jodi Ryan Family Lawyer
-
Olivier
- Community Contributor
- Posts: 159
- Joined: Thu Jun 26, 2008 5:46 am
- OLAP Product: TM1
- Version: Tm1 10.2.2fp4 -> 2.09
- Excel Version: Excel 2013 - 2019
- Location: Sydney
Re: Performance Issue in Excel
Just a quick one to thumb up on Martin comments...
These little things thats seems obvious but you have to fight for in some places...
These little things thats seems obvious but you have to fight for in some places...
HTH
Olivier
Olivier
-
aklante
- Posts: 2
- Joined: Wed Jan 12, 2011 1:39 am
- OLAP Product: TM1
- Version: 9.4.1
- Excel Version: 2007
Re: Performance Issue in Excel
The following applies if there has been an active form in the spreadsheet previously, or a predecessor of the spreadsheet. When an active form is created, by default there is a parameter that calculates the entire workbook when you open it (even if calculation is set to manual). The parameter is stored in excel as a name called "TM1REBUILDOPTION" and by default it equals 1. If you change it to zero then it will no longer recalculate the entire workbook when it is opened. The parameter stays in the spreadsheet even if the active form is deleted, so the only way to tell if it there or not is to go to the name manager.
The spreadsheet will still take 5 minutes to calculate when you press F9 but at least you will have a chance to set the correct parameters for the report first, or you can press shift and F9 to calculate one sheet at a time.
There are a few things that could impact the performance of the report. For example, you could check the "VIEW" formula in each of your sheets because if it has not been set to the correct parameters then the DBRW formulae will retrieve values from the server one cell at a time instead of in batches.
The spreadsheet will still take 5 minutes to calculate when you press F9 but at least you will have a chance to set the correct parameters for the report first, or you can press shift and F9 to calculate one sheet at a time.
There are a few things that could impact the performance of the report. For example, you could check the "VIEW" formula in each of your sheets because if it has not been set to the correct parameters then the DBRW formulae will retrieve values from the server one cell at a time instead of in batches.