assignment 5 etl for periodic snapshot enterprise data warehouse your
Search for question
Question
Assignment 5 - ETL for Periodic Snapshot Enterprise Data Warehouse
Your goal is to load data from OLTP database to create dimension and periodic snapshot fact table using SSIS.
Business scenario:
Please install Adventure Works database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment. The CEO of Adventure Work Inc. wants a data warehouse to keep track of monthly sales' performance. The figure below shows tables of Adventure Work Inc.'s OLAP. Three fact metrics that he wants to have for his data warehouse: Monthly TotalTaxAmount, Monthly TotalOrders, and MonthlyTotalProductSold. Create SSIS package solution that can perform ETL process to load data into OLAP database (10 points). Please note that DimCustomer does not handle Slowly Changing Dimension issues.
DimMonth
FactSnapshotTournament
PK
MonthKey
int
FK
MonthKey
int
EnglishMonthName varchar(10)
FK
CustomerKey
int
CalendarYear
int
MonthlyTotalOrders
int
Quarter
int
MonthlyTotalTaxAmount
int
MonthlyTotalProductSold
int
DimCustomer
PK
CustomerKey
int
PersonType
varchar(25)
CustomerFirstName
varchar(25)
CustomerLastName
varchar(25)
The following criteria will be used to grade the assignment: Codes are run with detail comment, accurate, and answers are correct. Need to submit Source code file and output Screenshots of final answers.