Data Mapping and Transformation Overview

Data entering an Arc Flow often needs to be transformed into a different format before it can be sent to its ultimate destination. Arc supports mapping data into many standard formats to integrate data from disparate systems.

This page covers key concepts for designing a Flow that incorporates data mapping or transformation.

What is a Data Transformation Flow?

A key part of integrations is transforming data from the format used by one system into the format used by another.

For example, a trading partner sends an EDI file, starting an Arc Flow. The EDI data needs to be inserted into a database, but first it must be transformed from the EDI format (e.g. X12, EDIFACT) into a format the database understands.

Since Arc integrates many different systems, it is not feasible to have a direct translation mechanism between every possible pair of data formats. Instead, Arc streamlines data transformation by using XML as a common intermediary format.

The XML Map Connector performs the actual transformation/mapping step once the data has been converted to XML.

Flow Overview

A full transformation Flow typically has this structure:

• A Connector translates the source data format into XML
• The XML Map Connector maps this XML into the desired XML structure
• The mapped XML is translated into the destination format

This three-part Flow should be approached from the outside-in, so the source and destination should be modeled as XML first, then the XML Map Connector establishes a mapping relationship between the two XML structures.

Database Connectors already model input and output as XML, so transformation Flows involving these connectors do not require an extra step to convert the data format into or out of XML.

Modeling Source and Destination as XML

Data transformation Flows always have a starting (source) format and an ending (destination) format. The job of the Flow is to convert data from the source format to the destination format.

Arc models both the source and destination formats as XML so that transforming formats is simply a matter of mapping one XML structure onto another. At a high level, data transformation has two components:

• Connectors that generate an XML model of the source and destination formats
• A connector that maps the source XML model onto the destination model

Converting Files To and From XML

Arc includes many connectors that convert files to XML and vice versa, including:

• X12 Connector
• EDIFACT Connector
• CSV Connector
• JSON Connector
• Flat File Connector

These connectors can perform translation in both directions; in other words, not only do these connectors model file formats as XML structures, they can convert those same XML structures back into the original file format.

For example, an X12 Connector converts X12 documents into XML with specific elements: Interchange headers, Functional Group headers, and a Transaction Set with various data elements. Any XML file that adheres to the XML structure for an X12 file can be translated back into X12.

Upload Test File

Many connectors that translate to and from XML provide the 'Upload Test File' feature. Uploading a test file tells the connector to build an internal XML model of the file, which can be accessed later during the mapping/transformation step (i.e. by the XML Map Connector).

The 'Upload Test File' feature is available via the More dropdown button in the Input tab of any translation connector.

For example, imagine that an EDIFACT document needs to be converted to a database INSERT. First, navigate to the Input tab of an EDIFACT connector, next select 'Upload Test File' from the More dropdown, then navigate to a sample EDIFACT document. The EDIFACT Connector will model the EDIFACT document as XML, and when this EDIFACT Connector is connected to an XML Map Connector in the Flow, the XML Map Connector will automatically read this XML model.

Note that this 'Upload Test File' feature is never required, since sample files can always be manually converted into XML (via the standard 'Upload File' option in the Input tab) and uploaded in an XML Map Connector. The 'Upload Test File' feature is simply a way to streamline this process.

Database Queries as XML

Arc models database input and output as XML. When a user creates an Input Mapping or Output Mapping in an Database Connector (or similar connectors like MySQL, SQL Server, CData), the connector builds an XML model of the resulting database query.

Since database data (both INSERTs/UPDATEs and SELECTs) is automatically modeled as XML, no additional steps are required in a transformation Flow to explicitly translate database input/output into XML.

Accessing XML Models of Database Queries

After creating an Input Mapping or Output Mapping in a Database Connector (or similar connector), the resulting XML model can be accessed by clicking on the 'code view' icon (</>) to the right of the mapping.

However, it is usually not necessary to view or modify the 'code view' XML model directly. Database Connectors make these XML models available in the transformation step automatically, as will be discussed in the Template Files section below.

XML Map Connector as the Transformation Workhorse

Once both the source and destination data formats have been converted to XML, the XML Map Connector performs the critical step of mapping one XML structure onto the other.

The XML Map Connector provides a visual designer with drag-and-drop controls to simplify the process of establishing a mapping relationship. The visual designer is populated with a collapsible tree-like model of both the source and destination structures. Dragging XML elements form the source onto the destination establishes a mapping relationship between the two elements.

The process of mapping XML is described briefly below, and the full feature set of the XML Map Connector is detailed in the XML Map Connector documentation.

Template Files

The XML Map Connector requires a source XML file and destination XML file as templates. The structure of these template files are used to populate the visual designer so that the mapping can be established within this designer.

Auto-Detect Template Files

When an XML Map Connector is connected to other connectors in the Flow (in either direction), then the XML Map Connector will attempt to detect any XML templates stored by these adjacent connectors.

Connectors will generate internal XML templates from two sources:

• The 'Upload Test File' feature in translation connectors (e.g. X12, EDIFACT, CSV, JSON)
• Input Mappings and Output Mappings in Database Connectors (and database-like connectors, e.g. MySQL, SQL Server, CData)

If an XML Map Connector detects any uploaded Test Files or configured Input/Output Mappings in adjacent connectors, it will make them available in the Source File or Destination File dropdown menus.

Note: If the expected template file is not showing up in the XML Map Connector's settings, ensure that you have saved the changes to the Flow via the blue save icon in the bottom right of the Flows page. Otherwise, the connections between connectors may not be recognized.

Manually Upload Template Files

In addition to the approach of automatically detecting source and destination template files, you can manually upload an XML file as the Source File or Destination File. These files should represent the input or output structure for the mapping.

For example, a sample X12 document could be passed through a configured X12 Connector, then the output from that connector could be downloaded and manually uploaded as the Source File. The 'Upload Test File' feature in the X12 Connector is intended as a shortcut for this specific procedure.

Creating the Mapping

Once the Source File and Destination File have been configured, the XML Map Connector's visual designer will show the tree structure of these files.

Drag an element from the source onto an element in the destination to establish a mapping relationship between the two elements. There are two types of mapping relationships, Value mappings and Foreach mappings.

Foreach Relationships

When an XML element has children but no value, it is considered a _Parent_ element. Dragging a _Parent_ element from the source onto a _Parent_ element in the destination establishes a Foreach relationship between the two elements.

This means that each occurrence of the source element will result in an occurrence of the destination element in the output XML. Note that this includes all of the children of the destination element.

_Parent_ elements in the source can only be dragged onto _Parent_ elements in the destination.

For example, when mapping from an EDI document to a database insert, there may be multiple elements that represent a Purchase Order in the source XML. Each Purchase Order should result in a new INSERT into an 'Orders' table, so there should be a Foreach relationship between the Purchase Order in the source and the 'Orders' table insert in the Destination. Drag the _Parent_ element representing a Purchase Order onto the _Parent_ element representing an 'Orders' insert to establish this relationship.

More information on Foreach relationships, including examples, is provided in the XML Map Connector documentation page.

Value Mappings

When an XML element has a value but no children, it is considered a _Leaf_ element. Dragging a _Leaf_ element from the source onto a _Leaf_ element in the destination establishes a Value mapping between the two elements.

This means that the value in the source element will be parsed from input files and used to populate the destination element in output files. Note that these Value mappings should only be created once the Foreach relationships have already been established.

More information on mapping _Leaf_ nodes can be found in the XML Map Connector documentation page.

Advanced Mapping Features

Sometimes, values from the input file need to be formatted or manipulated during the mapping process. The XML Map Connector provides Formatters, Conditionals, and even Custom Scripts to satisfy specific mapping conditions.

For information on the extensive feature set within the XML Map Connector, please see the XML Map Connector documentation page.

Next Steps

To get started on your own custom data Flow, you can download Arc here. The application comes with a free, fully-functional 30-day trial license, so you can test out the data transformation and mapping capabilities right away.

Here in the knowledge base you can also find articles on Getting Arc Up and Running, Creating Your First Flow, and more.