Need help with solutions

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.

Q$1$:

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 sales productivity of their

salesperson. Currently, the company uses total numbers of orders 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$).$

Q$2$:

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 quickly 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 orders 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$).$