In-Depth PowerPivot Training
For 2 days of in-depth PowerPivot training, please consider requesting Accelebrate's Introduction to PivotTable and PowerPivot for Excel 2010 course for you and your staff. Please contact us to request a quote.

PowerPivot and Power View Hands-On Practice

With PowerPivot, 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 PowerPivot 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 PowerPivot 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

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 PowerPivot.

I have seen intermittent problems where the POWERPIVOT 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 POWERPIVOT 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 PowerPivot 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 PowerPivot ribbon to return to your data model in PowerPivot.
  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. PowerPivot 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


In-Depth PowerPivot Training
For 2 days of in-depth PowerPivot training, please consider requesting Accelebrate's Introduction to PivotTable and PowerPivot for Excel 2010 course for you and your staff. Please contact us to request a quote.



Contact Us:

Accelebrate’s training classes are available for private groups of 3 or more people at your site or online anywhere worldwide.

Don't settle for a "one size fits all" public class! Have Accelebrate deliver exactly the training you want, privately at your site or online, for less than the cost of a public class.

For pricing and to learn more, please contact us.

Contact Us Train For Us

Toll-free in US/Canada:
877 849 1850
International:
+1 678 648 3113

Toll-free in US/Canada:
866 566 1228
International:
+1 404 420 2491

925B Peachtree Street, NE
PMB 378
Atlanta, GA 30309-3918
USA

Subscribe to our Newsletter:

Never miss the latest news and information from Accelebrate:

Microsoft Gold Partner

Please see our complete list of
Microsoft Official Courses

Recent Training Locations

Alabama

Huntsville

Montgomery

Birmingham

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

San Francisco

Oakland

San Jose

Orange County

Los Angeles

Sacramento

San Diego

Colorado

Denver

Boulder

Colorado Springs

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Miami

Jacksonville

Orlando

Saint Petersburg

Tampa

Georgia

Atlanta

Augusta

Savannah

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Ceder Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

Banton Rouge

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Hagerstown

Frederick

Massachusetts

Springfield

Boston

Cambridge

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Saint Paul

Minneapolis

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Reno

Las Vegas

New Jersey

Princeton

New Mexico

Albuquerque

New York

Buffalo

Albany

White Plains

New York City

North Carolina

Charlotte

Durham

Raleigh

Ohio

Canton

Akron

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Tulsa

Oklahoma City

Oregon

Portland

Pennsylvania

Pittsburgh

Philadelphia

Rhode Island

Providence

South Carolina

Columbia

Charleston

Spartanburg

Greenville

Tennessee

Memphis

Nashville

Knoxville

Texas

Dallas

El Paso

Houston

San Antonio

Austin

Utah

Salt Lake City

Virginia

Richmond

Alexandria

Arlington

Washington

Tacoma

Seattle

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Edmonton

Calgary

British Columbia

Vancouver

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan

© 2013-2019 Accelebrate, Inc. All Rights Reserved. All trademarks are owned by their respective owners.