Business scenario:
Please install Adventure Works database, read the Adventure Works case study, and analyze the ERD
OLTP schema before starting this assignment.
Q1:
The CEO of Adventure Works Inc. wants to build a data mart to keep track of product production inventory. He is concerned with how effective each product vendor provides the company appropriate products. To measure the effectiveness of each product vendor, he wants to use total numbers of work order quantity and total number of work order scrapped quantity. 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 (2 points).
Q2:
The CEO of Adventure Works Inc. wants to build a data mart to keep track of the product production’s lifecycle. Specifically, he wants to know the total number of products when the company received work order and bill of materials. 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 (2 points).
Q3
The CEO of Adventure Works 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 (3 points).
