Reference no: EM133125086

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