## Nathan’s Office Hour for Assignment 4

Date/Time: October 24 (Tuesday), 3:30 pm – 4:30 pm

Location: Alter 240A (Breakout room)

## Excel Videos from Fox Video Vault

In case you’d like some resources to learn Excel and as help for Assignment #4, here are some links for video tutorials on text manipulation (e.g., CONCATENATION), VLOOKUP, and Pivot Tables. You will need to log in to watch the videos.

Text manipulation: https://www.fox.temple.edu/vault/video/Text-Manipulation/

VLOOKUP: https://www.fox.temple.edu/vault/video/lookup-tables/

Pivot Tables: https://www.fox.temple.edu/vault/video/pivot-tables/

Thanks to Grace Laggan who recommended these videos to me.

## Hint for Assignment #4 (ETL and Pivot Tables)

I got quite some questions related to Part 4, Question (b) of Assignment #4, which asks you to create a pivot table first, and then use the excel AVERAGE function in a separate cell outside the pivot table.

Here is an example of what I mean by “use the Excel AVERAGE function to average those values … in a separate cell ** outside** the Pivot Table.”

Link: Hint for HW4 – Part4Qb

Let me know if you have any questions.

-Jing

## Schedule Change for 10/23 (Monday)

I will be out of town on 10/23 (Monday). **We will not meet in class**. Instead, please review this slide deck: 9. Supplement – Basic Statistics on your own.

For 10/25 (Wednesday), we will meet as usual.

## Download the latest version of Office (Windows or Mac)

We will use Excel to create Pivot tables this week. Note that if you have an earlier Mac version, some of the options may not be available. If so, download the latest Mac version of Microsoft Office for free by following the instructions here: https://computerservices.temple.edu/office-365. It is free for Temple students.

The webpage also offers free download of Microsoft Office for Windows users.

## Assignment #4: ETL and Pivot Tables in Excel (due by Wednesday, 10/25, 11:59 pm)

Here is the assignment: Assignment #4 – ETL and Pivot Tables in Excel

And here is the Excel workbook you’ll need to complete the assignment: ETL Workbook.xlsx

This assignment is due by** Wednesday, 10/25, 11:59 pm.**

- To complete the assignment, you need to review the Excel basics (supplementary material for Module #7 – ETL) on your own if you are not familiar with Excel functions such as CONCATENATE and VLOOKUP.
- We will cover Pivot Tables through an ICA on Friday (10/20).

## Agenda for Week 8 (Week of 10/16)

Class Schedule:

- Monday — Extract, Transform, Load (ETL) and some Excel basics
- Please review the supplementary materials: Excel Basics, on your own

- Wednesday — Dimensional Data Modeling: Overview
- Friday — ICA #7 (Pivot Tables in Excel)

Deadlines (unless otherwise mentioned, the due time is 11:59 pm):

- Group project
- Submit names and AccessNet accounts of your group members by 10/20 (Friday) at noon.
- Submit your visualization and description (both in PDF) by 10/31 (Tuesday).

- ICA #7 (Pivot Tables in Excel) is due by 10/23 (Monday).
- Assignment #4 (ETL and Pivot Tables in Excel) will be announced and due by 10/25 (Monday).

## Nathan’s Office Hour for Assignment 3 (Friday 3:00-4:00 pm)

Time: 3:00 pm – 4:00 pm on Friday (10/13)

Location: Alter 240B

## Group Project: Data Visualization and the QVC Analytics Challenge (due by 10/31 at 11:59 pm)

Here are the assignment instructions: Group Project – Data Visualization [f17].

**The group project (for the course) is due October 31, at 11:59 pm. **

- You can work in teams of up to four people.
- For each group, please email me the names and AccessNet IDs (i.e., tuz12345) of all group members by
**October 20 before noon**. (This way I can enter the groups in Blackboard in advance.)

The group project is based on the Temple Analytics Challenge, a University-wide data analysis and visualization competition.

- You should enter the challenge as well – to get the extra credit, professional achievement points and a chance to win up to $2500.
- To enter the challenge, you must submit your entry, also by
**October 31, at 11:59 PM**.

## More on Assignment 3 (SQL Part 2)

**1. To start Assignment 3, you need to complete ICA #6 first.** Want to check if you did ICA #6 correctly? Take a look at the solution key that I posted under Course Materials. If you had any mistake in your solution, follow these steps:

- In MySQL Workbench, delete your Company table;
- Copy and paste the statements from the file “ICA #6 SQL statements” to your MySQL Workbench;
- Replace the schema names “mxxws” with your schema name (mxx should be your MySQL username);
- Run all the statements. Make sure you did not get any error message;
- If everything works, you can start Assignment 3 now.

**2. BOOLEAN data type.** In the Contact table, the field IsMain has a BOOLEAN data type. If you do not know how to handle BOOLEAN data type, here is short tutorial that might help: More on MySQL BOOLEAN Data Type