PK

SalesPerformanceFact

FK Sales Personkey

FK OrderDateKey

TotalTaxAmount

TotalSubTotal

HighestTaxAmountForOrder

DimDate

DateKey

Full Date

int

datetime

int

int

int

int

int

SalesPersonDim

SalesPersonKey

BusinessEntityID

Marital Status

Gender

SalesQuota

int

int

varchar(25)

varchar(25)

int/nAssignment 4 - ETL for Simple Enterprise Data

Warehouse

Summer 2023

It must be submitted through the

"Assignment 4 - ETL for Simple Enterprise Data Warehouse" in Assignment Submission Folder provided

in the D2L. It is important that you specify your name in the SQL Server Integration Service DTSX file. The

attachment name should follow the following convention:

BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.DTSX.

Overview:

Now that we have learned to load data into Date dimension in assignment 3. In assignment 4, we are

going to repeat the ETL process for other dimension and fact tables. In this assignment, you goal is to

load data from OLTP database to create dimension and fact table using SSIS.

Business scenario:

Please install AdventureWorks 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 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: TotalTaxAmount, TotalSubTotal, and HighestTaxAmountForOrder. Create SSIS package

solution that can perform ETL process to load data into OLAP database (10 points). Please note that

Sales PersonDim should handle Slowly Changing Dimension issues.

The following criteria will be used to grade the assignment:

• Codes are run with detail comment, accurate, and answers are correct.

*** NOTE

Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!