PowerPivot Raises the Bar on Row Limitations

Microsoft’s PowerPivot is an add-in for the company’s forthcoming Excel 2010 spreadsheet application, which first grabbed my attention by the way it enables users to work with much larger sets of data than is possible with Excel alone. For instance, I used PowerPivot to load and browse through a data set that ran 3.9 million rows—about four times Excel’s existing upper limit—and I had no more trouble scrolling around in the set than I would with a spreadsheet of only several hundred rows.

Typically, bumping up against the row limitations of your spreadsheet application indicates that a database is probably a better tool for the job. But it’s tough for heavy spreadsheet users to give up the familiarity of a favored tool.

As compelling as PowerPivot’s speed with lots of rows is, the free add-in’s knack for more effectively linking up Excel workbooks with one or more database back ends is a big part of what PowerPivot is meant to accomplish. I used the product to tap data in Access databases, in flat files and in MySQL databases, through an ODBC driver.  

PowerPivot comes with a new language for crafting relational expressions, called Data Analysis Expressions (DAX). It resembles Excel’s existing functions capabilities, but is better-suited to the add-in’s database orientation. For instance, DAX operates on whole columns or tables, rather than on ranges or individual cells, so there’s no need to drag formulas across vast spreadsheet real estate or twiddle with range definitions as the amount of data in a set changes.

The versions of PowerPivot and Excel 2010 I tested are still in development, and I hit a few rough spots during my tests. On a pair of occasions, the data embedded within my test workbooks became corrupt, and I hit some snags with my tests with MySQL.

With that said, I expect that once these bugs are squashed, PowerPivot will prove a powerful arrow in the quivers both of IT administrators out to provide their users with more flexible access to company data and of business analysts looking for more ways to crunch that data with familiar tools. What’s more, there’s a Sharepoint integration element to PowerPivot—which I did not test—that’s intended to provide PowerPivot users with easy ways to share their analyses.

PowerPivot in the Lab

I tested PowerPivot with the beta release of Excel 2010 on a virtual machine running the 64-bit version of Windows 7. Both the PowerPivot plug-in and Excel 2010 are available in 64-bit vesions, but I stuck with the 32-bit edition of Excel and the plug-in for my tests. Development versions of Office 2010 and PowerPivot are available for download at microsoft.com/office/2010 and at powerpivot.com, respectively. If you intend to install only Excel 2010—as opposed to the complete Office 2010 suite—take care to install “Office Shared Features” alongside Excel, as PowerPivot won’t install properly without it.

For my tests, I used sample data in the form of Access databases and Excel worksheets from the Codeplex project at powerpivotsampledata.codeplex.com. I also tested with campaign finance data that I downloaded from the Center for Responsive Politics’ OpenSecrets.org project into a MySQL database.

Installing PowerPivot tacked a new “Ribbon” tab in Excel for the add-in. From that tab, I could launch into a separate window for working with PowerPivot, with its own controls for importing and manipulating data. The regular Excel spreadsheet environment doesn’t go anyhere. PowerPivot data lives within an Excel workbook, with sheets that can house PivotCharts and Tables that draw on the PowerPivot data, as well as feed that data through linked tables. 

PowerPivot sports a handy wizard for piping in external data from databases, flat files or data feeds. I hooked my test worksheet up to an Access database by browsing to the right .ACCDB file, and then choosing either to select from a list of the tables and views in the database or to select them through an SQL query. With the list-based selection route, I could pick out tables to include, as well as filter which records to input.

When I pursued the same import path to my MySQL-stored campaign finance data (through an ODBC driver that I had already installed and configured in Windows), the process was similarly point and click—up until graphical table selection and filtering tool, where I hit error messages. Through the SQL query route, I could set up the same sorts of conditions as with the more wizardly option, just not as easily.

After importing one set of data into my PowerPivot workbook, each table appeared in a tab at the bottom of the interface, like sheets in a workbook. I could add new tables or views from different sources, remove any of the tables I’d brought in, and create relationships between the tables by clicking on one of the table columns and indicating in a dialog window the table and column to place on the other end of the relationship.

Following a tutorial included in the PowerPivot help file, I linked up a set of tables detailing a fictional company’s bicycle sales to tables of industrywide bicycle sales data. I could very quickly visualize the information using PivotCharts and Slicers, both of which make it fairly easy to wander through data looking for insights.