Excel Power Pivot Exercise Excel's PowerPivot builds upon what you have learned from Pivot tables and entity relationship diagrams as you pull fields from multiple linked tables. PowerPivot allows you to link to and pull data in from multiple sources (text files, databases, data warehouses, other Excel files, etc.). However, for this exercise we are just going to use multiple worksheets stored in one workbook. Tutorials There are many excellent tutorials on the Internet. I have created one associated with the exercise, but we only scratch the surface of the use of Power Pivots. Bill Jelen of "Excel is Fun" has written at least two books and has created over 250 excellent Excel video tutorials. His YouTube channel can be found at https://www.youtube.com/user/ExcelIsFun This is an excellent resource. Mr. Jelen has created a PowerPivot playlist. At the beginning of his first video, he lists several additional resources that you might want to visit as well. Tutorials The tutorial video that I created centers on a doctor's office and the data that would be collected in that situation. I have included the data file that I used for the tutorial video on the assignment description in case you want to work through the video as you watch the tutorial. (You will not be turning in the physician office work. The purpose of this file is to help you practice.) The instructional video explains Power Pivots, but when we get to the pivot table oriented work, you will be on your own since you should have already worked on the Excel Pivot Table assignment. Power Pivot allows you to essentially take multiple worksheets and connect them on related fields to then perform pivot tables analysis on the related data. To use Excel's Power Pivot feature, you have to install an Add-In. If you cannot (or do not want to) install the Excel PowerPivot Add-in, you can remote into a computer lab machine and use Excel on the lab machine. The instructions for remoting in are linked on the assignment. The file that I used in the tutorial video is a small example of data that you might find at a physician's office for maintaining electronic health records (EHR). The workbook contains the following data worksheets: 1. Patient: Names and addresses of patients 2. Services: A list of the services provided at the office. 3. Physicians: A list of the physicians who work at the clinic. 4. Specialty: A list of the specialty areas associated with the physicians in the office. 5. ZipCounty: A list of zipcodes, their associated cities and the counties in which the towns are cities. (This might be used for supplemental funding reports to lower socioeconomic areas.) 6. And a fact table Office Visit that links the physicians and services to a patient. Homework Data File The data file that we will be using for the homework assignment is very similar to the tutorial video file except the scenario has changed. The data file would be used by the repair department in a car dealership to keep track of customers and the services that they have performed on their vehicles. The workbook contains the following data worksheets: 1. Customer: Names and addresses of customers 2. Service: A list of the services provided at the office. 3. Technician: A list of the technicians who work at the business.

4. Specialty: A list of the vehicle brands that the technicians specialize in repairing. 5. ZipCounty: A list of zipcodes, their associated cities and the counties in which the towns are cities. (This might be used for determining the location of a branch location.) 6. And a fact table Shop Visit that links the technicians and services to a customer. Instructions Work through the video tutorial if you are not already familiar with Excel PowerPivot and then: 1. Make 8 Power Pivot Tables stored on two worksheets. Name the sheets "Results1" and "Results 2." a. Make at least three calculations. b. Make two different splicers. c. Write one sentence for each Power Pivot table explaining what it displays. (You can type the sentence beneath each table.) 2. Upload your Excel workbook file to the dropbox