We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:
Excel found unreadable Content. Do you wish to repair
When we click yes the following log is shown:
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
And all the pivot tables are converted to plain text.
I have read about this in KB 929766 but this do not apply since KPIs are not used.
The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.
Is there some way to “save” pivot tables from destruction? I can’t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?
i'm having the exact same problem.
KB943088 looks intresting but doesnt help solve the problem.
i've tried to uninstall office 2007 to get rid of SP1 and that didnt help anything.
i can reproduce this 100% of the times on my computer by simply creating a new empty excel book and add one pivot table from a SSAS 2005 cube.
when i save it localy on my computer and reopen it i get that error about the file being corrupted.
one would atleast think that excel could open its own files on the same computer with the same SP level.
problem is that because the file gets corrupted moss 2007 doesnt like it.
I have the same problem with one of my cubes. It only happens when i use a certain dimension, either in the filter or rows or columns.
To be correct, when I use it in the filter it lets me save the workbook, and then the problem happens. If I use it on rows or columns it crashes excel immediately. This dimension is a parent-child dimension. I have other similar in the same cube with more members and they don't crash excel neither maker the content unreadable.
I'm trying to get to a solution, but since you all just refer the use of the cube, not on any localized part of cube I thought I could help by referring my situation.
Hope this helps everyone.
i removed office 2007, installed it again and then ran windows update patches to get sp1 and now the problem seems to be gone.
I've finaly found the cause of this problem!
The problem has to do with translation of the olap cube, or more specificaly missing translations for some items in the cube and how this is handled by excel.
The reason it worked after i reinstalled it was because the cube i have created is fully translated in english (the default language if no other translation is avaialbe) so after installing english i had all attributes and measures fully translated and no empty captions.
What happens is that the caption atribute of the cacheHierarchy tag in the xml files in /xl/pivotCache/ is an empty string and this excel does not like.
What i did was:
i had 2 files, one working saved with english excel and one broken saved with swedish excel.
i used OpenXmlDiff from the open xml format sdk v2 and started looking around at what the differences was.
i noticed that one attribute of a measure had an caption="" tag, so i unziped the broken xlsx file, added some text to the caption and zipped the file again and opened it with excel, and it worked!
(i also experimeted with removing swedish characters åäö from names, but i belive that was not the cause)
- Proposed as answer by TobbeJ2 Monday, March 02, 2009 9:34 PM
Thanks TobbeJ2, your post put me on the right track and solved our problem!
We are a MS partner delivering BI solutions and the problem that customers could not save their OLAP pivottable in excel 2007 without losing pivot funcationality was really troubling us. As you mentioned I have found out that the cubes which are causing problems all contain missing translations (in Dutch in our case). When I connect to the cube with the default language (English, the translations are not adressed then) the problem was gone! We will add a translation for each field, for each language and then the problem should be solved.
I have spend most of to day on this issue because adding captions for each translation wasn't enough to solve the problem. In the pivotCacheDefinition1.xml file (rename .xlsx to .zip and unzip to locate this file) there was indeed an empty caption (caption="") for one of the dimensions. In this dimension the translations were completely filled however. Changing this value manually did not work. It seems excel keeps changing the xml back on opening of the file.
The solution was to remove the translations completely from the faulty dimension. The problem is now gone!
For several other dimensions translations work fine though! This dimensoin is the only dimension sharing the same dim table and key with another dimension the cube (it is an alternative hierarchy put in a separate dimension). I suspect the problem has to do with this somehow.
We have opened a call with Microsoft anyway. If they come up with more information I will let you know.
yes excel will change the xml files back next time you open the file, so modifying the xlsx file is just a temporary fix.
remeber that you can have hidden attributes that will not show up by default in the translation tab.
so you need to use one of the buttons at the top to make sure you see all possibel translatable attributes and you need to translate even the hidden ones or the file will break.
there must be a bug somewere causing this, question is if it's in excel or analysis services or maybe both.
i am prety sure that doesn't help at all.
it even makes things worse because now you can manualy modify the file to fix it.
as stated before, this issue is with missing translations in the cube.
and i'd consider this a bug because if there is missing translations it should fall back to the default language.
We opened a case with Premier Support and they confirmed that it is a bug in Excel 2007. The solution they gave us was to use Excel 2010 since the MDX that is generated in Excel 2010 is different. Then you can save it and open it in Excel 2007 without any issue.
I hope it helps to someone.
- Proposed as answer by Stéphane Guilleminot Tuesday, October 26, 2010 1:14 PM
I think this is my problem...except that I am unable to work out the technical part of the whole process, and migrating from 2007 to 2010 did not help the process. I was hoping to send the corrupted .xls files to one of you for redemption.
Can I, please?
I also come across the same problem of Crashing PivotTables in Excel 2007.....mine was very simple PT sourced from a Table in another sheet. I saved the fully working file and when re-opened the PT functionality gone.
The problem I noticed with my PT is it has two Fields of same name "Years". One field in source table itself and other I get when I grouped the dates field for Years.
After editing the pivotCacheDefinition1.xml for one of the "Years" to "Years2", I got my fully working PT back.
Hope this help you with your quest.
- Proposed as answer by Murtaza Ibrahim Tuesday, January 10, 2012 10:01 AM
Same problem to me, in Excel 2010.
Same solution here:
As TobbeJ2 expained, it can be solved my modifying the caption="" to caption="whatever" and changing your SSAS Cube Model so that all Elements have a translated caption.
- Edited by Andreas Michael Tuesday, July 31, 2012 12:08 PM