Date/Time: October 24 (Tuesday), 3:30 pm – 4:30 pm
Location: Alter 240A (Breakout room)
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/
Pivot Tables: https://www.fox.temple.edu/vault/video/pivot-tables/
Thanks to Grace Laggan who recommended these videos to me.
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.
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.
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.
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).
- 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).
Time: 3:00 pm – 4:00 pm on Friday (10/13)
Location: Alter 240B
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.
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