data mapping

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Dale FyeConnect With a Mentor Commented:
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.
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
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.
techEverestAuthor Commented:
Thanks for your help.
DavidSenior Oracle Database AdministratorCommented:
If you were able to reach an answer on your own, please include.  Otherwise please award points and close this ticket.
techEverestAuthor Commented:
I tried giving points to both the above folks from my office computer but the browser just froze
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.