Summer 2023 It is important that you specify your name in the assignment document/attachment: Your submitted files should contain one SQL file for part 1, and a DTSX file part 2. If you use Flat File or Excel Data Source for Part 2, please include the file also. It is important that you specify your name in the assignment document/attachment: The attachment name should follow the following convention for Part I: BUSA532 MIDTERM FIRSTNAME LASTNAME PART1.SQL The attachment name should follow the following convention for Part II: BUSA532_MIDTERM_FIRSTNAME_LASTNAME PART2.DTSX PART I: Use Adventure Work OLTP logical schema to build data warehouse tables in SQL: CEO of Adventure Work Inc. wants to keep track of performance of their stores. Particularly, he wants to know the total number of products produced quarterly and total number of products sold quarterly. In addition, he wants to know yearly net profit margin (calculated by using product list price divided by product standard cost). Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose (26 points). PART II: Use Adventure Work OLTP's Production and Sales schema to build a data warehouse consisted of two data marts. (You can find instructions to install the database in Topic 2 if you haven't installed yet). (70 points). Your database should meet these requirements: DimDate table has DateKey values ranging from 20100304 to 20140602 (YYYYMMDD integer data type). If you use Flat File or Excel Data Source to import date key data, include the data source file in the submission also (5 points). DimDate table should have IsMartin LutherKing (BOOLEAN data type), Week Number Of Year (INT data type), DayNumberOf Year (INT data type), and Quarter (INT data type) attributes (15 points). - DimProduct should contain product ID, product name, category name, discontinued, and Current Value (10 points). Dim Product should be able to handle Type 2 SCD issues (5 points). FactlessProductSelling Table has 2 foreign keys - SellStartDateKey and ProductKey (10 points). SalesPerformance has 2 foreign keys – DateKey and ProductKey (10 points). Correct ETL process to create 3 fact measures for SalesPerformance (15 points) The following table and graph show you the data description of fact measures and ERD of the data warehouse: Fact Measure TotalProductOrdered TotalProductSpecialOffer MostProductOrdered Indicator Data Description Total number of products ordered each day Total amount of product special offer each day Display Boolean value if the product was ordered the most FactlessProductSelling Table FK SellStartDateKey FK ProductKey PK DateKey DimDate int IsMartinLutherKing boolean WeekNumberOfYear int DimProduct PK int ProductKey int FK DateKey DayNumberOfYwar int Quarter int FK Product Key SalesOrderID ProductID int int ProductName varchar(25) CategoryName varchar(25) Discontinued boolean CurrentValue varchar(25) SalesPerformance TotalProduct Ordered int int int int TotalProductSpecial Offer money MostProductOrderedIndicator boolean The following criteria will be used to grade the midterm: • Codes are detailed, accurate, and SSIS solution package are correct. ***NOTE Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!