Analyze & Visualize SQL Server Data w/ Power Pivot & Power View in Excel

Last updated 05-13-2017

In this tutorial we’ll use Microsoft Excel to retrieve data from our SQL Server database. We’ll then model, analyze and visualize the data using Excel’s Power Pivot and Power View features.

Activate the Power Pivot & Power View add-ins

•          Open MS Excel 2013\2016
•          Go to File > Options > Add-Ins
•          In the Manage box, click COM Add-ins> Go
•          Check the Microsoft Office Power Pivot & Power View boxes

Excel 2013

Excel 2016

 

Power Pivot tab

• Click the Power Pivot tab
  • This is the tab where you work with Power Pivot PivotTables, calculated fields, and key performance indicators (KPIs), and creating linked tables

• Click Manage to open the Power Pivot window

 

Import Data from SQL Server

• In the Power Pivot window, click Get External Data > From Database > From SQL Server
• In Connect to Microsoft SQL Server Database, in Server or File Name, enter the name of SQL Server instance where your Data Warehouse is located.
•  Click the down arrow to the right of the Database name list, and select an Analysis Services database from the list
•  Click Test Connection to verify that the server is available.

• Click Next
• On the Choose How to Import the Data screen click the radial button for Select from a list of tables and views to choose the data to import
• Click Next
•  Select the tables and views that you want included.
  • You can change any Source Table name to Friendly Name
  • You can click Preview and FIlter to exclude columns and data
• Click Finish to complete import process

Completed Data Import (below)

 

Diagram View – Table Relationships

 

Save the Model Project

It is important to frequently save your model project.

To save the model project

• In SQL Server Data Tools, click on the File menu, and then click Save All.

 

Review Existing Relationships and Add New Relationships

A relationship is a connection between two tables that establishes how the data in those tables should be correlated. For example, the Product table and the Product Subcategory table have a relationship based on the fact that each product belongs to a subcategory.

When you imported data by using the Table Import Wizard, you imported seven tables from the AdventureWorksDW database. Generally, if you import data from a relational source, existing relationships are automatically imported together with the data. However, before you proceed with authoring your model you should verify those relationships between tables were created properly. For this tutorial, you will also add three new relationships.

To add new relationships between tables

• In the model designer, in the Provider table, click and hold on the ProviderID column, then drag the cursor to the Claim column in the Provider table, and then release.
  • A solid line appears showing you have created an active relationship between the ProviderID column in the Provider table and the ProviderID column in the Claim table.
• In the Patient table, click and hold on the RecipientID column, then drag the cursor to the RecipientID column in the Claim table, and then release.
  • A dotted line appears showing you have created an inactive relationship between the RecipientID column in the Patient table and the RecipientID column in the Claim table. You can have multiple relationships between tables, but only one relationship can be active at a time.

To review existing relationships

• Using the top Ribbon, click Home, under the View section click Diagram View.
  • The model designer now appears in Diagram View, a graphical format displaying all of the tables you imported with lines between them. The lines between tables indicate the relationships that were automatically created when you imported the data.
  • Use the minimap controls in the lower-right corner of the model designer to adjust the view to include as many of the tables as possible. You can also click and drag tables to different locations, bringing tables closer together, or putting them in a particular order. Moving tables does not affect the relationships already between the tables. To view all of the columns in a particular table, click and drag on a table edge to expand or make it smaller.

 

Create a Calculated Column

A calculated column is a column created by a dax formula that creates a value by calculating data that already exists in the model.

Create a calculated column in the Provider table

• sing the top Ribbon, click Home, under the View section click Data View.
  • Calculated columns can only be created by using the model designer in Data View.
• In the model designer, click the Provider table (tab).
• Right-click the Add Column column header, and then click Insert Column.

• A new column named Calculated Column 1 is inserted to the right of the ZipCode column.
• In the formula bar above the table, type the following formula. AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available.
  • =[Title]&” “&[FirstName]&” “&[LastName]
  • When you have finished building the formula, press ENTER.
  • Values are then populated for all the rows in the calculated column. If you scroll down through the table, you will see that rows can have different values for this column, based on the data that is in each row.
• Rename this column to FullName.

 

Create a Measure

Similar to the calculated columns you created in the previous lesson, a measure is essentially a calculation created using a DAX formula. However, unlike calculated columns, measures are evaluated based on a user selected filter; for example, a particular column or slicer added to the Row Labels field in a PivotTable. A value for each cell in the filter is then calculated by the applied measure. Measures are powerful, flexible calculations that you will want to include in almost all tabular models, to perform dynamic calculations on numerical data.

To create a Daily Average Number of Claims measure in the Claim table

• Click the Claim table(tab).
• In the measure grid (lower pane under the dark grey line), click the top-left empty cell.
• In the formula bar, above the table, type the following formula:
  • Total Claims:=DISTINCTCOUNT([DCN])
  • When you have finished building the formula, press ENTER.
• Create a second measure in the below cell, type the following formula:
  • Daily Avg Claims:=[Total Claims]/DISTINCTCOUNT([AdjudicatedDt])
  • When you have finished building the formula, press ENTER.

• You can format the numeric measure value by the right clicking the cell and selecting Format.

• Once the Formatting Window opens you can then specify how you want to display the numeric value. For our example we utilize the Decimal Number format and then specify the number of decimal places.

• Click Ok.

 

Create Key Performance Indicators

Key Performance Indicators (KPIs) are used to gauge performance of a value, defined by a Base measure, against a Target value, also defined by a measure or by an absolute value. In reporting client applications, KPIs can provide business professionals a quick and easy way to understand a summary of business success or to identify trends.

To create a Daily Average Claim Performance KPI

• In the model designer, click the Claim table (tab).
• In the measure grid, right-click the Daily Avg Claims measure, and then click Create KPI.
  • The Key Performance Indicator dialog box opens.
• In the Key Performance Indicator (KPI) dialog box, in Target, select the Absolute Value option.
• In the Absolute Value field, type 15, and then press ENTER.
• In the left (low) slider field, type 6, and then in the right (high) slider field, type 12.
• In Select Icon Style, select the first (red), (yellow), (green) icon type.
• Click OK to complete the KPI.
  • In the measure grid, notice the icon next to the Internet Current Quarter Sales Performance measure. This icon indicates that this measure serves as a Base value for a KPI.

 

Create a Hierarchy

Hierarchies are metadata that define relationships between two or more columns in a table, thus defining the relative position of column to another column.

To create a Category hierarchy in the Patient table

• Using the top Ribbon, click Home, under the View section click Diagram View.
• Right-click the Patient table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.
• In the hierarchy name, rename the hierarchy by typing PatientLocation, and then press ENTER.
• In the Patient table, right click the State column, in the pop up box go to Add to Hierarchy and then click Locations.
• In the Patient table, click the City column, then drag it to the Locations hierarchy, releasing it below State.
• In the Patient table, click the ZipCode column, then drag it to the Locations hierarchy, releasing it below City.
• In the Patient table, click the Address column, then drag it to the Locations hierarchy, releasing it below ZipCode.
• In the Locations hierarchy, right-click the State column, then click Rename, and then type StateProvince.
• Note: Ensure that the columns in the hierarchy are in logical order. Typically the column with the least uniqueness (based on business) should be near the top of the list and the column with the highest uniqueness (based on the business) should be near the bottom of the list.

Create a PivotTable

• While in Power Pivot click the PivotTable button
• On the Create PivotTable screen select New Worksheet
  • A new PivotTable Worksheet is created
• Under PivotTable Fields do the following
  • Go to Provider. Select FullName.
  • Go to Patient. Select PatientLocation.
  • Go to Claim > Daily Avg Claims, Select Value and Status.
• Give it a title
• Complete. You’ve created a PivotTable

Note: Use the Filters pane (in PivotTable Fields pane) to only display specific data that you want shown in yourPivotTable.

Create a Power View Workbook

• Navigate back to your excel workbook to display your previously created PivotTable
• Using the top Ribbon, go to Insert and then click the Power View button
• Excel creates a Power View Sheet with your data model in the Field List

 

1. In the Power View Fields list do the following
   1. Go to Patient. Select FullName.
   2. Go to Claim > Daily Avg Claims, Select Value and Status.
2. Go to Design, (In the Switch Visualizations section) go to Other Chart and Select Pie.
3. Go to Layout, (In the Labels section) go to Title and Select None.
4. Drag the edges of the Chart so that it spaced across the top half of the pane.
5. Right Click the white space within the chart and click Copy. Then Right Click the white space below the chart and select Paste.
6. Go to Design, (In the Switch Visualizations section) go to Column Chart and Select Clustured Column.

Filter the data

• In the Power View Fields Pane, Go to Claim and find Year
• Drag Year to the Filters pane
  • Year will now display in the Filters pane
• Click on the Advanced Filter Mode button next to Year in the Filter pane
• Select radial box with the most current year
• The Power View displays the charts for the most current year
• Click where it says, “Click here to add a title” and give it a title.
• Complete, you’ve created a Power View report.

Note: Interactivity: By clicking on any part of either chart, the visualization will interact and react to your selection by highlighting/emphasizing your specific

Note: Interactivity: Direct your mouse/pointer to any portion of your chart – Power View will display additional data about that specific section

Note: You can change or add to the view by making additional selection from the Power View Fields pane

 

END

You’ve are created a Power Pivot data model and visualized it Power View – all within MS Excel.

Note: The database used in this demo is based on the test healthcare data set made public by I-HFS.