Big data tools in Excel: PowerPivot
The ability to crunch large datasets and squeeze meaningful decision support data out of it is in demand. Microsoft, in Excel 2010, included the ability to install a free add-in called PowerPivot. PowerPivot provided the sought-after ability to analyze multiple-related datasets in the same Pivot Table. For example, say you had payroll data in one data source, perhaps a SQL database, employee demographics in an exported comma-separated values file and benefit election information in an Excel workbook.
The one field they had in common was the employee ID. Without some serious copying and pasting, macros or additional query software, there was no easy way to do it, until PowerPivot. (In Excel 2013, this capability became part of the native software with its ability to link different tables.) Microsoft defines PowerPivot as, “an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook create relationships between heterogeneous data.” These relationships enable you to analyze the collection of data sources as one with pivot tables, pivot charts and calculations.
To install the add-in, click File, Options. Then choose the Add-ins category. At the bottom, in the Manage field, choose COM Add-ins. You’ll see PowerPivot for Excel listed. If you don’t, go to PowerPivot.com and download the version for 2010. There is also a PowerPivot for 2013. Make sure you get the right one.
Click the new PowerPivot tab, and on the left, click the PowerPivot Window button. Using the buttons in the Get External Data group, bring in your data. If you are accessing an external database, PowerPivot will give you the option of choosing which tables to bring in. You are not limited by the row capacity of the regular Excel workbook (1,048,576). In fact, you can bring in millions of rows.
While PowerPivot will connect related tables from a database, it will not necessarily connect them between data sources. For example, in our illustration, the Store workbook is a separate data source from the database tables we imported. So, you need to create a relationship. On the Design tab, PowerPivot Window, click the Create Relationships button in the Relationships group. In a “many to one” relationship, you’ll want to identify the many data source first, then select the field they have in common. Even if the field is named differently, if the data are the same, it will work.
Back on the Home tab in the PivotTable Window, near the middle, you’ll see a PivotTable button. Click it, and now you’ll see your pivot table fields on the right. What’s different is that you will be able to select fields from all of the data sources you imported to create your reports.