Assignment 4 – Modeling Enterprise Data Warehouse Spring 2021
The due date for assignment 4 is 3/10/2021 (midnight). It must be submitted through the
“Assignment 4 – Modeling Enterprise Data Warehouse” in Assignment Submission Folder provided in the D2L. It is important that you specify your name in the SQL file. The attachment name should follow the following convention: BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.SQL
Overview:
In assignment 3, we have learned 4-step dimensional design to create the first draft of your data warehouse models. Our models are preliminary works in which we continue to gather more requirements from sponsors to begin developing more cohesive final drafts. The dimension and fact tables in assignment 3 are quite simple because we care more about the high abstract level of our first draft model. In assignment 4, we are continuing to apply the 4-step dimensional design to further refining our fact tables and dimension tables.
Business scenario:
Please install AdventureWorks database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment.
Q1:
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the sale orders’ performance. Specifically, he wants to know the total order quantity for each products, total order price, and total tax order percentage paid by customer (defined by total tax amount divided by total order price). Provide a 4-steps dimensional model in the SQL comment section (2 points) and create a data mart solution in SQL for the dimension model that you propose (4 points).
Q2:
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the sale orders’ performance. The figure below shows the current design of the data mart. However, the data mart designer(s) assume that one order can only be paid by one credit card. If one order can be paid by multiple credit cards, what changes should we make to keep track with total order amount and total tax amount for each credit card? Create data mart solution in SQL for the dimension model that you propose (4 points).
SaleOrderFact
FK
CustomerKey
FK
CreditCardKey
TotalOrderAmount
TotalTaxAmount
The following criteria will be used to grade the assignment:
· Codes are run with detail comment, accurate, and answers are correct.
***************************NOTE *************************************
Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!
