Data Mashups, Hundreds of Million Rows in Excel 2010 Using PowerPivot

The PowerPivot client add-in for Excel 2010 is the best feature to hit Excel in 20 years.

Excel 2010 introduces slicers, sparklines, the AGGREGATE function, the Equation Editor and a smoother way to paste, but by far, the best feature in Excel 2010 did not come from the Excel team. After installing Excel 2010, download the free PowerPivot add-in from www.PowerPivot.com. Authored by the SQL Server Analysis Services group at Microsoft, PowerPivot adds serious business intelligence tools to Excel 2010. Here are four amazing things that PowerPivot enables: 1. Store hundreds of millions of rows in an Excel workbook. You will no longer have to produce 1 million row subsets for your Excel users. Give them access to the whole data set, even if that data set is 200 million rows. Obviously, to load 200 million rows into memory, you will have to have a pretty serious 64-bit machine with a ton of RAM. 2. Import data from anywhere. PowerPivot can consume data from SQL Server, Access, Excel, text files, Oracle, Teradata, ODBC, SharePoint reports, atom feeds. You can import SQL Server into Sheet1, text files into Sheet2, and an atom feed into Sheet3. 3. Mash up data without mastering VLOOKUP: Say that you have 2 million rows on Sheet1 with an ItemID field. Sheet2 contains the product master database, mapping ItemID to a variety of descriptive fields. PowerPivot can create a single Pivot Table offering fields from both worksheets. Traditionally, your power Excel users would have created 10 million slow VLOOKUP formulas to solve this problem. Your non-power users would have been stopped in their tracks. PowerPivot analyzes the data, figures out how to join the tables, and builds a star schema, all behind the scenes. 4. New time-intelligence functions. PowerPivot offers 80 functions that are similar to Excel functions and then adds 60 new functions that should have been in Excel. When building a pivot table, you could calculate Month to Date sales from a parallel period that is 1 year ago. PowerPivot also adds date functions that work even if your fiscal year doesn't end on December 31. While the client version of PowerPivot is free, you may want to invest in the SharePoint version of PowerPivot. This tool allows your Excel users to post their reports to a SharePoint library. From their, you can use the PowerPivot dashboard to see which reports are being used frequently and by whom. The Excel client version of PowerPivot requires Excel 2010. It is a painless install and doesn't interfere with the normal operation of Excel. Offer a version to a few of your star Excel data analysts and they will fall in love with the new reporting opportunities. Additional Resources: If you opt to buy PowerPivot for Sharepoint, you will want to read Denny Lee's Professional Microsoft PowerPivot for Excel and SharePoint The data analysts who are using the Excel client version of PowerPivot will want to read my PowerPivot for the Data Analyst: Excel 2010

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2010 IDG Communications, Inc.