Power Pivot Summary, Features and Sample
The simple answer: Power Pivot is a free add-in to Microsoft Excel to support self-service BI. It is available to all version of Excel 2010 and some versions of 2013.
Compared to traditional Excel:
- Much larger data volumes are possible; 100 million rows is not out of the question
- The ability to import data from a large variety of data sources.
- The ability to store data in separate tables, with persistent relationships.
- Rich programming language (DAX) allowing for complex measures in pivot tables.
The pivot table at right (which is based on the model above):
-
Was built in less than 15 minutes
-
Uses no calls to VLOOKUP, OFFSET, MATCH, etc.
-
Works split out by continents and years based 100% on the relationships. No specific logic for it.
-
Is using info from three different tables
-
Is showing a rich %Growth measure
-
Could handle sales data with millions of rows.
Care to try that in traditional Excel?!
Power Pivot Technology and Ecosystem
There is a lot that could be said about the technology underlying Power Pivot, but I am going to limit it to a few points: It is a purely “in memory” technology, it is highly compressed, and because of these two things… it can hold a lot of rows, and it is stupid fast. (which is another way of saying “insanely fast” 
).
It is also built right into Excel, an environment you already know, love and can continue to leverage. It also integrates with SharePoint – enabling you to schedule your workbooks to automatically refresh from the original data sources, so that your workbooks always present the latest data while you are snoozin’.
I have heard it said that “Power Pivot puts the Power in Power BI”, and completely agree. But the supporting cast of Power Query, Power View, Power Map certainly bring something to the party. That Microsoft continues to invest heavily in these tools means we will all benefit for years to come.