Mar 03, 2014

I’m sure that we can all agree that Microsoft Excel is an incredible application. You can do so much with it, but broadly speaking, there are 3 main functions that Excel performs:

1. Excel can hold data (in a table or range)
2. Excel can mask data (through formulas or different formatting options)
3. Excel can present data (i.e. through charts, PivotTables, conditional formatting, etc.)

In the above mentioned functions however, there are some limitations. For example, Excel 2007 and later can hold up to 1,048,576 rows of data and its chart types are limited to conventional graphs for data presentation. However, for most of what people do with Excel every day, these limitations do not surface at all. It is not until users actually start to work with huge volumes of data and/or when data is going to be received from several data sources outside Excel that they start to run into problems. This is when users start to wish that Excel could handle all that data, seeing as it is an application known for its data analysis capabilities. This is when PowerPivot comes to rescue. PowerPivot is an extension to Excel that expands the data capacity of Excel beyond imagination. It is added into Excel as a new tab in the Ribbon and acts as a data storage medium. Of course, it can do much more than this, but its main mission is to load large volumes of data from almost every data set available. You can practically load hundreds of millions of rows without loss of performance and use them in Excel PivotTables for data analysis. As I mentioned, it can do more than just hold data. It can create a data model by creating table relationships as we would in relational databases. It also introduces numerous functions that you can use to create measures on top of what PivotTables default measures provides such as ‘SUM,’ ‘AVERAGE,’ ‘COUNT,’ and etc. In fact, PowerPivot turns Excel into a robust Business Intelligence (BI) tool. While PowerPivot overcomes the limitations in data capacity and more, Power View helps improve the data presentation capability of Excel. It enables you to create compelling reports and visualisations based on your data. The data might be stored in either Excel sheets or PowerPivot or both. Power View is also an add-in to Excel similar to PowerPivot. It provides a fantastic user interface to quickly create charts, maps, tabular reports, and etc. If you are familiar with or heard of SQL Server Analysis Services (SSAS) and SQL Services Reporting Services (SSRS), then it’s easy to understand the difference between PowerPivot and Power View. PowerPivot is really an implementation of SSAS and Power View is an implementation of SSRS. The idea behind introducing these applications to Excel is that Excel is a very familiar tool for most business users while SQL Server is a tool only a few people can work with and master. It makes sense to add these capabilities to Excel as it is a more commonly used and lighter application. That’s why PowerPivot is called a Self-Service BI and Power View a Self-Service-Visualisation and Reporting tool.