I find myself explaining PowerPivot to customers over and over. Most understand parts of how it works but not the whole story. I will touch on two parts; PowerPivot for Excel and PowerPivot for SharePoint.
Part 1: PowerPivot for Excel
PowerPivot for SharePoint is nothing without the Excel Rich Client and the PowerPivot Add-in. Pre-Excel 2013, the PowerPivot Add-in was a separate COM Add-in that you had to download. In Excel 2013, the PowerPivot Add-in ships with the product.
Before you can do anything in SharePoint, you need to create a PowerPivot workbook in Excel. To do this, open the PowerPivot tab choose Manage > From Database (choose your datasource, servername and database name).
You (potentially) will be pulling in a large amount of data into Excel. I have seen people with Excel (PowerPivot) workbooks 1GB in size (which is huge since .xlsx (Open Office XML) files are zipped). This large amount of data is essentially a database and you can see this in the workbook. To view the Data Model, rename workbook file extension (.xlsx to .zip), open the workbook > xl > model > item.data.
After you have pulled this data (essentially a database) into the PowerPivot environment, Excel can point to this database and build a PivotTable off it. In the PowerPivot environment choose PivotTable > PivotTable
Excel is now pointing at the database (Data Model) embedded inside itself. You can see for yourself via Excel > Data Connections > Properties. In SharePoint (when we get there), we will call this the “Refresh in Browser“.
Keep in mind there is still the PowerPivot Connection that can also refresh. In SharePoint, we will call this the “Scheduled Data Refresh“. You can view this connection in Excel > PowerPivot > Manage > Existing Connections (choose the PowerPivot Data Connection) > Edit > Advanced.
At this point, we should have a beautiful Excel (PowerPivot) workbook that has a Pivot Table and Slicers and looks something like the below sample.
Now that we have a beautiful Excel (PowerPivot) workbook that functions wonderfully in the Excel Rich Client, we are ready to publish this to SharePoint!
Please see “What is PowerPivot for SharePoint? Part 2“.