Kendal VanDyke last week asked me if I would do one of my PowerPivot presentations at this month’s MagicPASS meeting on April 20th because the scheduled speaker was not able to attend. . I had already prepared my presentation for SQL Saturday Jacksonville on the 30th so I figured, sure, why not.
Despite the fact the Excel has supported pivot tables and pivot charts for some time now, the built-in capabilities of Excel still limited the amount of data that you can use. It was also not convenient to use multiple data sources. But with the introduction of Excel 2010, Microsoft created an add-in to Excel called PowerPivot (originally named Gemini) which would use the Analysis Services VertiPaq engine and run in-process in Excel. It would allow you to import data from many different data sources in building the pivot table and/or chart. It would also allow you to have tables with millions of records. It would take all of the data and compress it very highly and store the data in the Excel .xlsx file although not in the same way that it stored the other native Excel data.
From my point of view, PowerPivot is a great addition for business analysts to create different views of their data without having to write a bit of code. They can choose different dimensions (parameters in a way) to look at, sort, and filter the data to answer many different questions. Why should a database administrator or analyst care? Because, why the business analyst can very quickly learn how to manipulate the pivot tables and charts for a given set of data to answer different questions, they probably cannot create the original data set or define the relationships to link the various data sources. As a DBA or analyst, you can spend just a few hours to build the basic pivot tables files (individual or by building a data warehouse or even an Analysis Services cube) and then show the analyst how they can pick different measures from their fact table and slice and dice that data with dimension data.
If you are in Orlando and have time Wednesday April 20th, come on down to Stetson University in Celebration by around 5 PM. If you can not make it, I’ll be doing a similar presentation at SQL Saturday Jacksonville on April 30th.