Answer To: Assignment 4 – ETL for Simple Enterprise Data Warehouse Fall 2021 The due date for the assignment 4...
Ravindra Kumar answered on Oct 26 2021
Dbms/adventurework-oltp-erd-3-wizlzv1g-wtsthxvt.png
Dbms/assignment-4-etl-for-simple-enterprise-data-warehouse-iy1qcqnh.pdf
Assignment 4 – ETL for Simple Enterprise Data
Warehouse
Fall 2021
The due date for the assignment 4 is 10/26/2021 (midnight). 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 and
Word document. The attachment name should follow the following convention:
BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.DTSX.
BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.DOCX.
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 (8 points), and submit the
mapping document in Word file in D2L (2 points). Please note that SalesPersonDim 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!!
SalesPersonDimSalesPerformanceFact
DimDate
SalesPersonKey intPK
BusinessEntityID int
MaritalStatus varchar(25)
Gender varchar(25)
SalesQuota int
SalesPersonKey intFK
TotalTaxAmount int
TotalSubTotal int
HighestTaxAmountForOrder int
DateKey intPK
FullDate date time
OrderDateKey intFK
Dbms/datekeyaw-2-wgxilgzd-jb555adv(1).csv
DateKey
20110531
20110601
20110602
20110603
20110604
20110605
20110606
20110607
20110608
20110609
20110610
20110611
20110612
20110613
20110614
20110615
20110616
20110617
20110618
20110619
20110620
20110621
20110622
20110623
20110624
20110625
20110626
20110627
20110628
20110629
20110630
20110701
20110702
20110703
20110704
20110705
20110706
20110707
20110708
20110709
20110710
20110711
20110712
20110713
20110714
20110715
20110716
20110717
20110718
20110719
20110720
20110721
20110722
20110723
20110724
20110725
20110726
20110727
20110728
20110729
20110730
20110731
20110801
20110802
20110803
20110804
20110805
20110806
20110807
20110808
20110809
20110810
20110811
20110812
20110813
20110814
20110815
20110816
20110817
20110818
20110819
20110820
20110821
20110822
20110823
20110824
20110825
20110826
20110827
20110828
20110829
20110830
20110831
20110901
20110902
20110903
20110904
20110905
20110906
20110907
20110908
20110909
20110910
20110911
20110912
20110913
20110914
20110915
20110916
20110917
20110918
20110919
20110920
20110921
20110922
20110923
20110924
20110925
20110926
20110927
20110928
20110929
20110930
20111001
20111002
20111003
20111004
20111005
20111006
20111007
20111008
20111009
20111010
20111011
20111012
20111013
20111014
20111015
20111016
20111017
20111018
20111019
20111020
20111021
20111022
20111023
20111024
20111025
20111026
20111027
20111028
20111029
20111030
20111031
20111101
20111102
20111103
20111104
20111105
20111106
20111107
20111108
20111109
20111110
20111111
20111112
20111113
20111114
20111115
20111116
20111117
20111118
20111119
20111120
20111121
20111122
20111123
20111124
20111125
20111126
20111127
20111128
20111129
20111130
20111201
20111202
20111203
20111204
20111205
20111206
20111207
20111208
20111209
20111210
20111211
20111212
20111213
20111214
20111215
20111216
20111217
20111218
20111219
20111220
20111221
20111222
20111223
20111224
20111225
20111226
20111227
20111228
20111229
20111230
20111231
20120101
20120102
20120103
20120104
20120105
20120106
20120107
20120108
20120109
20120110
20120111
20120112
20120113
20120114
20120115
20120116
20120117
20120118
20120119
20120120
20120121
20120122
20120123
20120124
20120125
20120126
20120127
20120128
20120129
20120130
20120131
20120201
20120202
20120203
20120204
20120205
20120206
20120207
20120208
20120209
20120210
20120211
20120212
20120213
20120214
20120215
20120216
20120217
20120218
20120219
20120220
20120221
20120222
20120223
20120224
20120225
20120226
20120227
20120228
20120229
20120301
20120302
20120303
20120304
20120305
20120306
20120307
20120308
20120309
20120310
20120311
20120312
20120313
20120314
20120315
20120316
20120317
20120318
20120319
20120320
20120321
20120322
20120323
20120324
20120325
20120326
20120327
20120328
20120329
20120330
20120331
20120401
20120402
20120403
20120404
20120405
20120406
20120407
20120408
20120409
20120410
20120411
20120412
20120413
20120414
20120415
20120416
20120417
20120418
20120419
20120420
20120421
20120422
20120423
20120424
20120425
20120426
20120427
20120428
20120429
20120430
20120501
20120502
20120503
20120504
20120505
20120506
20120507
20120508
20120509
20120510
20120511
20120512
20120513
20120514
20120515
20120516
20120517
20120518
20120519
20120520
20120521
20120522
20120523
20120524
20120525
20120526
20120527
20120528
20120529
20120530
20120531
20120601
20120602
20120603
20120604
20120605
20120606
20120607
20120608
20120609
20120610
20120611
20120612
20120613
20120614
20120615
20120616
20120617
20120618
20120619
20120620
20120621
20120622
20120623
20120624
20120625
20120626
20120627
20120628
20120629
20120630
20120701
20120702
20120703
20120704
20120705
20120706
20120707
20120708
20120709
20120710
20120711
20120712
20120713
20120714
20120715
20120716
20120717
20120718
20120719
20120720
20120721
20120722
20120723
20120724
20120725
20120726
20120727
20120728
20120729
20120730
20120731
20120801
20120802
20120803
20120804
20120805
20120806
20120807
20120808
20120809
20120810
20120811
20120812
20120813
20120814
20120815
20120816
20120817
20120818
20120819
20120820
20120821
20120822
20120823
20120824
20120825
20120826
20120827
20120828
20120829
20120830
20120831
20120901
20120902
20120903
20120904
20120905
20120906
20120907
20120908
20120909
20120910
20120911
20120912
20120913
20120914
20120915
20120916
20120917
20120918
20120919
20120920
20120921
20120922
20120923
20120924
20120925
20120926
20120927
20120928
20120929
20120930
20121001
20121002
20121003
20121004
20121005
20121006
20121007
20121008
20121009
20121010
20121011
20121012
20121013
20121014
20121015
20121016
20121017
20121018
20121019
20121020
20121021
20121022
20121023
20121024
20121025
20121026
20121027
20121028
20121029
20121030
20121031
20121101
20121102
20121103
20121104
20121105
20121106
20121107
20121108
20121109
20121110
20121111
20121112
20121113
20121114
20121115
20121116
20121117
20121118
20121119
20121120
20121121
20121122
20121123
20121124
20121125
20121126
20121127
20121128
20121129
20121130
20121201
20121202
20121203
20121204
20121205
20121206
20121207
20121208
20121209
20121210
20121211
20121212
20121213
20121214
20121215
20121216
20121217
20121218
20121219
20121220
20121221
20121222
20121223
20121224
20121225
20121226
20121227
20121228
20121229
20121230
20121231
20130101
20130102
20130103
20130104
20130105
20130106
20130107
20130108
20130109
20130110
20130111
20130112
20130113
20130114
20130115
20130116
20130117
20130118
20130119
20130120
20130121
20130122
20130123
20130124
20130125
20130126
20130127
20130128
20130129
20130130
20130131
20130201
20130202
20130203
20130204
20130205
20130206
20130207
20130208
20130209
20130210
20130211
20130212
20130213
20130214
20130215
20130216
20130217
20130218
20130219
20130220
20130221
20130222
20130223
20130224
20130225
20130226
20130227
20130228
20130301
20130302
20130303
20130304
20130305
20130306
20130307
20130308
20130309
20130310
20130311
20130312
20130313
20130314
20130315
20130316
20130317
20130318
20130319
20130320
20130321
20130322
20130323
20130324
20130325
20130326
20130327
20130328
20130329
20130330
20130331
20130401
20130402
20130403
20130404
20130405
20130406
20130407
20130408
20130409
20130410
20130411
20130412
20130413
20130414
20130415
20130416
20130417
20130418
20130419
20130420
20130421
20130422
20130423
20130424
20130425
20130426
20130427
20130428
20130429
20130430
20130501
20130502
20130503
20130504
20130505
20130506
20130507
20130508
20130509
20130510
20130511
20130512
20130513
20130514
20130515
20130516
20130517
20130518
20130519
20130520
20130521
20130522
20130523
20130524
20130525
20130526
20130527
20130528
20130529
20130530
20130531
20130601
20130602
20130603
20130604
20130605
20130606
20130607
20130608
20130609
20130610
20130611
20130612
20130613
20130614
20130615
20130616
20130617
20130618
20130619
20130620
20130621
20130622
20130623
20130624
20130625
20130626
20130627
20130628
20130629
20130630
20130701
20130702
20130703
20130704
20130705
20130706
20130707
20130708
20130709
20130710
20130711
20130712
20130713
20130714
20130715
20130716
20130717
20130718
20130719
20130720
20130721
20130722
20130723
20130724
20130725
20130726
20130727
20130728
20130729
20130730
20130731
20130801
20130802
20130803
20130804
20130805
20130806
20130807
20130808
20130809
20130810
20130811
20130812
20130813
20130814
20130815
20130816
20130817
20130818
20130819
20130820
20130821
20130822
20130823
20130824
20130825
20130826
20130827
20130828
20130829
20130830
20130831
20130901
20130902
20130903
20130904
20130905
20130906
20130907
20130908
20130909
20130910
20130911
20130912
20130913
20130914
20130915
20130916
20130917
20130918
20130919
20130920
20130921
20130922
20130923
20130924
20130925
20130926
20130927
20130928
20130929
20130930
20131001
20131002
20131003
20131004
20131005
20131006
20131007
20131008
20131009
20131010
20131011
20131012
20131013
20131014
20131015
20131016
20131017
20131018
20131019
20131020
20131021
20131022
20131023
20131024
20131025
20131026
20131027
20131028
20131029
20131030
20131031
20131101
20131102
20131103
20131104
20131105
20131106
20131107
20131108
20131109
20131110
20131111
20131112
20131113
20131114
20131115
20131116
20131117
20131118
20131119
20131120
20131121
20131122
20131123
20131124
20131125
20131126
20131127
20131128
20131129
20131130
20131201
20131202
20131203
20131204
20131205
20131206
20131207
20131208
20131209
20131210
20131211
20131212
20131213
20131214
20131215
20131216
20131217
20131218
20131219
20131220
20131221
20131222
20131223
20131224
20131225
20131226
20131227
20131228
20131229
20131230
20131231
20140101
20140102
20140103
20140104
20140105
20140106
20140107
20140108
20140109
20140110
20140111
20140112
20140113
20140114
20140115
20140116
20140117
20140118
20140119
20140120
20140121
20140122
20140123
20140124
20140125
20140126
20140127
20140128
20140129
20140130
20140131
20140201
20140202
20140203
20140204
20140205
20140206
20140207
20140208
20140209
20140210
20140211
20140212
20140213
20140214
20140215
20140216
20140217
20140218
20140219
20140220
20140221
20140222
20140223
20140224
20140225
20140226
20140227
20140228
20140301
20140302
20140303
20140304
20140305
20140306
20140307
20140308
20140309
20140310
20140311
20140312
20140313
20140314
20140315
20140316
20140317
20140318
20140319
20140320
20140321
20140322
20140323
20140324
20140325
20140326
20140327
20140328
20140329
20140330
20140331
20140401
20140402
20140403
20140404
20140405
20140406
20140407
20140408
20140409
20140410
20140411
20140412
20140413
20140414
20140415
20140416
20140417
20140418
20140419
20140420
20140421
20140422
20140423
20140424
20140425
20140426
20140427
20140428
20140429
20140430
20140501
20140502
20140503
20140504
20140505
20140506
20140507
20140508
20140509
20140510
20140511
20140512
20140513
20140514
20140515
20140516
20140517
20140518
20140519
20140520
20140521
20140522
20140523
20140524
20140525
20140526
20140527
20140528
20140529
20140530
20140531
20140601
20140602
20140603
20140604
20140605
20140606
20140607
20140608
20140609
20140610
20140611
20140612
20140613
20140614
20140615
20140616
20140617
20140618
20140619
20140620
20140621
20140622
20140623
20140624
20140625
20140626
20140627
20140628
20140629
20140630
20140701
20140702
20140703
20140704
20140705
20140706
20140707
20140708
20140709
20140710
20140711
20140712
financepersonnel-ggoylqdd(1).csv
First_Name,Last_Name,samAccount,City,County,PostalCode,OfficePhone,MobilePhone
James,Butter,James.Butter,New Orleans,Orleans,70116,504-621-8927,504-845-1427
Josephine,Darakjy,Josephine.Darakjy,Brighton,Livingston,48116,810-292-9388,810-374-9840
Art,Venere,Art.Venere,Bridgeport,Gloucester,8014,856-636-8749,856-264-4130
Lenna,Paprocki,Lenna.Paprocki,Anchorage,Anchorage,99501,907-385-4412,907-921-2010
Donette,Foller,Donette.Foller,Hamilton,Butler,45011,513-570-1893,513-549-4561
Simona,Morasca,Simona.Morasca,Ashland,Ashland,44805,419-503-2484,419-800-6759
Mitsue,Tollner,Mitsue.Tollner,Chicago,Cook,60632,773-573-6914,773-924-8565
Leota,Dilliard,Leota.Dilliard,San Jose,Santa Clara,95111,408-752-3500,408-813-1105
Sage,Wieser,Sage.Wieser,Sioux Falls,Minnehaha,57105,605-414-2147,605-794-4895
Kris,Marrier,Kris.Marrier,Baltimore,Baltimore City,21224,410-655-8723,410-804-4694
Minna,Amigon,Minna.Amigon,Kulpsville,Montgomery,19443,215-874-1229,215-422-8694
Abel,Maclead,Abel.Maclead,Middle Island,Suffolk,11953,631-335-3414,631-677-3675
Kiley,Caldarera,Kiley.Caldarera,Los Angeles,Los Angeles,90034,310-498-5651,310-254-3084
Graciela,Ruta,Graciela.Ruta,Chagrin Falls,Geauga,44023,440-780-8425,440-579-7763
Cammy,Albares,Cammy.Albares,Laredo,Webb,78045,956-537-6195,956-841-7216
Mattie,Poquette,Mattie.Poquette,Phoenix,Maricopa,85013,602-277-4385,602-953-6360
Meaghan,Garufi,Meaghan.Garufi,Mc Minnville,Warren,37110,931-313-9635,931-235-7959
Gladys,Rim,Gladys.Rim,Milwaukee,Milwaukee,53207,414-661-9598,414-377-2880
Yuki,Whobrey,Yuki.Whobrey,Taylor,Wayne,48180,313-288-7937,313-341-4470
Fletcher,Flosi,Fletcher.Flosi,Rockford,Winnebago,61109,815-828-2147,815-426-5657
Bette,Nicka,Bette.Nicka,Aston,Delaware,19014,610-545-3615,610-492-4643
Veronika,Inouye,Veronika.Inouye,San Jose,Santa...