Power Pivot is a free add-in for Excel (versions 2010 and later) that transforms Excel into a powerful data modelling, reporting and analytical tool; and in doing so brings self-service business intelligence straight to your desktop.
At the heart of Power Pivot is the Power Pivot data model which is very similar to the Tabular Cubes we find in the latest versions of SQL Server Analysis Services (SSAS) or the Data Set we find in Power BI.
The Power Pivot data model stores the data, which can be imported from many different sources, as well as any calculations and aggregations that the developer has added for reporting.
There are certain limits that apply to Power Pivot, these can be found here.
Power Pivot uses a formula language called DAX (Data Analysis Expressions). DAX is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex groupings, calculations and analysis to take place. The syntax of DAX formulas is similar to that of Excel formulas.
Pivot Tables and Charts are used to present the data in the data model as attractive and interactive reports. The user experience can be further improved by incorporating slicers (filters) to a report which enables users to effortlessly select the data that’s most relevant to them.
- Instructions on how to activate Power Pivot for Excel 2013 can be found in our blog
- The Power Pivot add-in for Excel 2010 can be downloaded from here.
Example Power Pivot data models and Pivot Chart and Pivot Tables based reports created from them.