Posts Tagged 'VertiPaq'

A Peek Inside: Unloading PowerPivot data

By powerpivotgeek (dwickert@microsoft.com), on March 4th, 2010

Ok folks. Here comes a cool one.

As I am sure that everyone is aware by now, one of the most powerful new features of PowerPivot is that we do on-demand loading of embedded data. Well, if you are going to load it, then you had better at least think about unload it at some time. There is probably some law of thermodynamics that applies here.

First rule for deleting data is that we don’t touch data if there are users currently connected to it. But other than that, any databases loaded or cached by the PSS (i.e. their database folder is contained in the SSAS Backup folder tree) are candidates for being unloaded.

There are two ways that databases can be unloaded in PowerPivot:

Read more…

Advertisements

For Excel PowerPivot, the database is IN the workbook

Concerning Excel PowerPivot, the question is often asked how is it possible for Excel to now handle tens or hundreds of millions of rows – the way native Excel handles tens or hundreds of thousands of rows. 

How you ask? 

Well, the reason why is because Excel (with the PowerPivot add-in) is using the new In-Memory BI (IMBI) database.  It utilizes a column-based store which has been seen academically and empirically to have some interesting characteristics within BI scenarios…namely, it can calculate many (but not all) BI aggregate very quickly.  Because it is in-memory, it does not utilize disk I/O and performs all of its scans in-memory – i.e. much faster because you do not have disk contention.  As well, it has a great compression algorithm in order to significantly reduce the size of the overall data stored.  By the way, this new database architecture is called VertiPaq for all those interested in the trivia!

So while Vertipaq is really cool, what does this have to do with Excel PowerPivot?  Well, the reason is because the PowerPivot database is actually in the workbook itself.  To prove it, here’s what you can do:

  • Take your Excel PowerPivot workbook, which you’ll notice is typically much larger than your typical native Excel workbooks
  • Copy the Excel file and rename it using a .zip extension instead of a .xlsx.  Note, the reason why is because .xlsx is actually XML

image

  • When you open up the .zip file, you’ll notice a lot of various folders within it (as noted, it’s all XML).

image 

  • Open up the xl folder, and then you’ll notice the customData folder.  When you navigate to it, you’ll notice the item1.data file which contains the bulk of the size of the Excel file

image

This item1.data file is your PowerPivot database stored right IN the Excel file.