Reference no: EM132392165 , Length: 4 Pages

DATA 620 Data Management and Visualization Assignment

University of Maryland University College, USA

This assignment starts with the script, "Week6_business_units.sql" .  This script should create a table called "business_unit" and a table called "Product_BU."  Unfortunately, the metadata descriptions have been lost, so you will need to figure out what you can from the SQL script.  The only thing you know about the metadata is that the company runs several individual strategic business units, such as "On The Go" and "Snack."  Each of these business units is run under an umbrella designation, such as "Growth" or "Decline."  The company will run marketing for growth products differently than it would run marketing for products on the decline.  

You also have product order files from 2012, 2013, and 2014.  They are attached as .csv files titled 

• "2012_product_data_students.csv"
• "2013_product_data_students.csv"
• "2014_product_data_students.csv"

Your job is to use SQL to perform an ETL which will accomplish the following:

1. Extract data from the 2012, 2013, and 2014 order files.

2. Transform the data according to the given rules.

3. Load it into one final table.

4. Export your final output table under the name "GX_output_final.csv" . (You may create as many or as few data objects as you like in your work, but the data in the .csv file named "GX_output_final.csv" will be the data evaluated.

Please name the computer files you submit for this assignment with a "GX" prefix, where "X" is your group number.  For example, if you are in Group 3, you might create an SQL script named "G3_extract_2012.sql"  (Ensure your group number and group member names are commented in any script you turn in as well.)  

This is so when we grade the work, it's clear which bit came from which group.   You should get credit for your good work!  

You may write one large SQL script to accomplish the entire process.  You may also break your SQL commands into smaller groups, interspersed with MySQL GUI commands.  If you do this, your notes should reflect what you did (for example, in the Appendix you could say "We created database YYY, and then used the "import" button on the MySQL GUI interface to upload the .csv file into Table Z.  Then we ran the script shown in Figure X ...")  

Please only use MySQL for this assignment.  The only exceptions here are minor edits made using Notepad or Excel, such as putting headers on column names.  Document these carefully in your Appendix; if your SQL script doesn't write column headers, but your output file magically has them, we want to know how they got there.  You can just say something like "After we did << XXX >> to export the data, we used Notepad to insert Row 1, which are the header names.")

Management Memo -

Your team writes a memo to management outlining your answers to the following questions:

1. Create and explain an entity relationship diagram (ERD) to go with this data.  Your ERD should describe the business situation in existence as best as you can infer it.   Since your input files are not necessarily in the best shape, your ERD should not simply map the input files.  Your output file is by definition a flat file with no major database schema, so your ERD shouldn't map that either.  As a hint, consider this:  based on the data here, what relationship can you infer exists between BU Designation and Product?  One to one?  One to many?  Must-have or may-have?  Use ER Assistant to do your ERD, and incorporate a screenshot of your ERD in the management memo.  (You do not need to attach the ER Assistant file.)

2. Document your ETL process.  Which functions did you use, and what logic did you follow?  This should be at the level that your boss, who has an MBA but not an IT/database background, can follow it.  Do not use "computer-ese" here; use regular business English.

3. Give metadata for your final deliverable file.  The analysts who follow you will thank you.

4. Your boss has a question for you.  "We think this is about the right level of granularity for our data mart.  What do you think? 

Should we extract more detailed information, and if so, what?  Or would you recommend going to a coarser level of granularity, and if so, what fields would you recommend we drop?" Give your rationale.  Think critically, and demonstrate a good understanding of data management.

5. Your boss wants to know the answer to this business question:  "We believe our Growth segment should show at least 10% year over year growth in either quantity sold or order total.  We also believe our Mature segment should remain pretty much the same in terms of quantity and order totals.  If I give the final data file you produced to Ramon (an expert analyst), can he run queries to answer this?"  (You may wish to run a query or two as proof of concept.)  Tell the boss if you believe the data as laid out like it is will easily support Ramon in that sort of analysis.  If it will, what about it makes it easy?  If it won't, how could it change to support this analysis?

6. Your boss has another question:  "Our database folks have suggested we use a different format for the ETL if I'm so interested in growth.  It's copied below.  It's the exact same data, just a little differently arranged.  What do you think of it?  Bobby, one of my IT people, thinks a data mart with this layout is a brilliant answer to the growth question.  But Susie, another one of my IT people, has concerns that this data layout will make it hard to query on any other dimension, such as whether a particular product is doing well or poorly in a given region, regardless of year, or monthly seasonal trends.  Am I missing anything here?  What do you recommend?  If we had to go with just one layout of our data mart, which layout should it be?"

Attachment:- Data Management and Visualization Assignment Files.rar