PowerPivot is a free addin for Excel 2010 and higher. It will actually ship with Office 2013. In a nutshell, PowerPivot is a data analysis tool that allows you to quickly create data cubes and pivot tables. You can build interactive dashboards, scorecards, KPI's (key performance indicators), and analytic charts and filters.
This is a tool that can be used by any data analyst or BI (Business Intelligence) professional. The tool is very easy to use but rich in features and functionality. I was able to download and install the plugin and create my first pivot table within thirty minutes! This is not a bad learning curve considering I did not use any documentation or tutorials. If you have ever built Data Source Views, Cubes, and Dimensions in SSAS (Sql Server Analysis Services) you will be blown away at the ease of using this tool. Will it replace SSIS? Not likely, but the amount of work you can accomplish with PowerPivot is astounding.
The data sources PowerPivot can pull data from are rich and include...
Further, you can pull the data by writing an sql query, so there is no need to even have the data already in a pre-defined dimensional model format (fact and dimension tables).
PowerPivot has been around since 2009 and is one of the best kept secrets out there. I recently saw a Microsoft posting for a data analyst position soliciting experience creating native excel pivot tables (2/27/13). Is it still that secret that even parts of Microsoft are not familiar with this terrific tool? No, Microsoft is not paying me to sing praises about PowerPivot. It is just an awesome tool. I believe the secret will not be kept for long as this is what I consider to be a groundbreaking tool in the BI world!
I have created an introductory video on using PowerPivot so you can see the tool for yourself. If you would like to follow along with the video I have also included the data source I am using in the zip file below.