MP7: REI_not

Building and using a Datawarehouse

Goal

Learning about designing and using Data Warehouses to create BI reports.

Context

Pressly was a fun assignment, but a stressful one. So when the chance came, you left Pressly to become a senior business analyst at REI_not, a retailer of camping and outdoor equipment. REI_not is more established and more technologically sophisticated than Pressly, and you figured you could learn a lot there.
One morning, as you walk down the hall at company headquarters, Mark Etting, the Chief Marketing Officer at REI_not spots you and hurries over. He seems to have been looking for you.
“Ah, there you are! Didn’t you go to UVA for your undergrad?”
“Yes, I did...”
“Wahoowa! Me too! What a place! The memories!... Anyway, did you learn anything about business intelligence? I want to slice and dice our sales data by store, by product, and by month. Right now we do it all in Excel, and it is painful. We aggregate the data by hand. So many emails, so much wasted time! There has to be a better way.”
“I know a bit about BI, and yes, there is a better way.”
“I knew it! You’re exactly the person I was looking for. Our IT people are great at moving data around, but they don’t really understand what I need to know to run the business.”
“That is an excellent starting point. What would you like to know about the business?”
“Ah, yes. Here.” He hands you a sheet of paper with a few questions scribbled on it. “These are the kinds of questions I want answered.”
You scan the list. “Hmm... this looks like BI. We may need to integrate the data from our three stores and create reports from it. We should build a data warehouse.”
“A data...what?”
“A central place that pulls together the data from all our stores, so we can produce the reports you need.”
“Yes!...that! Can you build one and have my reports on my desk by next Thursday?”
“I’m on it, boss.”

Task

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 three tables in your centralized DBMS 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 CMO, 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 VS Code to create a notebook that does the following:

As done before, alternate short lines of text that explain what the SQL does, and the actual SQL code.

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 and 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 VS Code to explore the source tables and their columns. Load the data using INSERT INTO.

5) Create queries that produce reports that answer the CMO questions. As in the past, list the number and text of the questions, and the SQL queries that produce the corresponding results when run. One query per notebook cell.

6) Clean the DB by executing the drops you created under step #1

Name the notebook is "MP7 YourLastname.ipynb" and post it by the beginning of class in myFiles.

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.

• One query per question
• If a question does not ask for specific columns, provide them all, except duplicates
• If asked to show data about a person or an item, always show their ID. For cities, use name and state
• Do not show duplicated records
• All result columns must have an easy-to-understand name
• All money figures are formatted with ‘$’, commas, and no decimals.  If most figures in a column are less than $100, show cents.  Integers have commas and no decimals. Percentages have 2 decimals. Interest rates have three. For example, we will accept 3.23% or 4.34%.  For interest rates, 0.373, 2.430, or 3.882%.
• Specific instructions may override these bullets.
• If a question asks for Months, spell them out in full or with three letters (e.g., 'JAN').