Building Key Performance Indicators (KPIs) with PowerPivot

A KPI (Key Performance Indicator) is a graphical representation that displays progress against a predefined measure or business goal. KPIs make it easier for end users to evaluate the amount of progress without reading a bunch of data.

In this post, I’ll use AdventureWorksDW2012 sample data so you can follow along with me. The database can be downloaded here.

Let’s get started.

Enabling PowerPivot in Excel 2013

To enable PowerPivot, open Excel, go to File, Options, Add-Ins, select COM Add-ins and click Go. This will open up the COM Add-Ins dialog box. Click “Microsoft Office PowerPivot for Excel 2013” and hit OK. After successfully enabling PowerPivot, the tab should appear at the top of the Excel spreadsheet:

SQL Freelancer SQL Server PowerPivot KPI

Importing Data

Open Excel, click the PowerPivot tab, Manage:

SQL Freelancer SQL Server PowerPivot KPI
Upon clicking Manage, a new window should appear. From this window, you will import data. Click From Database and select From SQL Server:

SQL Freelancer SQL Server PowerPivot KPI
Type in the Server Name, Authentication mode, and browse to the AdventureWorksDW2012 database:
SQL Freelancer SQL Server PowerPivot KPIClick Next, choose “Select from a list of tables and views to choose the data to import” and click Next. The next screen is where we will select our data to import. For this example, choose FactInternetSales and click “Select Related Tables”. The Select Related Tables button enables you to automatically select every table that is related to the source table selected:

SQL Freelancer SQL Server PowerPivot KPI
After clicking Finish, the import will begin. Once the import finishes successfully you should be able to view all the tables separated into sheets:

SQL Freelancer SQL Server PowerPivot KPI

Creating PivotTable

Before creating a KPI we will need to slice and dice our data into a PivotTable. To do this, click PivotTable on the ribbon bar and choose New Worksheet:

SQL Freelancer SQL Server PowerPivot KPI
Click here to view the rest of this post.

PowerPivot with Excel 2013

Excel 2013 changes things up a bit when it comes to installing PowerPivot. In previous versions you had to download the component and install, but with Excel 2013 it comes installed as an add-in, but disabled by default. To enable PowerPivot, open Excel, go to File, Options, Add-Ins, select COM Add-ins and click Go.

SQL Freelancer SQL Server Excel PowerPivot
This will open up the COM Add-Ins dialog box. Click “Microsoft Office PowerPivot for Excel 2013” and hit OK.

SQL Freelancer SQL Server Excel PowerPivot
After successfully enabling PowerPivot, the tab should appear at the top of the Excel spreadsheet.

SQL Freelancer SQL Server Excel PowerPivot

Creating a dashboard

There are a few different ways in which to import data into Excel to use with PowerPivot. Some of these ways include:

  • From database

SQL Freelancer SQL Server Excel PowerPivot

  • From Data Service

SQL Freelancer SQL Server Excel PowerPivot

  • From other sources such as Oracle, Excel, flat files, etc.

For this example, and simplicity sake, I will just run a query and simply copy and paste my results into the Excel spreadsheet. The query results look like this:

SQL Freelancer SQL Server Excel PowerPivot
Once the results are copied and pasted into Excel, click the PowerPivot tab and click Add to Data Model:

SQL Freelancer SQL Server Excel PowerPivot
On the create table dialog box, make sure you select the range for your data and click “My table has headers”

SQL Freelancer SQL Server Excel PowerPivot
After clicking OK, the PowerPivot window should appear. To start creating the dashboard, click PivotTable, PivotChart, then select New Worksheet:

SQL Freelancer SQL Server Excel PowerPivot
Click here to view the rest of this post.