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.

Creating a SharePoint Key Performance Indicator (KPI)

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.

We will use the sample data (see below) I used in the previous post to create our KPI (this data is static since we imported it into Sharepoint as a separate list, so it will not report real time data)

Let’s say, for example, our DBA Manager wants to upgrade all the SQL Servers to at least SQL Server 2008 R2 and wants to know how many SQL Servers are not compliant. This is a good scenario for using a KPI to display this data.

SQL Freelancer SQL Server Sharepoint KPI


Creating the KPI

In Sharepoint, browse to the page where you want the KPI displayed and go to Site Actions > Create:

SQL Freelancer SQL Server Sharepoint KPI
Custom Lists > KPI List:

SQL Freelancer SQL Server Sharepoint KPI
For this example, I’ll create a list called SQL Versions KPI:
SQL Freelancer SQL Server Sharepoint KPI
Once, the KPI list is created you should see a blank list. To add a KPI, click New > Indicator using data in Sharepoint list (you can also use other data sources for KPI’s):

SQL Freelancer SQL Server Sharepoint KPI
First, give the KPI a name:

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