Use Join to Integrate Data in Any Format

Join is a powerful SQL operation implemented across most database types and familiar to database users. Join is typically used to select and combine information from multiple database tables.

Altova MapForce includes a join component for data mapping that works like a SQL join for database tables and extends data integration functionality by empowering users to join data trees of any data format. Anyone familiar with join operations for database tables will find the MapForce join component especially intuitive. A join operation in MapForce can even combine two different data formats and produce output in a new format altogether.

Join Data from Database Tables

Of course, the MapForce join component works with database tables, as seen in the mapping here:

The addresses table in this database uses a foreign key to link each address to the primary key for a person in the users table. The definition of the join condition for this mapping uses these keys:

This mapping uses only the key relationship to define equality, but note the Add Condition button at the top right to further define a more complex join.

The final output of this database mapping is a text file in CSV format, and the result is further refined by the SQL-where/order component and its associated input. The input condition is the “where” portion of the SQL-where/order and requests home addresses only:

The final output order is defined in the SQL-where/order properties dialog to produce a list in in last name sequence:

Here is a view of the final CSV output of the database mapping:

When applied to databases, the join component in MapForce produces SQL JOIN statements, which is critical for fast performance.

Join Data Trees of Any Format

In the data mapping below, information from two XML files is joined to create a contact list for a group of people. The output is written to a JSON file.

The condition element at the bottom of the join component defines how the files are combined. Our data mapping needs to match FirstName and LastName from each XML input file to connect each street address to the correct email and phone number. Clicking on the key icon next to the condition element opens a dialog window where the condition is defined.

The image below shows two XML input files at the left and center, and the JSON output of the mapping:

Note that the first input file contains four entries, but the second input file only provides three matches, which are then written in the JSON output file. Since there is no match to provide the address for the fourth name, it is not included in the output.

Accelerate Performance for Recurring Transformations with Join

After any MapForce mapping is designed and tested, it can be executed on demand by MapForce Server to automate business processes that require repetitive data transformations.

The MapForce Server Accelerator Edition contains several optimizations for high-performance server platforms, including multi-threaded execution of individual data mappings, and optimization for execution of data mappings that include the new MapForce join component.

Our testing has shown some data mappings perform up to 70 times faster* when run by the MapForce Server Accelerator Edition.

There are often several possible mapping designs for any particular data integration challenge, and similar results could be achieved using filters, but the join component makes the data mapping easier and more clear, and generates results much faster. Download a free trial to check it out for yourself!

* Performance results based on Altova internal tests. Your results may vary.