Altova MapForce 2007

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 maynot have similar schemas. Creating custom code or an XSLT file to do this canbe very tedious and time consuming. Altova MapForce 2007 helps solve thatproblem. MapForce allows you to take XML, SQL databases, EDI, and Web servicesand visually map the data fields from one source to another. Once that visualmapping has been accomplished, you can migrate the data or generate source codeto 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 focuseson the Enterprise Edition.

Getting Started

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

I started with a simple test of migrating data from oneXML file to another. First, I opened my source XML schema definition andMapForce asked me if I wanted to include a sample XML file in the mapping. Thetool automatically associates the source XSD and XML file when you do this. Withthe sample data XML file you can preview the output, which I recommend if youdon 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 todesignate the root element; this constitutes the minimum requirements to do amapping.

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 fieldsfrom the source schema to the target schema. It s nice to see the mappingtaking place right before your eyes. It s much more difficult to forget tomap something because you have lines going back and forth indicating a mappinghas taken place. Because it expedited the process, I liked the auto connect feature that automatically maps fields that are the same (you can also disablethat feature if you don t like it). Simply drag a node from the source to thetarget and matching nodes are automatically mapped. Once the mapping was inplace, I clicked a tab in the lower left to view the output and it wasexactly 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 nodeof the XML file. This time, however, I wanted to see what manipulation I coulddo to the data as it s mapped to the target. I was impressed with how much datamassaging I could perform. There are simply too many to list here, but if you haveto manipulate data (string or mathematically), chances are good MapForceprovides a function block to do it. To use a function block, simply drag it outbetween the source and target and then map fields to the function block. I alsocould concatenate data, which is a great thing. How many times have you neededto merge fields because they are combined in the target database or file? Icould parse data and pull out substrings, as well, before merging the data. I alsotested a conditional function: If the XML data contained a certain keyword, Ididn t want it transferred. I was able to do all this visually and test that itworked with no problem. I generated an XSLT file so I could simply pass my XMLthrough it.

MapForce supports all the major databases, including SQLServer, Oracle, Sybase, MySQL, DB2, and Access. I went back to my previous XMLmapping and instead decided to map from the XML file to a database. I clickedon the database icon and selected SQL Server. MapForce asks for the connectionstring information (it saves the connection string information for reuse, whichis nice). I selected the tables I wanted to map and began visually mapping fromthe 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 thedocumentation is the use of arrows to indicate parent-child relationshipswithin a treeview. An arrow pointing to the left indicates that table is achild of the table above it; an arrow pointing to the right indicates thattable 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 simplymust insert it into a new or existing project. I modified the code so I couldpass any XML file that had the same schema and the code worked just fine. Thesource code generated is easy to follow and well structured. But because datadoesn t always behave as it should, I decided to go back and add exceptionhandling to my mapping.

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

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

Flat Out Flexible

The last feature I tried was FlexText. The purpose of FlexTextis to let you migrate data from a flat text file to any of the supported outputformats. Using FlexText, you can ignore text, parse out sections of text, andeven insert text. I had a Microsoft Excel spreadsheet that I had exported to aCSV format. I wanted to put the data into my database, but someone had put lotsof comments in the file in between the real data. That made it impossible toconvert the CSV to XML, or put it in a database without writing custom code or manuallyediting the data. I was able to use the FlexText tool to create a MapForceFlexText template that I could use to create a mapping.

Open your text file in MapForce; it will ask if you wantto graphically build a MapForce FlexText template. I responded yes, then beganto parse portions of the text file. The result of each manipulation is storedin a block so you can visually see what you are working with. You can furthermanipulate that data with the results being stored to the left of the originalblock. In essence, the result of each manipulation is displayed to the right. Whenyou ve done a lot of manipulations, it s not quite obvious that you can use theOverview window to move around the work you ve already done, but you quicklyget 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 yourmappings and migrate your data into any of the supported formats. It s nevereasy to parse unstructured text, but the FlexText tool allows you to visuallydo just that.

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

Conclusion

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

David Mack is aSenior Software Engineer for Northrop Grumman-TASC. He has 12 years ofdevelopment experience. He loves classic cars and enjoys a 10 handicap on thegolf course.

Rating:

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

Price: EnterpriseEdition, US$999