Assignment Detail:- Scenario
Lastly, Lucy wants to have more information about the customer demographics in addition to the already available age and gender- Since your data has Customer ID for each row, you can "connect" these rows with your customer demographics database- Your customer demographics is stored in an Access database Workaround: Alternatively, the customer demographics is also provided as a txt file here, in case you could not work with Access database-
Important: If you are using Excel 2010, please download and install the Power Pivot- You need Power Pivot in order to perform this lab- Remember to download the version according to your Excel -32 or 64 bit--
What You'll Need
• A computer with Microsoft Excel installed on it- We prefer Excel 2016, but you can still complete the lab using Excel 2013 or Excel 2010-• File Lab8Start v5-xlsx- This Excel file contains two charts that, depending on your version of Excel, that you may not be able to view- The Composition worksheet contains a Treemap and Sunburst chart that will not be read by Excel 2010 and 2013 users- This will not affect your ability to complete this lab-• File Demographics-mdb- Alternatively, Mac users can attempt importing the data from Customer_demographics-txt text file since there is no Access import function in Mac-
Lab 8
Download the following files:
• File Lab8Start v5-xlsx- This Excel file contains two charts that, depending on your version of Excel, that you may not be able to view- The Composition worksheet contains a Treemap and Sunburst chart that will not be read by Excel 2010 and 2013 users- This will not affect your ability to complete this lab-• File Demographics-mdb- Alternatively, Mac users can attempt importing the data from Customer_demographics-txt text file since there is no Access import function in Mac-Let's bring the customer database into your Excel-
NOTE: This Excel file contains two charts that, depending on your version of Excel, that you may not be able to view- The Composition worksheet contains a Treemap and Sunburst chart that will not be read by Excel 2010 and 2013 users- This will not affect your ability to complete this lab----Excel 2010, 2013 and 2016 Users---
1- From a new worksheet, go to the Data tab and on the Get External Data group, select the From Access option- This will bring up the Select Data Source dialog box-
2- Navigate to where you saved the Demographics-mdb file, select it and click on the Open button-
3- From the Import Data dialog box, ensure that Table is selected for how you want to view the data, within the existing worksheet -or select New Worksheet if you didn't start from a new one-, then click on the OK button-
4- There is only one table in this database so the import is rather simple- Rename the worksheet Demographic Data- Your import should result in 18,484 rows -plus the header- and seven columns of data----Excel 2013 and 2016 Users--->>If you are using Excel 2010, skip the next two steps and go to the workaround below<<
5- Create a new pivot table from the SalesTable then click More tables- Remember to select Yes to Excel's prompt to create a new pivot table-
6- Now, add the MaritalStatus and TotalChildren fields from the Customer_demographic table as Rows and Order Quantity from the SalesTable as Values- In addition, add the Product Category as filters- HINT: For Excel 2016, let Excel Auto-Detect Relationships- For Excel 2013, link the Customer ID from SalesTable as Foreign key to the Customer ID from the table containing customer demographics as Primary key-You are now ready to answer the questions----Excel 2010 Users---
Workaround for Excel 2010 users-
1- Click anywhere on the newly imported data, click the Power Pivot tab, and click Create Linked Table- This will open a new window called the Power Pivot window and will add the newly imported data to the Data Model - this may take several minutes-
2- Go to the Data worksheet -the SalesTable-, click the Power Pivot tab and click Create Linked Table- This will open a new window called the Power Pivot window and will add the SalesTable data to the Data Model - this may take several minutes-
3- From the Power Pivot window, click the Home tab, click PivotTable and then click PivotTable- Select New Worksheet and click OK-
4- Now, add the MaritalStatus and TotalChildren fields from the Customer_demographic table as Rows and Order Quantity from the SalesTable as Values- In addition, add the Product Category as filters- HINT: click Create and let Excel detects and creates the relationship-You are now ready to answer the questions-Now, remove the MaritalStatus and TotalChildren fields from the Rows and replace them with the YearlyIncome field from the Customer_demographic table-
Now, remove the YearlyIncome field from the Rows and replace it with the EnglishEducation field from the Customer_demographic table-Lastly, remove the EnglishEducation field from the Rows and replace it with the HouseOwnerFlag field from the Customer_demographics table- Format the Sum of Order Quantity to show as Percentage of Grand Total with two decimal places-
Attachment:- big data analytics-rar
Most Recent Questions