Subject: Data warehousing and Data Mining

Read the following scenario and perform ETL (Extract, Transform, and Load) process step wise using Sql Server Integration Services using following excel fie.

A company maintains customers data of small outlets in excel files and finally send data to its main branch as a total sale per month. This data is necessary at headquarters (main branch) to track performance of each outlet. We will collect customer product purchase sales data from small-small outlets (extract) in excel file. Since the main branch of company is located at USA therefore we need to transform the currency into us dollar. Finally loading this transformed data into database / data warehouse.

200 2 00 CustomerCode CustomerName Product Purchase Quantity Amount CustomerVisitedDate 101 Shiv Caf ice cream 2 1/6/2016 102 Shaam caramel icecream 2 250 2/6/2016 103 Allwyn violette icecream 1523 1/6/2016 104 Khadak cassis icecream 3 750 1/6/2016 105 Robin chocolate icecrea 5 1000 2/6/2016 pista icecream 4 800 1/6/2016 107 Sukesh mango icecream 1 150 1/6/2016 108 Praful Caf ice cream 9 2250 2/6/2016 106 Ajay st 200 2 00 CustomerCode CustomerName Product Purchase Quantity Amount CustomerVisitedDate 101 Shiv Caf ice cream 2 1/6/2016 102 Shaam caramel icecream 2 250 2/6/2016 103 Allwyn violette icecream 1523 1/6/2016 104 Khadak cassis icecream 3 750 1/6/2016 105 Robin chocolate icecrea 5 1000 2/6/2016 pista icecream 4 800 1/6/2016 107 Sukesh mango icecream 1 150 1/6/2016 108 Praful Caf ice cream 9 2250 2/6/2016 106 Ajay st