Solved

data mapping

Posted on 2015-02-15
5
146 Views
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.
0
Comment
Question by:techEverest
  • 2
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
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.
0
 
LVL 23

Assisted Solution

by:David
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.
0
 

Author Comment

by:techEverest
ID: 40674373
Thanks for your help.
0
 
LVL 23

Expert Comment

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

Author Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now