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?
The most common way to do this Excel, but it has it's tradeoffs:
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.
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.
I would suggest to use SQL Power Doc by kendalvandyke. Its the best free tool that does a pretty decent SQL Server & Windows Documentation Using Windows PowerShell. IT is available at codeplex.
SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.
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.
In my experience, none of the ready-made applications I've tried provides enough usable information. The way I see it, we model and store information about nearly everything of value in an enterprise. Is the data itself of less value than an enterprise's other assets? Then why don't we document our data as well as we document our other assets? "Metadata repository" doesn't accurately describe what is really needed because it paints a picture of a bunch of definitions and structures that "sit there" mostly unused, which should not be the case. "Metadata application" is a much better term.
I would model your metadata the way you model your other data: think about how you want to use it, create a metadata data model or "meta-model", create the analogous schema structures, populate the tables, an use the data. Then you'll be able to not only document your metadata, but you'll be able to analyze it, discover problems and even do forecasting.
With my personal experience working with a giant healthcare organization where multiple payer and provider data comes from multiple sources and it really becomes a togh task to document and map them using excel spreadsheet. We were recommended to do a POC by our Director for AnalytiX Mapping Manager (www.analytixds.com) whom he met at a conference. We used a live use case and the results were outstanding.The solution is 100% metadata driven accessible from a web-browser. We have used it at enterprise level. It scans and organizes metadata from wide variety of databases, data models, flat files and unstructured data and identify upstream and downstream dependencies with its impact analysis and data lineage. The GUI is user friendly with its easy Drag/Drop user interface to create mapping specifications. We not only build mapping and document but also auto Generate ETL jobs for Informatica and SSIS ETL tools used internally. You can easily import your legacy spreadsheets and enforce mapping standards with built in customizable mapping templates and have full version control.
