DAta warehouse modelling

MegaOmniCorp sells flipwidgets to Canadian consumers. It has a number of salespeople who manage sales territories. Each salesperson has their own Quota which may vary over time depending on market conditions. MegaOmniCorp uses a machine learning algorithm to set the sales quota for each sales person and territory based on a number of inputs including sales data over time. Additionaly, management will leverage sales data to set compensation levels for their sales people. The machine learning algorithm requires a data feed that provides the number of products sold per product per salesorder per sales person per territory. Management requires a report that provides the total amount sold YTD per salesperson per territory.


The Task:
Part 1. Design a star schema that meets the needs of both requirements based on the ERD embedded.

Using PowerPoint, Word or NotePad, provide the design of the star schema and return it with the code written below.

Part 2. Write a MS SQL-server stored procedures that produces a data set based on the star schema you have designed.

Part 2 a: Write the stored procedure to extract data from your star schema for the machine learning algorithm. You should include the columns:
ProductID
Order Quantity
SalesorderID
Sales Person Firstname
Sales Person Lastname
Order Date
TerritoryID

Part 2b: Write the stored procedure to extract data from your star schema for the Management Report. The procedure should accept the following parameters: Territory and a group of sales people (one or more). You should include the following columns:
TerritoryID
Job Title
Sales Person FirstName
Sales Person LastName
Sales YTD




Impressive Bonus:
Provide the employee percent of total annual sales as column PercentOfTotal.
Also provide the Employee rank by sales while avoiding the use of cursors.

Return:
Create and return a zip file containing your report sql file and a star schema diagram.