ITM310 - Problem Solving #7 complete solutions correct answers key

ITM310 - Problem Solving #7 complete solutions correct answers key

Working with Multiple DataSets in Excel’s PowerPivot Feature - 25 pts

What is PowerPivot? When working with a single Excel spreadsheet, you are limited in what you can do with the data. We found PivotTables were a better option, as we could view data in several dimensions. The next step in data analysis is a powerful tool available in Excel called PowerPivot. PowerPivot lets you import multiple datasets from various sources (such as separate spreadsheets or query results from a database), and then view all of the imported files as a set of “data tables.” The imported tables may include formulas and expressions that allow more sophisticated data calculations and analysis.

This assignment is to be completed individually. Note that PowerPivot is included in Excel Professional 2013, including the Office 365 available for students through OIT (see Course Resources). It is not included in student versions of Office, or Mac Office versions of Excel, but is coming in Mac Office 2016. PowerPivot is available for Excel 2010 Professional as a download from Microsoft. All instructions below are written for Excel 2013 & menu items vary slightly for 2010. See supplemental software document and menu graphics for differences.

There are two steps to this activity:

         1. Set up your Excel PowerPivot working file - give yourself an extra half hour to go through the PowerPivot setup before you begin your problem tasks.

         2. Complete the tasks on the last page of this document in Step 9.

Before you Begin: See Excel podcast ITM310_Intro_to_PowerPivot.mp4), and review notes. Download the zip file which contains three Excel data files (ProductData.xlsx, SalesData.xlsx, and TerritoryData.xlsx), plus the Installing PowerPivot in Excel Office 20xx.doc for either Office 2013 or Office 2010. Have these files available on your computer desktop, in your homework file, or USB drive before you begin the PowerPivot setup.

Instructions:  Follow the instructions below carefully to set up a new PowerPivot working model by importing the three separate Excel worksheets and establishing a relationship between them. When finished with your PowerPivot setup, complete the two problem tasks located on the last page of this document. We will use PowerPivot again later in the semester for your final semester BI project so this activity is great practice!

Note: If you begin your setup in Excel 2013, you cannot then work with the file in Excel 2010, and vice versa. For best results, complete your setup and homework problems on the same computer, and save your working file in case you need to come back to it.

Step 1: Open a new Excel file & check to see if PowerPivot is installed on your copy of Excel. If it is not shown in the menu as below, install the PowerPivot function in Excel Professional 2013 or 2010, using the appropriate file included with this activity.

You can instantly tell if PowerPivot is already installed in Excel. Open a new worksheet, and look for a top Menu item that says “PowerPivot.” If it is not there, install it using the “Installing PowerPivot” file from Blackboard noted above for your version of Excel and continue to Step 2.

Excel PowerPivot is built in to Excel 2013 Pro, and a download into Excel 2010.

Step 2.  Create PowerPivot Data Model
 

Note: PowerPivot opens in a second worksheet window when you click the Manage icon. Both the PowerPivot Data Model and the regular Excel windows will be open while you work.

a.     Open a new Excel file open, click on the POWERPIVOT tab (1) in the top menu; a new menu appears. Double-click the green cube “Manage Data Model” (2) to open a PowerPivot window (two windows will be open, your original Excel worksheet, and the new PowerPivot window.

1

2

Step 3.  Import Data from External Files
 

From your PowerPivot window you can import data from many sources. We will import three tables from three Excel spreadsheets. Download these files from Blackboard if you have not already done so.

a.    Import Data from SalesData.xlsx Excel file

1.    

On the Ribbon to the right of the Manage icon, click on “From Other Sources.”

2.     Scroll to the bottom of the Table Import Wizard and choose “Excel File.” Press Next >

3.