{"id":281,"date":"2013-04-01T19:57:25","date_gmt":"2013-04-01T19:57:25","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=281"},"modified":"2014-03-06T20:30:08","modified_gmt":"2014-03-06T20:30:08","slug":"building-key-performance-indicators-kpis-with-powerpivot","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/building-key-performance-indicators-kpis-with-powerpivot\/","title":{"rendered":"Building Key Performance Indicators (KPIs) with PowerPivot"},"content":{"rendered":"

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.<\/p>\n

In this post, I’ll use AdventureWorksDW2012 sample data so you can follow along with me. The database can be downloaded here<\/a>.<\/p>\n

Let’s get started.<\/p>\n

Enabling PowerPivot in Excel 2013<\/h3>\n

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:<\/p>\n

\"SQL<\/a><\/p>\n

Importing Data<\/h3>\n

Open Excel, click the PowerPivot tab, Manage:<\/p>\n

\"SQL<\/a>
\nUpon clicking Manage, a new window should appear. From this window, you will import data. Click From Database and select From SQL Server:<\/p>\n

\"SQL<\/a>
\nType in the Server Name, Authentication mode, and browse to the AdventureWorksDW2012 database:
\n\"SQL<\/a>Click 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:<\/p>\n

\"SQL<\/a>
\nAfter clicking Finish, the import will begin. Once the import finishes successfully you should be able to view all the tables separated into sheets:<\/p>\n

\"SQL<\/a><\/p>\n

Creating PivotTable<\/h3>\n

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:<\/p>\n

\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,65],"tags":[35,106,59,66,55,110],"class_list":["post-281","post","type-post","status-publish","format-standard","hentry","category-business-intelligence","category-powerpivot","tag-bi","tag-business-intelligence","tag-development","tag-excel","tag-kpi","tag-powerpivot"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=281"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/281\/revisions"}],"predecessor-version":[{"id":289,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/281\/revisions\/289"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}