PowerPivot for Excel is an add-on for Excel 2010. PowerPivot allows users to conduct powerful business intellegence (BI) in an environment that is familier. It's a free download from Microsoft and allows users to work with extremely large data sets. Before PowerPivot, this kind of analysis was limited to enterprise BI tools such as SAS and Business Objects.
PowerPivot uses an in-memory engine called VertiPaq. This new SSAS engine takes advantage of the increased RAM available in most personal computers today. I have been using PowerPivot for the past few months and I think is has a lot of potential. Most IT shops are challenged with the resources needed to build out an enterprise BI environment. PowerPivot moves some of this work closer to the business user. While there are many features in PowerPivot for Excel, I have chosen 5 that I consider to be the coolest.
1. Work With Very Large Data Sets
If you open Microsoft Excel and move to the very bottom of a worksheet, you will see that the maximum number of rows is 1,048,576. This represents about a million rows of data. With PowerPivot for Excel, there is no limit on the number of rows of data. While this is a true statement, the actual limitation is based on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint 2010.
If you are running the 64-bit version of Excel, PowerPivot can reportedly handle about 2 GB of data. You must have enough RAM to make this work. If you plan to publish your PowerPivot based Excel spreadsheet to SharePoint 2010, the maximize file size is also 2 GB. The bottom line is PowerPivot for Excel can handle millions of records. If you hit the maximum, you will receive a memory error.
If you want to play with PowerPivot for Excel using a couple of million records, download the PowerPivot for Excel Tutorial Sample Data which has the data you need for the PowerPivot Workbook Tuturial. The Microsoft Access database included with the sample data has about 2.3 million records.
2. Combine Data From Different Sources
This has to be one of the most important features in PowerPivot for Excel. Excel has always been able to handle different data sources such as SQL Server, XML, Microsoft Access and even web based data. The problem comes when you need to created relationships between different data sources. There are 3rd party products available to help with this, and you can use Excel functions like VLOOKUP to "join" data. But these methods are impractical for large data sets. PowerPivot for Excel is built to accomplish this task.
Within PowerPivot, you can import data from virtually any data source. I have found that one of the most useful data sources is a SharePoint List. I have used PowerPivot for Excel to combine data from SQL Server and a list from SharePoint. You will need SharePoint 2010 to make this work along with the ADO.Net runtime installed on the SharePoint environment.
When you connect PowerPivot to a SharePoint list, you are actually connecting to a Data Feed. To create a Data Feed from a SharePoint list, open the list and click on the List ribbon. Then click on Export as Data Feed and save it. The feed is available as a URL in PowerPivot for Excel. Check out the white paper Using SharePoint List Data in PowerPivot for more information on using SharePoint as a data source for PowerPivot.
There is one challenge that will be resolved by Microsoft soon. PowerPivot for Excel only supports inner joins. If you need to do an outer join, you will need to wait until an update is released from Microsoft or tweak your data so that an inner join can be used.
3. Create Visually Apealing Analytical Models
PowerPivot for Excel allows you to output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts and a Flattened PivotTable. The power comes when you create a worksheet that includes multiple outputs. This provides a dashboard view of the data that makes analysis really easy. Even your executives should be able to interact with your worksheet if you build it correctly. Slicers, which shipped with Excel 2010, makes it simple to visually filter data.
4. Use DAX to Create Calculated Fields for Slicing and Dicing Data
DAX stands for Data Analysis Expressions and is the formula language used in PowerPivot tables. There are similarities between Excel formulas and DAX. The DAX formula language is used primarily in creating calculated columns. Check out the DAX Reference in TechNet for a complete reference.
I typically use DAX date functions to make date fields more useful. In a regular Pivot Table in Excel that included a properly formated date field, you can use grouping to include the ability to filter or group by year, quarter, month and day. In PowerPivot, you need to create these as calculated columns to accomplish the same thing. Add a column for each way you need to filter or group data in your Pivot Table. Many of the date functions in DAX are the same as Excel formulas which makes this a snap.
For example, use =YEAR([date column]) in a new calculated column to add the year to your data set in PowerPivot. You can then use this new YEAR field as a slicer or group by in your Pivot Table.
5. Publish Dashboards to SharePoint 2010
If your company is like mine, dashboard are still the work of your IT team. PowerPivot when combined with SharePoint 2010 puts the power of dashboards into the hands of your users. One of the prerequisites of publishing PowerPivot driven charts and tables to SharePoint 2010 is the implementation of PowerPivot for SharePoint on your SharePoint 2010 farm. Check out PowerPivot for SharePoint which is on MSDN. Your IT team will have to do this part. Be nice to them and I am sure they will update SharePoint 2010 for you.
