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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 48

Accepted Solution

Dale Fye 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…

636 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