Understanding why an Excel PowerPivot workbook is so large

During my and Dave Wickert’s SQLPASS session (SQLCAT: A Preview to PowerPivot Best Practices), we had shown to the audience how to view and better understand the PowerPivot database file structure within the Excel workbook.  First of all, I’d like to give credit where credit is due – and the thanks really should go to Ashvini Sharma – Power Pivot, Analysis Services, and Integration Services Dev MasterMind.  So while the presentation piece is ours – the actual knowledge and know how is his :-).

Saying this, what do we mean by all of this?

For starters, recall that an Excel utilizing the PowerPivot add-in actually contains the database within the Excel workbook.  For more information on this, please refer to For Excel PowerPivot, the database is IN the workbook.

But now that you know that the database is IN the workbook, how can you find out why the workbook is so large as my esteemed PowerPivot twitter @VidasM recently inquired?

Well, if you recall from the presentation or blog post, when Excel utilizes PowerPivot, it makes use of the VertiPaq database.  While the database is primarily in-memory, it will have an on-disk structure for it cache against before finally saving itself into the workbook itself (when saved and no longer in use).  To find this temporary cache folder,

  • Open up an Excel PowerPivot workbook
  • Go to your %TEMP% folder (e.g. c:\Users\MyName\AppData\Local\Temp) and find the IMBI_<GUID> VertiPaq_<GUID> folder.  If there are multiple IMBI_<GUID> VertiPaq_<GUID> folders, find the most recent one
  • When you open up this folder, for those of you who work regularly with Analysis Services, this file structure will be very familiar as it is very similar to your native Analysis Services data folder structure.


  • Within the [Search Box] (upper right hand corner of your Windows Explorer for those using Windows 7) and type *.* and sort by size.


  • By doing so, you can now identify which IDF and dictionary files exist and which files are larger which correspond to the tables and columns you have imported.

While we have yet to develop best practices, the basic principal here is by doing this, you will be able to find the tables and columns that are taking up alot of space within your Excel PowerPivot workbook and can potentially remove any columns that are not required.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Denny Lee’s Tweets (1/2 of PowerPivot Twins!)


Copyright © 2010 Denny G Lee - All Rights Reserved

%d bloggers like this: