PowerPivot file crashing on opening

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
This one is fun... not...

I have a file that we use once per month to generate a list of gratuities earned. PowerPivot was a godsend in this case, as the info was really difficult to source before. The file has been in production since May 2010, and when I went into Excel this month it crashed upon opening. :(

Here's what happens:
  • Opening the file crashes Excel
  • Opening Excel (not the file) then crashes Excel
  • Opening Excel (not the file) again allows you in, at which point you're taken to the "Document Recovery" pane
What's interesting here is that when you ask to show repairs, you get the message "No errors were detected in 'Gratuities Payable-issue.xlsx'. No repairs were necessary." (I renamed the file so I could restore a backup.) Despite this, upon saying OK, the file is presented with no PowerPivot data, Pivot Tables, table formats, or anything.

Has anyone else seen this kind of issue, and if so, did you come up with a fix for it?
 
With enough opens and closes I eventually managed to get one that came up where the Excel reports were hosed but the PowerPivot data was still there. I made notes of all the tables and relationships, then rebuilt the file from scratch. Fortunately it was an easy one.

I tried a few fixes that Rob Collie suggested, including:
  • Zeroing out item1.data as described near the end of this post http://bit.ly/g15XvS. Then trigger the PowerPivot recover process (as compared to the Excel recovery process)
    • Unfortunately the file still crashed on opening, without PowerPivot's recovery process ever kicking in.
  • "Transplant" item1.data into a blank "host" workbook. Create 1 pivot against the model, save file. Then open the crashing file in "safe" mode and try copying sheets over to new wkbk one at a time.
    • Transplant was successful, but the file kicked off a recovery upon opening. The PowerPivot data was not present, even if I tried to copy the _rels files as well
    • In addition, the original workbook still crashed Excel even if trying to open it in safe mode
I have uploaded the file to Microsoft for them to look at. Hopefully they can figure out what happenend and improve their error/recovery algorithms out of it.

The good news is that I was able to make the file MUCH more efficient than I did when I originally built it. :)
 
Mainly just better data selection from SQL

When I orignally built the file, almost a year ago, I was still pretty new to PowerPivot. So we just pulled in the full database tables into the file. (3 in all.) We then used one Excel table, linked to PowerPivot, to further refine sales area grouping. I wrote a few DAX formulas (calculated columns in PowerPivot) to be able to cut my data down using slicers.

This time around, I actually went back and edit the SQL queries to only pull in the columns I needed from SQL. In addition, rather than try to use DAX to give myself a slicer friendly data set, I just added a couple of WHERE statements in the SQL to cut the data down at the outset.

Overall effect is that the file has gone from 12MB to 0.5MB. File open, refresh and load is much quicker, and I can drive the whole model from one slicer, rather than 3.

Really should have done it a long time ago, but it worked until yesterday, and if it ain't broke... With it breaking, it was a good chance to revisit.

This does highlight a bit of a problem with PowerPivot. This is a tool marketed at Excel pros without DB experience to create ad-hoc reporting. To this end, many users will do exactly what I did... keep pulling in tables until you get what you need, save the file, and move on to the next project. Extra copies of data will be streamed into these files and mount up, many times without the user really being aware of it. Unless the file becomes one that is re-used, there won't be any discernable performance issues that motivate the user to cut the data down... after all, they've moved on to a new project, right? I can see where these files will put a major drain on server space.

I'm not sure that Sharepoint would even solve that, as I'll bet that a large amount of models using PowerPivot will be built for ad-hoc purposes and never uploaded to the server as "real" applications...
 
I think that is a critical point with PP Ken. To use it, you need to understand data modelling, at least to a certain level, and it is better to have some basic SQL. Marco and Alberto do make this point in their PP book, something Bill Jelen does not in his, and I think it is a barrier to Excel Pros (as per Rob Collie's definition) taking PP up, if they even wrap their heads around what is a pretty alien concept they are likely to see poor performance/wrong results - and guess what they will blame.

And most Excel users will have no access to Sharepoint ... (I will go to my grave chanting this I am sure).
 
... - and guess what they will blame.

And most Excel users will have no access to Sharepoint ... (I will go to my grave chanting this I am sure).

Agreed on all points. :(

I need to pick up Marco and Alberto's book. I think I have a brief level of data modelling understanding, but it would be nice to actually be able to put a definition to it. I can't do that as is.
 
This one is fun... not...

I have a file that we use once per month to generate a list of gratuities earned. PowerPivot was a godsend in this case, as the info was really difficult to source before. The file has been in production since May 2010, and when I went into Excel this month it crashed upon opening. :(

Here's what happens:
  • Opening the file crashes Excel
  • Opening Excel (not the file) then crashes Excel
  • Opening Excel (not the file) again allows you in, at which point you're taken to the "Document Recovery" pane
What's interesting here is that when you ask to show repairs, you get the message "No errors were detected in 'Gratuities Payable-issue.xlsx'. No repairs were necessary." (I renamed the file so I could restore a backup.) Despite this, upon saying OK, the file is presented with no PowerPivot data, Pivot Tables, table formats, or anything.

Has anyone else seen this kind of issue, and if so, did you come up with a fix for it?
This problem is quite complicated and some specialists' Microsoft recommend such type of programs if their products can't solve out these troubles. This tool can more unusual facilties for repairing outlook express information - repair excel file.
 
This is true, i've read many MVP say that in these cases the best is a recovery tool for Excel, but i know few good tools and we have to pay for them.
 
Back
Top