Q: How can I find how much space each table uses in PowerPivot?

Q: How can I find how much space each table uses in PowerPivot?

A: To find size of each table in PowerPivot workbook you should investigate temporary folder created by PowerPivot. This temporary folder is created when you open Excel workbook and Pivot table that is based on PowerPivot data is updated or when you go into PowerPivot for Excel window. This temporary folder is located in:

c:\Users\<UserName>\AppData\Local\Temp\Vertipaq_<GUID>

For example, on my machine folder is c:\Users\Vidas\AppData\Local\Temp\Vertipaq_F46D80AF55965A1590E1

Note: On Windows 2008R2 server this folder will be located in subfolder, examle:

c:\Users\<UserName>\AppData\Local\Temp\1\Vertipaq_<GUID>

Additional subfolders are created because on the server operating system the same user can login to the same workstation more than one time.

There could be multiple Vertipaq_<GUID> folders and you should find folder that has timestamp that matches workbook opening time.

To find how much total memory your workbooks PowerPivot is using you simply check size of that folder (right mouse click on folder and then choose "Properties")

As you can see, ContosoBI database that I loaded into PowerPivot uses 357MB.

Now we can explore content of that Vertipaq folder. This folder structure is very similar to Microsoft SQL Server Analysis Services data folder structure. We are mostly interested in the folder that ends with ".db" - that is folder that contains all our PowerPivot data. On my machine that folder name is "1645AD372D14EFC848C.2.db".

Our ".db" folder contains list of folders with extensions ".dim" (dimensions), ".ds" (data source)  and ".cub" (cube) and corresponsing ".xml" files.

Each folder name consist of unique <GUID> string that looks like "1E606773-f64a-4532-8134-1550045b3d73.0.dim". Matching xml file will start with the same <GUID> value but at the end might have different version number, example: "1E606773-f64a-4532-8134-1550045b3d73.2.dim.xml". This xml file can be opened in IE and it will show name of the table. Here is screenshoot of sample xml file:

When we know table name that this specifi <GUID> represents, we can check related folder size and this way find out how much memory this table is using in our PowerPivot. For our table size test we are interested just in ".dim" folders as these folders represent tables.

In ContosoBI PowerPivot workbook table sizes are:

This folder will also contain file "Sandbox.1.cub", but size of that file is not that big. For our ContosoBI database size of this folder is 360KB.