Self-service business intelligence: introducing the PowerPivot

You may have some knowledge of PivotTables – whether you have used the function or not. What you probably won’t have heard of, however, is the PowerPivot function.

PowerPivot is an add-on that was introduced in Excel 2010 that essentially extends the capabilities of the standard PivotTable function and allows you to take your data further. Some of the features previously only available as part of PowerPivot have now been incorporated into Excel 2013 itself.

PowerPivot uses PivotTables, combined with some greatly enhanced data tools to deliver what Microsoft has described as ‘self-service business intelligence’.

So what are the benefits of using PowerPivots?

Data

One of the most significant aspects of PowerPivot is the inclusion of tools for working with data that bring some powerful database technology into Excel. PowerPivot is designed to work with much larger volumes of data than Excel itself and extends the range of external sources to which Excel will link and enables the creation of relationships between different sets of data.

DAX

Another important feature of PowerPivot is the inclusion of an entire new ‘family’ of functions known as Data Analysis eXpressions or DAX. These expressions are intended for manipulating the data within the Data Model or directly within a PivotTable based on the PowerPivot data. Although some DAX functions are identical to the standard Excel equivalents, there are many functions that combine spreadsheet and database functionality to allow PowerPivot to extract and manipulate data in a range of ways that Excel on its own would be incapable of.

KPIs

An important aspect of Business Intelligence is often to assess actual performance against Key Performance Indicators (KPIs). PowerPivot uses an advanced application of Excel’s Conditional Formatting feature to allow KPIs to be built into PivotTables.

How to install PowerPivot

As mentioned in the introduction, the PowerPivot Add-in is only available if you have the Professional Plus edition of Office 2013. To enable the Add-in, from the File ribbon tab go to Options, then Add-Ins:

From the Manage dropdown select COM Add-ins and click the Go button and select the Microsoft Office PowerPivot for Excel 2013 Add-in:

Installing the PowerPivot Add-in will add a PowerPivot ribbon tab in Excel:

Importing data

Now you have PowerPivot installed it is time to start importing data. The beauty of PowerPivot is that it lets you import data from multiple sources and combine them in Excel using PivotTables. The most common places to import data from would be a database tool such as SQL or Access. Select where you want to import the data from in the Home tab of PowerPivots.

In the Filtered course we use an Access database so we will follow this example. You will need an Access database stored on your computer or network to practice this. In this case we have downloaded the sample database from Microsoft called Northwind. We can then browse and select that database. Be sure to test the connection before proceeding:

When we click Next, we will have the choice of selecting from a list of tables or views (queries) or writing our own query as an SQL statement. We’ll stick with selecting from a list. If you are familiar with SQL statements you can write your own to pull data through.

We can then start selecting which tables we want to show in our PowerPivot. You can use the Select Related Tables button to see which tables have relationships set up within the existing database. These relationships are crucial if you want to pull information into one PivotTable once you have your data. You can use the Preview and Filter button to examine the data in each table.

The finish button will then start importing the data from your database into your PowerPivot window:

 

Now that you have your data you are ready to start using it.

Find out more about the PowerPivot function, in the module below:

Section 5 ‘Data Analysis’ > Unit 6 ‘PowerPivot: Handling Big Data’ > Module 2 ‘PowerPivot Data Tools’

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles