data mapping

Posted on 2015-02-15
Last Modified: 2015-03-19
Hi ,

I would like to know how to prepare a data mapping document for ETL process.  In order to prepare data mapping document, one has to know the source  and target database. I have seen data mapping documents prepared in Excel.Are there special tools such as ETL software Informatica  or others that are used in creating data mapping document?. Is it automated or manual effort in creating this?. Please let me know where to start. I might soon be asked to work on this , but not quiet sure of the best approaches.Thanks in advance.
Question by:techEverest
  • 2
  • 2
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 250 total points
ID: 40612499
When migrating data from one database to another, of a different structure, the critical aspect is understanding how the data is organized in each system.  I generally do this manually, by reviewing the data in each system and identifying the similarities first.  

The other critical piece is identifying the primary and foreign keys in each of the database tables, and how the table (within each system) relate to the other tables within that database.  I generally do this by importing the data into SQL Server and developing relationship diagrams.

But ultimately, I generally do the documentation of mapping fields from one database to another using Excel, which allows me to paste diagrams as images,  list all of the fields in each table and align them with one another, and add comments anywhere within a spreadsheet.  I use separate sheets for each table or logical representation of an entity.
LVL 23

Assisted Solution

David earned 250 total points
ID: 40612693
Adding to Dale's summary, I raise the details of mapping the data types, national language settings (NLS such as date format and character set), and any encryption requirements.  I concur with his home-grown approach as you didn't mention any specific hardware platforms.

There are of course GUI modeling tools such as Dell/Quest TOAD,  and even Oracle's SQL*Developer, which handles most all of the prevailing SQL variations.

In a prior project, I worked on a ETL team that had to migrate several hundred legacy streams into Oracle e-Business Suite.  Oracle Application Framework (OAF) was the tool for this -- particularly when the customer changed requirements and demanded an authenticity check.

Author Comment

ID: 40674373
Thanks for your help.
LVL 23

Expert Comment

ID: 40674543
If you were able to reach an answer on your own, please include.  Otherwise please award points and close this ticket.

Author Comment

ID: 40674865
I tried giving points to both the above folks from my office computer but the browser just froze

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question