Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

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.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s