part 1 SSIS
Rama
datawarehouseassignment2251.docPART I:
Use the OLTP logical schema below to build data warehouse consisted of two data marts. You will need to import the final exam data code for the OLTP logical schema and develop ETL process in SSIS (70 points). Your tables should meet these requirements:
· DimDate table has DateKey values ranging from 19960704 to 19980603 (YYYYMMDD integer data type). If you use Flat File or Excel Data Source to import datekey data, include the data source file in the submission also (5 points).
· DimDate table should have EnglishMonthName (varchar data type), CalendarYear (INT data type), Quarter (INT data type), LeapYear (BOOLEAN data type), and IsMartinLutherKingHoliday (BOOLEAN data type) attributes (15 points).
· DimProduct should contain product name, supplier name, category name, unit price, and discontinued (10 points).
· AccumulatingOrderFact has 2 foreign keys – OrderDateKey and ShipDateKey, and one degenerate dimension attribute - OrderID (10 points).
· PeriodicSnapShotMonthlyOrder has 2 foreign keys – MonthKey and ProductKey (10 points).
· Correct ETL process to create 3 fact measures for AccumulatingOrderFact (10 points)
· Correct ETL process to create 3 fact measures for PeriodicSnapShotMonthlyOrder (10 points)
The following table and graph show you the data description of fact measures and ERD of the data warehouse:
|
Fact Measure |
Data Description |
|
TotalProductOrderReceivedOnOrderDate |
Total number of product order that data warehouse received |
|
|
on the order date. Note that one order could have multiple |
|
|
products ordered. |
|
TotalPriceOrderReceivedOnOrderDate |
Total amount of price order that data warehouse received |
|
|
on the order date. |
|
TotalProductOrderShippedOnShipDate |
Total number of product order that data warehouse shipped |
|
|
on the ship date. We assume that products of one order are |
|
|
always shipped together. |
|
TotalMonthlyProductOrdered |
Total number of products ordered of one month |
|
TotalMonthlyAmountOfProductDiscount |
Total amount of product discount of one month |
|
MonthlyPopularItem |
Display ‘True’ if the product sold the most in that month. |
|
|
Otherwise ‘No’. |
AccumulatingOrderFact
|
|
FK |
OrderDateKey |
int |
|
|
|
|
|
|
|
|
ShipDateKey |
int |
|
|
|
|
|
|
|
FK |
|
|
|
|
|
|
|
|
|
|
OrderID |
int |
|
|
|
|
|
|
|
DD |
|
|
|
|
|
TotalProductOrderReceivedOnOrderDate |
int |
|
|
|
|
|
|
|
|
TotalPriceOrderReceivedOnOrderDate |
money |
|
|
|
TotalProductOrderShippedOnShipDate |
int |
DimProduct
|
|
PK |
ProductID |
int |
|
|
|
UnitPrice |
money |
|
|
|
ProductName |
varchar(25) |
|
|
|
SupplierName |
varchar(25) |
|
|
|
CategoryName |
varchar(25) |
|
|
|
Discontinued |
bit |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DimDate |
|
|
|
|
|
|
PeriodicSnapShotMonthlyOrder |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DateKey |
int |
|
|
|
|
|
MonthKey |
int |
|
|
|
|
|
PK |
|
|
|
|
|
|
FK |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ProductKey |
int |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EnglishMonthName |
varchar(10) |
|
|
|
FK |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CalendarYear |
int |
|
|
|
|
TotalMonthlyProductOrdered |
int |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Quarter |
int |
|
|
|
|
TotalMonthlyAmountOfProductDiscount |
money |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
LeapYear |
boolean |
|
|
|
|
MonthlyPopularItem |
bit |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IsMartinLutherKingHoliday |
boolean |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
