Extract, Transform, and Load (ETL) in AdventureWorksDW2012 Assignment

Assignment Task

Prerequisite: Execute the table script provided with the assignment to create the required table(s) in AdventureWorksDW2012.

1. Select the following fields from the table DimProduct and use expression task to pass the filter condition:

a. ProductKey

b. ModelName

c. Color

Also, filter the data on columns Color='NA' and Status='Current'

2. Use Bulk Insert task to pull below fields from FactReseller.txt file into FactReseller in the database

a. ProductKey

b. UnitPrice

3. Using Merge Join task, join the datasets from the dimension and fact table FactReseller on ProductKey column. Verify that following columns are present in output.

a. ProductKey

b. ModelName

c. Color

d. UnitPrice

4. Using Aggregate task, select the average of measure [UnitPrice] grouping it by ModelName and Color. Rename the column with prefix 'Avg.

5. Save the result in text file (Module7_Output.txt) with following columns in the order as mentioned below:

a. ModelName

b. Color

c. Avg_UnitPrice

The data in the text file must be sorted on the columns ModelName and Color . Comma separated column names must be present in the first header row.

6. Using script task calculate the number of rowset in output of step 5 without including header row and write it in the last line of the output file (Module7_Output.txt) file. Note: Outpout should be in the following format like Count Of Rows.

This IT Computer Science has been solved by our PhD Experts at My Uni Paper.