Powerpivot is a relatively new addition to the Microsoft Business Intelligence world. It adds quite a bit of capability to their platform. This release was designed to overcome some of the historical limitations of working in an Excel based structure.
It’s basically an add-on for the Office 2010 package. It’s kind of like Excel on steroids. For now, there is no version available for older versions of Office so it may be a compelling reason to upgrade if you haven’t done so yet.
It serves as a connection point between several Microsoft technologies. Of course, it lives within the Office infrastructure as a front end. It also allows collaboration through the Sharepoint server. It can access data within the SQL Server framework.
One of the big issues with spreadsheet work is that they were not easily shared or collaborated on. Users could not easily work on different parts of a model. Using Sharepoint helps a lot with the collaboration piece of the puzzle. The security and infrastructure there can be easily accessed. IT can insert themselves and manage the data integrity and security.
In addition, Excel users were previously limited in the amount of data that could be accessed in a sheet. While these limits had been increased over the years, it was still very limited especially when looking at enterprise datasets. That annoying row limit has been vastly increased. Powerpivot adds the ability to have data “in-memory” which can significantly speed up work. No more waiting minutes to wait for a sheet to load. The add-in itself does take a little while to load.
One of the new features is a new DAX (Data Analysis Expressions). It’s somewhat like the familiar function ability but has some bigger powers. You can apply formulas to whole rows and columns easier, making things more database like.