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 is concerned with how effective each salesperson can sell products to the customer. To measure the effectiveness of each salesperson, he wants to use total numbers of orders and sales performance ratio (defined by SalesYTD divided by SalesLastYear). Provide a 4-steps dimensional model in the SQL comment section (1 points) and create a data mart solution in SQL for the dimension model that you propose.

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 the total numbers of orders when they are ordered, due, and shipped to the customer. Provide a 4-steps dimensional model in the SQL comment section (1 points) and create a data mart solution in SQL for the dimension model that you propose.

Q3 The CEO of AdventureWorks Inc. wants to build a data mart to keep track of store's monthly sales performance. Specifically, he wants to keep track of monthly tax effectiveness (defined by Tax Amount divided by Sales SubTotal) and monthly sales amount. Provide a 4-steps dimensional model in the SQL comment section (1 points) and create data mart solutions in SQL for the dimension model that you propose.