asp:review

Altova MapForce 2007

Powerful Tool with Many Features Is Worth the Learning Curve

By David Mack

If you ve worked around databases or XML files for a while, you ve probably run into the problem of having to merge data sources that may not have similar schemas. Creating custom code or an XSLT file to do this can be very tedious and time consuming. Altova MapForce 2007 helps solve that problem. MapForce allows you to take XML, SQL databases, EDI, and Web services and visually map the data fields from one source to another. Once that visual mapping has been accomplished, you can migrate the data or generate source code to do the migration at a later time. Source code can be generated in C++, C#, Java, and XSLT 1.0 and 2.0. There are three versions of MapForce: Standard, Professional, and Enterprise. This review focuses on the Enterprise Edition.

Getting Started

The first thing I did was to look at the documentation and the Web site associated with the tool. I highly recommend checking out the flash demo for MapForce (available on the Altova Web site); it s a quick way for you to get a feel for how powerful this tool is. Also, I recommend you browse the discussion forums Altova provides on the site.

I started with a simple test of migrating data from one XML file to another. First, I opened my source XML schema definition and MapForce asked me if I wanted to include a sample XML file in the mapping. The tool automatically associates the source XSD and XML file when you do this. With the sample data XML file you can preview the output, which I recommend if you don t do it, you can t be sure your mapping was successful. With my source set up, I opened another XSD file to be used as the target schema. MapForce asked me to designate the root element; this constitutes the minimum requirements to do a mapping.

Figure 1: MapForce in action.

You can expand and collapse the fields you want to map, which is particularly useful for a large XML file. I quickly dragged fields from the source schema to the target schema. It s nice to see the mapping taking place right before your eyes. It s much more difficult to forget to map something because you have lines going back and forth indicating a mapping has taken place. Because it expedited the process, I liked the auto connect feature that automatically maps fields that are the same (you can also disable that feature if you don t like it). Simply drag a node from the source to the target and matching nodes are automatically mapped. Once the mapping was in place, I clicked a tab in the lower left to view the output and it was exactly what I wanted. It took 15 minutes of playing around to accomplish this, which was definitely faster than doing it manually.

Delving Deeper

I wanted to expand my first example and map another node of the XML file. This time, however, I wanted to see what manipulation I could do to the data as it s mapped to the target. I was impressed with how much data massaging I could perform. There are simply too many to list here, but if you have to manipulate data (string or mathematically), chances are good MapForce provides a function block to do it. To use a function block, simply drag it out between the source and target and then map fields to the function block. I also could concatenate data, which is a great thing. How many times have you needed to merge fields because they are combined in the target database or file? I could parse data and pull out substrings, as well, before merging the data. I also tested a conditional function: If the XML data contained a certain keyword, I didn t want it transferred. I was able to do all this visually and test that it worked with no problem. I generated an XSLT file so I could simply pass my XML through it.

MapForce supports all the major databases, including SQL Server, Oracle, Sybase, MySQL, DB2, and Access. I went back to my previous XML mapping and instead decided to map from the XML file to a database. I clicked on the database icon and selected SQL Server. MapForce asks for the connection string information (it saves the connection string information for reuse, which is nice). I selected the tables I wanted to map and began visually mapping from the XML to my database. The tables in your mapping are displayed in a treeview. One thing that is subtle which you might not pick up on unless you read the documentation is the use of arrows to indicate parent-child relationships within a treeview. An arrow pointing to the left indicates that table is a child of the table above it; an arrow pointing to the right indicates that table is the parent of the table above.

Once the mapping was done, I tested it by generating C# source code. MapForce will put the source code in a directory for you; you simply must insert it into a new or existing project. I modified the code so I could pass any XML file that had the same schema and the code worked just fine. The source code generated is easy to follow and well structured. But because data doesn t always behave as it should, I decided to go back and add exception handling to my mapping.

Using the exception function built into MapForce, I could monitor a condition and throw an exception if it was met. I regenerated the code and the exceptions I created visually were also included in the source code this time. I repeated the process of putting it in a .NET project, and again met with success.

Next, I wanted to migrate data from one database to another, but I didn t want to do a full migration. Some of the data wasn t appropriate for that table. I was pleased that MapForce provides the SQL-Where function to include a custom query. To use the SQL-Where function, click on the toolbar icon that looks like a database with a filter in front. Right-click on the SQL-Where function and simply put your SQL code in there. You can even pass parameters to your SQL with this function block. The way you map the data is the same, but the dataset that is migrated is the result of the query. This is a very cool feature (but is not available in the Standard Edition). I generated the C# code again and copied it into another .NET project, with similarly successful results.

Flat Out Flexible

The last feature I tried was FlexText. The purpose of FlexText is to let you migrate data from a flat text file to any of the supported output formats. Using FlexText, you can ignore text, parse out sections of text, and even insert text. I had a Microsoft Excel spreadsheet that I had exported to a CSV format. I wanted to put the data into my database, but someone had put lots of comments in the file in between the real data. That made it impossible to convert the CSV to XML, or put it in a database without writing custom code or manually editing the data. I was able to use the FlexText tool to create a MapForce FlexText template that I could use to create a mapping.

Open your text file in MapForce; it will ask if you want to graphically build a MapForce FlexText template. I responded yes, then began to parse portions of the text file. The result of each manipulation is stored in a block so you can visually see what you are working with. You can further manipulate that data with the results being stored to the left of the original block. In essence, the result of each manipulation is displayed to the right. When you ve done a lot of manipulations, it s not quite obvious that you can use the Overview window to move around the work you ve already done, but you quickly get the hang of it. Once you get the data the way you want, save the template, then open the template in the MapForce application. From there you can do your mappings and migrate your data into any of the supported formats. It s never easy to parse unstructured text, but the FlexText tool allows you to visually do just that.

There is definitely a learning curve associated with FlextText, but it beats trying to parse flat text files programmatically. I recommend you look at the flash demo on the Altova Web site to get an idea of how powerful this feature is. Keep in mind, however, that this functionality isn t available in the Standard Edition.

Conclusion

I do a great deal of data integration in my government-contracting job. The government is constantly trying to get data from one database into another. This tool is absolutely fantastic for that. It does take time to learn but it s a powerful tool with many features, so you have to expect that. However, the learning curve is absolutely worth it; it pays off when you start to migrate data in days instead of weeks. Even if you need customized logic, you can still use MapForce as a starting point. It takes the tedious process of migrating data and lets you do it visually. When you do that visually, the chances of you omitting something decrease and you tend to better see the big picture. If you have to migrate data from various sources (especially with different schemas), download the free trial and put MapForce to the test.

David Mack is a Senior Software Engineer for Northrop Grumman-TASC. He has 12 years of development experience. He loves classic cars and enjoys a 10 handicap on the golf course.

Rating:

Web Site: http://www.altova.com

Price: Enterprise Edition, US$999