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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…

734 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