Analyze the size of your PowerPivot workbook model – introducing PowerPivot Examiner
Inspired by the MS release of Workbook optimizer link and Kasper de Jonge’s post “What’s eating up my memory Powerpivot – Excel Edition” – Link – I actually found myself missing a tool that could tell me why my PowerPivot workbook was so big even though I had used these two tools.
One thing that really frustrated me was the fact that Office365 tells me that I can upload and browse my Powerpivot workbooks … but the maximum size for these files are 10 mb 😦 and it’s very hard to have millions of rows and stay below this limit.
So I decided to find out what is taking up space in my model.
- Open a workbook with a Powerpivot data model
- Open “PowerPivot Examiner” –
- Click the “Choose File” button and select the workbook opened under 1
- Click OK and the table below will be updated and so will the chart
The table is linked to PowerPivot and is then visualized in the pivot chart. And because we are in Excel 2013 – you can now drill up and down in the pivot chart.
So when I double click the largest table I can see which columns in the FactSales is taking up the space
Reading the chart I can see that I have no chance with the current number of rows in my fact table I have no chance of getting below the 10 mb magic limit.
But I can also see that if I remove the Totalcost and UnitPrice I can reduce my file size from 36 mb with appx 9 mb to 27 mb – and there by optimize the load time and thes speed of the model.
If you have comments or suggestions to the tool please don’t hesitate to contact me via the comments.
Here is a link to the model – Link