PowerPivot is a great new technology for Self-Service BI. In previous blog entries I have outlined some of the technology using SQL Server 2008 R2 and Excel 2010. The concept of accessing data from various sources and bringing them together in a PowerPivot model is very powerful. The fact that it can all be done on the client is truly amazing.
But how does this compare to using a native Analysis Services (SSAS) cube and accessing the data remotely via the Excel 2010 Client? With an OLAP system we typically start out with a well-designed Data Warehouse that is loaded incrementally every night with the latest operational data. SSAS will convert that relational database into a multi-dimensional “cube” for rapid access across huge amounts of data. MDX queries can be submitted to the cube for fast aggregation and analysis. The role of the Excel client is to allow the PivotTable and PivotChart user interface to submit powerful MDX queries under the hood transparent to the user. This is done through the OLE DB Provider for Analysis Services. All the user sees is the data.
Enter PowerPivot with Excel 2010. Through the PowerPivot Window, we can build a PowerPivot model that consists of one or more Data Sources and associated relationships. The Data Sources may include relational databases or multi-dimensional cubes as well as data from text files or reports. The key differentiator here is that the data is actually imported into the spreadsheet locally on the client. So you may see the spreadsheet grow accordingly but using the new “VertiPaq” technology the data is massively compressed at the column level. Now we can build a PivotTable or PivotChart in Excel as normal using the PowerPivot model as the source.
In my testing, performance using native SSAS was lightning fast even across millions of rows of data. This is because of the pre-processed aggregates in the cube that limit the number-crunching aggregation needed. The multi-tier architecture helps performance too with the great majority of the processing happening on the server. Call me traditional, but that is how it should be. Of course, the trade-off is that you have to design, implement and maintain a Data Warehouse with an SSAS Cube and provide the hardware, services and support that go along with it.
PowerPivot allows ad-hoc access to multiple data sources, effectively building a cube on the fly while hiding the complexities of OLAP from the user. Once the PowerPivot model is built and data imported, all the processing is on the client. There is no need for a Data Warehouse or Cube, although if they exist they would be prime candidates for data sources. When stress testing with millions of rows in my PowerPivot model, I did notice sluggish aggregations since they were being performed interactively and could not be pre-processed as is the default in SSAS. However, I did have to pinch myself to realize that this was, yes, Microsoft Excel working alone with millions of rows of data to produce some very powerful and attractive results with minimal effort.
Author: Brian Egler