1. Give at least three reasons why ETL functions are most challenging in a data warehouse environment. 2. Identify at least five major types of the major transformation tasks and give an example of...

Homework 3
Due 13 June 10, 2012
1. Give at least three reasons why ETL functions are most challenging in a data warehouse
environment.
Answer: Generally ETL functions are labor intensive.
1) Data extraction is more complex due to distributed source system geographically.
2) Data Transformation is difficult due to wider number of task involved.
3) Data loading is more challenging due to the factor of more time consumption.
2. Identify at least five major types of the major transformation tasks and give an example of each.
Answer:
1) Select only the specific columns to load instead of all the columns. For example,
Name, Age, RollNo, then select only Name and RollNo to load into the target.
2) Coded values can be translated (For example, if the source system stores 1 for
male and 2 for female, but the warehouse stores M for male and F for female)
3) Deriving calculated values using the mathematical formulas (For example,
sale_amount = qty * unit_price).
4) Joining different data from multiple sources & deduplication the data.
5) Aggregation
3. Describe the entity identification problem in data integration and consolidation & suggest a
resolution to this problem.
Answer: Consider three different legacy application has been developed in three different
times in a company. Each application has its own customer files. Let us assume, a single customer is
having account in these three applications. Each application is maintaining its own uniqueness in records
of different customers. This uniqueness will not be same. There is a problem of identifying which of the
records from these three applications referring to a single customer. The possible solution is to develop
complex algorithms to match records from all these three applications. So for none of the algorithms
completely determine the...