//
you're reading...
BI, Data Warehouse

What is ETL Mapping Document ?

In my travels I get questioned a lot regarding the importance of the ETL mapping document and do I have any good examples. Throughout my career I have developed many ETL mapping documents of various formats. I have taken a few minutes to outline the basics here.

What is ETL Mapping Document :
The ETL mapping document contains the source,target and business rules information’s, this document will be the most important document for the ETL developer to design and develop the ETL jobs.

What should an ETL mapping document include:

A typical mapping document should contain the following information’s
1) Mapping indicator(Values A:ADD, D:Delete,C:Change)
2) Change description (Used to indicate mapping changes)
3) Key Indicator( Indicates whether the field is Primary key or not)
4) Source Table/File Name
5) Source Field Name
6) Source Field Data Type
7) Source Field Length
8) Source Field Description(The description will be used as a meta data for end user)
9)Business Rule
10)Target Table Name
11) Target Field Name
12) Target Data Type
13) Target Field Length
14) Comment
You can add more fields based on your requirements.

Real Time Example:
Let’s take a look at a real time example here, consider that we are getting a source file called “Employee_info” which contains employee information’s and it should get loaded into the EMP_DIM table in target.
One of the business requirement is to convert Sales –>S,Packing–>P and Transport–>T

Here is how the mapping will look like

Once the mapping is prepared, it needs to be reviewed with end user and get the sign off from the them.
Lets say that during the review they users want to add “E” before the EMP_NUM field in target.
The mapping document should be changed first, here it’s how it will look after the change

As you can see in the image that the “Mapping Indicator” column got changed from A to C and “Change description” contains change details. This should give you a good basic understanding of the form and function of an ETL Mapping Document.

 

Bonn Appetit

ldix19

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: