Converting data from one database to another can sometimes be a painful and complex process. There are tools available which provide an easier way to carry out data mappings between databases automatically. We look at a few of them here, and they don’t all cost a fortune!
Altova MapForce® 2011
Altova MapForce® 2011 is a general purpose mapping tool. It covers a whole range and types of databases. It can map data formats flat files, XML, EDI, and numerous others.
It is reputed to be able to handle and support all major types of relational databases . A graphical user interface handles the mappings for the user and makes the process efficient. The user simply drags related information from the source to the target using dragging connecting lines.
Examples of the types of databases it can support include Microsoft SQL Server, DB2, Oracle, Sybase, MySQL, Access and PostgreSQL. A fully working version can be obtained on a trial basis for free. The tool is available from: https://www.altova.com/mapforce/database-mapping.html
ETL Suite
Like Altova ETL, the software can handle migrations and mappings of databases from practically any source to target. A graphical user interface is provided to assist the process, and it is stated to be easy to use.
It can map flat files, XML, CSV, databases, EDI and numerous other file formats. Additional tools are provided such as date and string formatting, alongside numerical conversions. It is stated that the software is flexible as to the types of data it can map from source to target.
The software works by using a web-based interface called “Design Studio,” which has wizard functionality included to make the process easier. The actual validation, mapping process and any editing can all be automated using relevant scripts.
The ability to view mappings via the “preview” capability means that both source and target data can be viewed in real time as rules are changed on the fly. All the rules and source data file information is held in a central storage area, which includes all the rules and objects required for the designated mapping process.
The automated or manual process is handled by the “Run-Time Execution Engine,” which takes as its input any incoming data files and rules to work by. For enterprises, the software complies with regulations by providing an audit report of activities. The software is commonly used in Service-Oriented Architecture (SOA) environments. One very useful feature is that the software can handle multiple sources of data at the same time to be mapped to a common target database.
The ETL suite is available at https://www.adeptia.com/products/data _transformation.html
Allora Database XML Mapping
This tool is not as sophisticated as ETL, but it serves the purpose of mapping database data on a smaller scale, in that it is only designed to handle XML to relational database mappings. It is primarily intended for application developers to provide them with bi-directional access to relational databases, thus saving them the job of writing time consuming SQL scripts.
The intention of the product is that it speeds development and deployment, and transforms data in the process.
It can map XML and relational databases, and once configured provides developers with consistent and easy access to data. Although it is primarily intended for XML use only, it supports a wide range of relational databases, including databases such as Oracle, SQL Server, MySQL, Access, and numerous others. As the product specializes in XML it can also handle industry standards from all types of industries. Typical examples include AgXML, JusticeXML, UCCnet and VoiceXML.
Full support is provided during the trial period, and the product is available at: https://www.hitsw.com/products _services/xmlplatform.html
Please continue on page 2 for more on database mapping software.
Open Database Connectivity (ODBC)
Open Database Connectivity (ODBC) is not an open source or a commercial tool as such. It is a utility program that takes a source data type specified by the user, and then converts it into a target data type. This means the type of database can be independent, and if the developer decides to switch to another database type, they can be assured that ODBC will take care of the situation. ODBC is useful regardless of the DBMS used for managing data. This means that applications can be platform and database independent.
A typical implementation of ODBC would be when an application needs to read data from a database of one type, and then feed the results to a second database of a different type. This requires the use of ODBC drivers, in addition to specific database drivers. A translation mechanism sits in the middle of the databases and performs that translation.
More about ODBC can be found at: https://support.microsoft.com/kb/110093
References
Toolbox.com, Data Mapping, https://it.toolbox.com/wiki/index.php/Data _mapping
