keep pulling in tables until you get what you need, save the file, and move on to the next project. To this end, many users will do exactly what I did. This is a tool marketed at Excel pros without DB experience to create ad-hoc reporting. This does highlight a bit of a problem with PowerPivot.

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

File open, refresh and load is much quicker, and I can drive the whole model from one slicer, rather than 3. Overall effect is that the file has gone from 12MB to 0.5MB. 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. This time around, I actually went back and edit the SQL queries to only pull in the columns I needed from SQL. I wrote a few DAX formulas (calculated columns in PowerPivot) to be able to cut my data down using slicers. (3 in all.) We then used one Excel table, linked to PowerPivot, to further refine sales area grouping. So we just pulled in the full database tables into the file. When I orignally built the file, almost a year ago, I was still pretty new to PowerPivot. Mainly just better data selection from SQL The good news is that I was able to make the file MUCH more efficient than I did when I originally built it. Hopefully they can figure out what happenend and improve their error/recovery algorithms out of it. I have uploaded the file to Microsoft for them to look at.

In addition, the original workbook still crashed Excel even if trying to open it in safe mode.The PowerPivot data was not present, even if I tried to copy the _rels files as well Transplant was successful, but the file kicked off a recovery upon opening.Then open the crashing file in 'safe' mode and try copying sheets over to new wkbk one at a time. Create 1 pivot against the model, save file. 'Transplant' item1.data into a blank 'host' workbook.Unfortunately the file still crashed on opening, without PowerPivot's recovery process ever kicking in.Then trigger the PowerPivot recover process (as compared to the Excel recovery process) Zeroing out item1.data as described near the end of this post.I tried a few fixes that Rob Collie suggested, including: I made notes of all the tables and relationships, then rebuilt the file from scratch. 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.

Excel 365 powerpivot