This is done in SQL. Please paste formatted code in your answer

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

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

SaleOrderFact FK Customerkey FK CreditCardKey TotalOrderAmount TotalTaxAmount Credit Card Dim CustomerDim PK CreditCardKey PK Customerkey CreditCardNumber CustomerID CreditCardBillingAddress Customer Name SaleOrderFact FK Customerkey FK CreditCardKey TotalOrderAmount TotalTaxAmount Credit Card Dim CustomerDim PK CreditCardKey PK Customerkey CreditCardNumber CustomerID CreditCardBillingAddress Customer Name