Excel Pivot - SSAS Error

Hi all

I've got a nasty problem with excel and SSAS.

Here's the problem:

When I create a pivot using SSAS as source everything works fine. Even saving works perfect.
But when I close the file and open it again I get an Error:

Entferntes Feature: PivotTable-Bericht von /xl/pivotCache/pivotCacheDefinition1.xml-Part (PivotTable-Cache)
Entferntes Feature: PivotTable-Bericht von /xl/pivotTables/pivotTable1.xml-Part (PivotTable-Ansicht)
Entfernte Datensätze: Eigenschaften der Arbeitsmappe von /xl/workbook.xml-Part (Arbeitsmappe)

This is only the case when I use one (of my two) parent-child-hirarchies!

I've read that SP1 of excel should solve this. But since I use SP2, this can't be the case.

Excel 2007 (12.0.6545.5000) SP2 MSO (12.0.6545.5004)

SSAS 2008 10.0.1600.22

I ran into this exact problem a while back.  Your problem is related to the openxml that gets saved from 2007 - the default save format for excel 2007 is xslx and that's where the issue is.

You will find that if you create the same pivot table and then save it as XLS it will save.   If you reload it will load correctly.   After this you can also save out as XLSX and your pivottable will work from then on.

It took me a while to figure out what would work.  Unfortunately MSFT doesn't have a fix for this yet.  I haven't yet tried to see if the fix is in 2010,  but for the meantime,  I would suggest you save out as XLS or have a template empty XLS to create pivots.

Hope this helps you,

Luis.

Hi Luis

Thank you very much for your help. Your absolutely right. If I save it as .xls it works fine. But if I reopen the xls and save it as xlsx, I get the same error as if I save it right away as xlsx.

To make it work as xlsx, I have to open excel and save an empty sheet as xls. Then I have to first reopen this empty xls, before I make the connection to the SSAS. Then I get this 'old-style' pivot with the blue lines. Now I can convert it to xlsx. And this xlsx I'm able to open without a error msg. (Note: the pivot still has got those blue lines).

At least I know now a workaround, but it's a bit odd that MS doesn't release a fix to this (I guess rather common) problem. I mean who has cubes without parent-child hirarchies?!?

Good information on workarounds for this problem. 

I have not encountered this problem but often I work very hard to avoid using true recursive parent-child hierarchies in SSAS due to processing/query performance concerns. Can this be reproduced with a flattened non-recursive ragged hierarchies? I am also not experiencing this in Excel 2010 so perhaps that will be good news on the horizon for those haivng this problem.