Learning ETL – Mapping
Posted by Dylan Wan on January 17, 2007
Column level mapping is to define the transformation rule that maps columns from the source datastore to on of the target datastore column.
Many ETL tool nowady can automatically map a column from the source to one in the target if they have the same name. Most of the ETL tool also allow you to drag and drop column from the source to the target to define the mapping.
Here are some types of mapping:
1. Map the target to a constant value
This is used if the target is always populated with a value. You may do so if the target column is a required column and the value is set to a default when the target data is populated from a given source datastore.
2. Map the target column from a source column
The source datastore can have one to one relationship with the target datastore, or can be a parent table when the data is denormalized into the target.
3. Use the expression to transform one ore more source column to a single targte column.
For example, you may use concatnation to add both first name and last name columns from a target datastore into the person name column in the target table.
4. Use the aggregate function
This mapping is to populate the summary data into the target column.