Assignment 2 - Modeling Enterprise Data Warehouse (1)
pdf
keyboard_arrow_up
School
Texas A&M University, Commerce *
*We aren’t endorsed by this school
Course
532
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
2
Uploaded by MasterTank12856
Assignment 2 –
Modeling Enterprise Data Warehouse Spring 2024 The due date for the assignment 2 is 2/18/202
4
(midnight).
It must be submitted through the “Assignment 2 –
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_ASSIGNMENT2_FIRSTNAME_LASTNAME.SQL Overview: In this assignment 2, we apply the 4-step dimensional design to create the 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. 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 sales performance. He wants to know 1) how many products we sell to customer and 2) what is sales performance of their salesperson. Currently, the company uses total numbers of products and sales performance ratio (defined by SalesYTD divided by SalesLastYear) to measure the business process. Provide a 4-steps dimensional model in the SQL comment section (1 points), create a data mart solution in SQL for the dimension model that you propose (2 points), and provide SQL commands to answer the two questions above (2 points). Q2: The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the sale orders’ lifecycle. Specifically, he wants to know how many days in average the orders were moved from ordered status, to due status, and to shipped status to the customers. Currently, the company uses the total numbers of product per order to measure the business process. Provide a 4-steps dimensional model in the SQL comment section (1 points), create a data mart solution in SQL for the dimension model that you propose (2 points), and provide SQL commands to answer the question above (2 points). 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!!
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help