Skip to content
May 6, 2013 / Erik Svensen

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.

Quick guide

  1. Open a workbook with a Powerpivot data model
  2. Open “PowerPivot Examiner” –
  3. Click the “Choose File” button and select the workbook opened under 1

  4. 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.

Feedback

If you have comments or suggestions to the tool please don’t hesitate to contact me via the comments.

Download

Here is a link to the model – Link

 

Advertisements

6 Comments

Leave a Comment
  1. mdenis2013 / Jul 26 2013 12:02 am

    Hi Erik, thanks for the PowerPivot Examiner. I am using and is very useful. However not allways show the tables or queries included in the model. If you want I can send you a spreadsheet that have tables not showed in PP Examiner.

    (sorry my english, i am spanish spoken)

  2. Viv / Jul 30 2015 2:14 am

    That’s a simple but very useful tool. Thanks for this.

  3. Pierre / Nov 5 2015 3:22 pm

    Erik,
    FYI: I downloaded the PowerPivot Examiner.xlsm but couldn’t run it successfully because

    Excel online altered the workbook by removing unsupported features (e.g. controls) and it doesn’t give me the option of downloading the original workbook.

    https://support.office.com/client/Differences-between-using-a-workbook-in-the-browser-and-in-Excel-f0dc28ed-b85d-4e1d-be6d-5878005db3b6

    • Erik Svensen / Nov 6 2015 7:42 am

      Hi Pierre

      I can send you a copy to your mailbox if you are interested

      /Erik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: