Posts Tagged 'Database'

How much data can I load into PowerPivot?

By powerpivotgeek (, on August 22nd, 2010

Recently a question has come up on one of the forums about the number of rows that PowerPivot can load. Clearly PowerPivot can hold a lot – just look at our demos. Millions and millions . . ah . . but how many millions?

The key geek ‘magic’ is the Vertipaq engine. The in-memory Vertipaq engine has an extensive array of techniques that it uses for compressing and manipulating the data. Being an in-memory database system means that the more data that we can get in . . the more data we can scan . . the more kinds of applications that we can solve. So compression is a HUGE deal for us.

Please click through to continue reading: How much data can I load into PowerPivot?


A Peek Inside: Allocation. What is it? Why is it important?

By powerpivotgeek (, on April 2nd, 2010

Recently I have been getting some questions about an earlier post that I did around how the PowerPivot inactivity unloading is done. see for more details. Surely there must be a faster mechanism for unloading data – and there is – but to fully discuss that aspect of the system we need to take a step back and talk about how databases get loaded in the first place. I don’t mean the actual loading process (that is another interesting post that I’ll do later on), what I mean is how PowerPivot decides to load something to begin with . . . and where it decides to load it.

Read more…

A Peek Inside: Unloading PowerPivot data

By powerpivotgeek (, 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…

Why you may want to use RBS for your #PowerPivot for #SharePoint Farm

By Denny Lee, Ed Campbell

Introducing RBS

So what is RBS and why would I want to use it in order to help my PowerPivot for SharePoint farm?  Well, RBS in this particular case is Remote Blob Store which was introduced as part of one of the SQL Server 2008 Feature Packs.  As of this post, the current version can be found in the SQL Server 2008 R2 November CTP Feature Pack (search SQL Server 2008 R2 Remote Blob Store).  Simply put, remote blob store allows you to put blobs outside of the SQL Server database so you can reduce the performance and manageability impact of storing blobs directly within the database.  For more information about RBS, please refer to the documentation linked below.

Read more…

What database support is needed for #PowerPivot

By powerpivotgeek (, on January 29th, 2010

I get this question a lot and I thought that it was a good one to address here in the blog.

If you look at the PowerPivot for SharePoint architecture, you know that we use several SQL Server 2008 R2 components within the architecture, but how about SharePoint itself . . . What RDBMS does PowerPivot with SharePoint 2010 use? and does it need to be R2?

Read more…

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


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


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


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

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


Copyright © 2010 Denny G Lee - All Rights Reserved