Building and using the Data Warehouse at REI_not
Individual graded pledged assignment MP7
Goal
Learning about data warehouse design
Learning about creating tables and inserting records
Learning to query a DW to create BI reports.
Context
Pressly was a fun but also stressful assignment. You are happy to work now as a business analyst for REI_not. You like it there.
Your CEO appears to be looking for you when he sees you walking down the hall at the company HQ.
"Ah! Here you are... didn't you go to that fancy Comm school for your undergrad?"
"Do you mean McIntire?"
"Yeah yeah... that one... did you learn anything about business intelligence? I want to slice and dice our sales data by store, by product, by month. So far I have been doing it in Excel and it is PAINFUL! There must be a better way to do it."
"Yes, I know a little about BI."
"Beautiful! You are the person I was looking for... our IT people are great at moving data around, but they do not fully understand what I need to run our business."
"Which is....?"
""Ah yes, here: I wrote down a few examples of the kind of questions I would like to have answered" says he while giving you a sheet of paper with a few typed-up questions.
"Mmmm.... It looks like you need to integrate the data from our three stores. We should build a Data Warehouse."
"A data what?"
"A technology to produce the reports you need to run the business."
"Yes, THAT! Can you build one of those things and bring me my reports by Wednesday?"
"I am on it, Boss..."
Task
[Note: some of the content relevant to do this assignment will be covered on Monday - however, you might get started]
Good news! The IT department at Rei_not has already built ETL (Extract, Transfer and Load) technology to transfer data from the three REI_not stores to your centralized data warehouse at headquarters. These tables are called STORE1_DATA, STORE2_DATA and STORE3_DATA. They have also built a CALENDAR table for you. You can view all these tables in the COMM3220 database. Given the business questions asked by the CEO, you decide that line-item-level sales per day per product per store is a good design. Using mySQLWorkBench you quickly converge on the star schema below. Then you begin building your Data Warehouse.
Use ADS to create a notebook that does the following:
1) Drop all the tables that YOU created in this exercise (but NOT the tables created by the REI_not IT department). This is for grading.
2) Create and populate a dimension table for Product, based on the data below. Call it <yourUserID>.PRODUCT
and include a PK
3) Create and populate a dimension table for Store, based on the data below. Call it <yourUserID>.STORE.
include a PK
4) Create a new fact table called <yourUserID>.SALES_FACT, with FKs and PKs that contains all the data from all the
stores. To ensure a successful data transfer, the receiving table must have similar datatypes as the source tables. Use ADS
to explore these tables and their columns.
5) Load the data from the three store tables into <yourUserID>.SALES_FACT using INSERT INTO.
6) Create queries that produce reports that answer the CEO questions. As in the past, list (1) the number and text of the questions, and (2) the SQL queries that produce the corresponding results when run.
Filename for the notebook is "MP7
YourLastname YourFirstName.ipynb"
Post the notebook by the beginning of class in your BOX folder.
Criteria
Your SQL must produce the correct tables, populate them with the correct data, and drop them as needed. Read carefully what is being asked. SQL must produce the correct answer and no extra data. All columns must have easy-to-understand names. Format all $ numbers.