Power Pivot and Power View Tutorial

With Power Pivot, Microsoft provides users with the ability to pull and explore data from a variety of data sources, all within a single PivotTable and Power View report. The information stored in Power Pivot is commonly referred to as a data model. By performing the following hands-on practice, you will explore adding tables from a SQL Server data source and an Excel file. You will also create relationships between tables in the Power Pivot data model and add a PivotTable based on your newly created data model. Finally, you will add a Power View report and create a table and bar chart in that report.

Prerequisites

• Microsoft Silverlight
• Excel 2013 with Power Pivot and Power View enabled
• SQL Server 2012 with AdventureWorksDW2012 database installed
  • Can be downloaded from https://github.com/microsoft/sql-server-samples/releases/download/adventureworks2012/adventure-works-2012-dw-data-file.mdf
• Excel file with Customer information
  • Demo file - https://accelebrate.s3.amazonaws.com/labfiles/powerpivot_files.zip

General Notes:

Due to heightened security restrictions in Excel, if you see a security warning with an Enable Content button, you must click this button before you can edit your Excel worksheets or save changes in Power Pivot.

I have seen intermittent problems where the POWER PIVOT ribbon disappears. In most instances, the only fix is to reboot the entire computer. There is a bug notice on Microsoft Connect for this, but unfortunately, it is impossible to reproduce. I have found no correlation to when or why it happens, although low system resources do seem to play a part in it.

Directions

1. Open Excel 2013.
2. On the New page, click Blank workbook to create a new workbook.
3. Switch to the POWER PIVOT ribbon, and then click Manage.
4. On the HOME ribbon, click the down arrow on Get External Data, click From Database, and then click From SQL Server.
5. Enter the name of your SQL Server instance in the Server name box.
   Note: A period “.” will work if your SQL Server is on your local computer.
6. Set the appropriate authentication information, select AdventureWorksDW2012 from the Database name drop-down list, and then click Next.
7. Verify that Select from a list of tables and views to choose the data to import is selected, and then click Next.
8. On the Select Tables and Views page, click the check box in the first column to select the DimDate and FactInternetSales source tables.
9. Change the Friendly names to Date and InternetSales respectively.
10. Click Finish.
11. Once the import has completed, click Close.
12. You should now see two tabs in the Power Pivot window. Click the Save icon in the Quick Launch area at the top of the window. Name the file InternetSales and place it in a location where you can find it again if necessary.
    Note: This will switch you back to Excel to save the file.
13. You can use Alt-Tab, your mouse, or the Manage button on the Power Pivot ribbon to return to your data model in Power Pivot.
14. On the Home ribbon, click Get External Data, and then click From Other Sources.
15. On the Connect to a Data Source page of the Table Import Wizard, click Excel File near the bottom of the list under Text Files, and then click Next.
16. Click the Browse button, and locate and click the CustomerInfo.xls file that accompanies this demo, and then click Open.
    Note: You will need to change the file type drop-down list to All files (*.*) or Microsoft Office Access 97-2003 Database (*xls, *.xlt).
17. On the Connect to a Microsoft Excel File page, select the Use first row as column headers option, and then click Next.
18. Select the check boxes for the DimCustomer and DimGeography source tables.
19. Change the Friendly Name column to remove the “Dim” from the name.
20. Highlight the DimGeography row, and then click the Preview & Filter button.
21. Remove the checks from the boxes at the top of the FrenchCountryRegionName and SpanishCountryRegionName columns, and then click OK.
22. Click Finish.
23. When the import has finished, click Close.
24. You should now have 4 tabs across the bottom of the page. Each tab represents a table in your data model.
25. Click Diagram View in the View section to the far right side of the HOME ribbon.
26. When the diagram view appears, drag the CustomerKey field in the InternetSales table to the CustomerKey field in the Customer table.
    Note: This creates a relationship between these two tables so that you can build formulas across all of the tables.
27. Repeat this process to create a relationship between the GeographyKey in the Customer table and the GeographyKey in the Geography table.
    Note: It does not matter which direction you make the connection. Power Pivot looks at the data to determine which side is the one side and which side is the many side and creates the relationship accordingly.
28. Click the PivotTable button on the HOME ribbon, and then click PivotTable.
29. In the Insert Pivot dialog box, click Existing Worksheet, verify that the location is set to ‘Sheet1’!$A$1, and then click OK.
30. In the PivotTable Fields list, expand InternetSales, and then select the box next to SalesAmount. Notice that Sum of SalesAmount is added to the VALUES area, and also to the PivotTable.
31. Return to the PivotTable Fields list, and expand the Geography table.
32. Drag the EnglishCountryRegionName field to the ROWS area at the bottom of the list.
33. Drag the StateProvinceName field to below the EnglishCountryRegionName.
34. Return to the PivotTable Fields list, and expand the Customer table.
35. Drag the MaritalStatus field to the COLUMNS area and review your PivotTable.
36. Save your workbook.
37. Change to the INSERT ribbon.
38. Click Power View in the Reports section near the center of the ribbon.
39. A new worksheet named Power View1 appears and the POWER VIEW ribbon should be active.
40. From the Power View Fields area on the right side of the screen, expand InternetSales, and then drag the SalesAmount field and drop it on the left side of the canvas under the Click here to add a title heading.
41. In the Power View Fields list, expand Geography, and click the box to select the EnglishCountryRegionName field.
42. If necessary, click and drag on one of the corners to resize the table to show all of the displayed information.
43. If the EnglishCountryRegionName field appears to the right of the SalesAmount field, in the FIELDS area at the bottom of the Power View Fields list, drag the SalesAmount field to below the EnglishCountryRegionName field.
44. In the Power View Fields list, expand Customer and drag the MaritalStatus field to the middle of the canvas, far enough away from the existing table so that it creates a new table, rather than modifying the existing table.
45. Add the SalesAmount field to this new table, below the MaritalStatus field.
46. Verify that the new table is selected.
47. On the DESIGN ribbon, in the Switch Visualization section, click Bar Chart, and then click Stacked Bar. Resize the chart to show all the information if needed.
48. Click on the bar for “M”. Notice that the SalesAmount column in the first table changes to show the totals for people who are married.
49. Click down under the bars near the word “(Millions)” to clear the slicer and show the total sales again.
50. Save and close your Excel workbook.

Author: Ann Weber, one of Accelebrate's instructors