Description
Objectives
1. Become familiar with the structure of a dimensional model / star schema data warehouse
2. Understand the unique nature of the date dimension
3. Successfully run the scripts necessary to create the sample data warehouse consisting of 5
dimension tables and one fact table. Run a script to verify that your data warehouse is correctly
built.
4. Run SQL against your data warehouse to answer the assigned problems.
Step One: Ensure that your MySQL environment is working.
This assignment follows on Project Assignment # 1 in which you created a MySQL environment on your
personal computer and executed queries against it. For Homework # 3, you must first ensure that
MySQL is up and running on your device, and that you are able to run SQL queries against your
database(s).
If your MySQL environment (DBMS Engine and Query Editor) isn’t working properly, please refer to the
instructions for Project # 1 to get the MySQL environment set up and working.
Step Two: Creating the Data Warehouse Tables
To get started, you need to download some scripts from Moodle and create your database and tables,
and then load the tables with data.
Before you can create your database, you need to make sure that your instance of MySQL is running.
Then using your query editor, you must connect to the MySQL instance.
Creating the Data Warehouse:
The SQL statements to create the tables for your Homework # 3 data warehouse can be found on the
Moodle site “Week 12 Moodle November 12 – 18” under the Homework assignments heading. There
are 9 scripts for you to run.
Script # 1: Create the Database
Scripts # 2-6: Create and Load the Five Dimension Tables
Script # 7: Create and Load the Fact Table
Script # 8: Create the Foreign Key Constraints needed for the Fact Table
Script # 9: Run the “verify” script to ensure that the data warehouse is built properly
Your Sales_DW database consists of the following tables:
• Dim_Product
• Dim_Store
• Dim_Customer
• Dim_Date
• Dim_SalesPerson
• Fact_ProductSales
• Dates, Numbers, Numbers_Small – Created and used by the script to create the dim_date
dimension, but NOT used for anything else.
To complete this homework, you must open up each of these 9 script files, copy the SQL statements,
paste the SQL statements into your query editor and execute the script.
Note: Most scripts begin with a command to DROP the table before it creates it. This allows you to
run the script over and over as needed.
After creating the five dimension tables and one fact table, run the “verify” script. You should see the
following tables and row counts for each.
Preparing Your Homework Submission
Your results for this homework assignment should be captured in a document (such as a .txt file, MS
Word or similar tool.) Please then save your final deliverable document as a PDF. Use the link found in
the Homework Assignment section of “Week 12 Moodle November 12 – 18” Moodle site to submit your
work for grading. If you are doing PAIR PROGRAMMING on this assignment, please be sure to identify
the name of your programming partner on your submission. You must EACH submit your own results
document for this homework and both names must appear on all submitted documents.
Data Warehouse Problems
For this homework you must answer the questions below stating the results of your analysis of the data
in the data warehouse. Each answer should be stated in a sentence or two providing the requested
analysis. For these questions, you must create and execute one or more SQL Queries against the
sales_dw data warehouse to answer the question. In addition to the answer to the question, you must
turn in your SQL code AND your answer set from the query.
1. Which customer has produced the most total revenue for this organization? (Total Revenue =
SalesPrice * Quantity)
2. Which salesperson has produced the most total revenue for this organization? (Total Revenue =
SalesPrice * Quantity)
3. What is the total revenue by store for each year represented in the data warehouse?
4. Which store had the highest sales dollar volume in September, 2011?
5. Describe the trend in total revenue for the Boulder store for each quarter in 2016.