Best way to document database mappings for migrations

Question

I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this?

Excel is a very flexible way to document simple mappings but I was wondering if anybody had a specific methodology they followed or other tools they use that they could recommend?

Answer 1

The most common way to do this Excel, but it has it's tradeoffs:

• Very difficult to update the spreadsheet if either side of the migration changes (due to missing something, something changing, etc.)
• Excel is harder to use by the people implement the rules documented in it.
• Change tracking is nearly impossible if you have multiple people collaborating on the document.

I prefer to use data lineage features of data modelling tools such as ER/Studio, ERwin or PowerDesigner to track the transformations required for migration from one data store to another. I get all the benefits of RE and compares to the source and target, plus none of the tradeoffs mentioned above. It really makes a difference.

Answer 2

If you're trying to document data mappings then a spreadsheet is probably the simplest approach, although it's not great for anything but a 1:1 mapping. A few approaches are:

Excel: You can use a spreadsheet for the mappings, particularly where they are simple. If your databases are not isomorphic (i.e. they don't have a 1:1 mapping for all fields you're interested in) then documenting the mapping can be a bit more complex. If the differences are minor you can still document this in a spreadsheet by adding notes to the fields where the mapping is more complex.

CASE tool with custom meta-model A year or two ago I built an extended meta-model and some associated scripts for Sparx Enterprise Architect. This allowed me to set up diagrammatic representations of the mappings, and it had some modelling primitives that allowed you to document transformations.

Note that this requires a bit of programming - and some time poking around the COM API presented by EA and possibly the repository model. It's not all that opaque, though - much simpler than the repository model in (for example) Oracle Designer.

If you have a really complex data migration job, you can write scripts that query the model to make sure (for example) that you don't have any unmapped items. The repository report generator is also quite flexible in what you can include in a report - I didn't have too much trouble getting customised reports from it that did most of what I wanted. You can also write things that extract data from the model and dump it out into (for example) a spreadsheet.

Diagrams, including custom model elements (EA has a shape and connector definition feature) can also be made for a graphical overview of the migration spec.

The value of this approach is that you can automate some of the controls on the migration docs, and validate that you've got complete coverage of everything you need. For instance, you can also include references to data requirements in the repository model, and use specially tagged links between these and the source or destination data models.

ETL tools: If you have access to one of the best-of-breed ETL tools such as Informatica Powercentre, you could set up the mappings there. Some ETL tools are fairly self-documenting and you could also use the tool to do the migration. SSIS will do the mapping, but it's not self-documenting in this way.

What you really want is a CASE tool with the meta-model for mappings aleady set up. Unfortunately I'm not aware of such a product.

Answer 3

I have had success with using Red Gate's SQL Doc. It's simple to setup and gives good documentation. However if you are looking for something with graphical representation of tables and views and how they relate, I can't help you there.