NOTE: This article has been updated and moved to the Codeless Platforms Documentation Portal.
If you are seeing this message and have not been redirected, click: Introducing the Data Transformation Layer.
<!–
Introduction
The DTC provides a number of functions which can be applied to the mapping of connections between the data input and a TaskCentre tool input, to bring about particular transformations in the data.
An example of this is the Web Services Connector Tool where you will find the DTC in the “Mapping” tab.
This overview outlines what can be achieved with this component and contains links to articles that explain each section in greater detail.
How the DTC works
The DTC works on source data to transform it into an XML schema for use by the tool.
The source data may comprise output from a previous task step or external data. It may take the form of any object that can carry data, such as record set, XML document, variable, formula, function or constant.
The DTC maps this source data to a destination XML schema, which then becomes the input data for the tool.
A number of transform functions can be applied to the transformation of the source data, such as summing values.
The data transformation process
- The DTC accepts any type of data as input
- The input data flows into the receiving tool’s DTC mapping and transformation section
- It is then mapped by the DTC to an input XML schema, which is presented to the tool
- The tool then exposes the XML as output for the next task step
The DTC user interface
Any tool which has the data transformation component embedded will have a tab called “Mapping” visible at the upper left” corner of the tool interface.
Selecting the Mapping tab displays a window with various panes and the tools you use for mapping transformations.
The tool opens with the General tab displayed. Selecting the Mapping tab displays the Mapping window.
The Mapping window consists of four panes:
1 – Data Input
The Data Input pane displays the source data which the tool receives from the output of preceding tasks steps.
2 – Transformation Mappings
The Transformation Mappings pane displays the mappings graphically, letting you create and change mappings and combine various types of transformation functions into the mappings
3 – Tool Input
The Tool Input pane displays the transformed tool input data in the form of an XML schema.
4 – Functions
The Functions pane shows a list of the transform functions available for use in the Transformation Mappings section.
The pane can be minimised to the side bar to provide more space for the other three panes.
Where the DTC can be used
The DTC is used for transforming input data into a form for use by the tool. It is therefore ideal for situations where data from a third-party system has to be transformed before it can be written to a destination API.
Such third- party source data may have been imported into TaskCentre using the Import XML Document tool or the Import Flat File tool or may have come directly from a web service API call.
Data transformation functions
To support data transformation, a number of pre- defined functions are provided in the Functions interface panel of the DTC.
The available pre-defined data transformation functions are grouped into Lookup, Data, Looping and Aggregation categories
Lookup
Lookups are used to query another data source in order to merge or update elements in the source data or to verify that key codes exist. The DTC has two types of lookup.
External Lookup
An example of this type is checking that a customer exists in a destination system before running an update. Read more about external lookup functions.
Internal Lookup
An example of this would be translating a regional code to make it compatible with the destination system. Read more about internal lookup functions.
Data
There are two types of data function: Fixed/Dynamic and Run VBScript.
Fixed/Dynamic allows values to be assigned to any of the schema elements in the tool input data structure, where the data requirement is either a fixed value or a dynamic value provided by a task variable.
Examples of dynamic data are variables populated through TaskCentre event tools.
Examples of fixed data may be constants defined in the Web Service Connector tool configuration.
Read more about Fixed/Dynamic data functions.
Run VB Script allows source data to be transformed using the VB Script language. It may consume many inputs and use VB Script to provide reprocessed output, which can then be mapped to the tool input schema.
This is useful where flexibility in defining a transformation is required. The interface presents the VB Script language reference when viewing or editing the script.
An example is transforming a delivery address into a block of data suitable for label printing.
Read more about the Run VBScript function.
Looping
Looping functions are available as Interleaved merge, Simple or Split by type.
Loops enable you to work through the schema structure where multiple items are present against a top-level object.
Simple looping would be used, for example, to loop through all the sales or purchase orders in an XML document. In order to process the individual line items for each order, another nested simple loop would be required.
You can also use looping to split or merge data items.
Read more about Simple looping.
Read more about Interleaved merge looping.
Read more about Split-by-type looping.
Aggregation
Two aggregation functions are available: Node Count and Sum. They aggregate data values during run time.
As an example of this, the number of sales orders present in the source XML can be aggregated to produce a summary order total.
Read more about Node Count aggregation.
Read more about Sum aggregation.
DTC transformation in action
Here you can see an example of a task design that is using the DTC to perform data transformations. You can see the source and input schemas with a number of mappings between them. Not all of the mappings shown here involve transformations.
1 – Selecting the function
The transformation functions available for use in the mappings are listed here. They can be selected and dragged to the Transformation Mappings pane.
Two of the functions used in this example are visible in the mappings area, at points 2 and 6 above.
2 – Applying a data function
Here you can see a data transformation being performed using the Run VBScript data function.
3 – Source for data transform
This example shows a many- to-one mapping. The transformation is converting source data that consists of four individual address lines into a single block address.
4 – VBScript code
This shows the VBScript code used in this example of converting the individual lines into a block address.
5 – Untransformed mappings
Some data mappings, such as these, are not subject to any transformation and are simply mapped across on a one-to-one basis.
6 – A looping function
A simple looping transform has been set up to loop through all the line items of a sales order.
It is shown here mapped from the “salesOrderItems” node in the source and to the “items” node in the tool input.
The input panes
The input panes display nodes for mapping from (at the Data Input) and to (at the Tool Input). The nodes are arranged in a typical inverted tree hierarchy.
There are three levels of node which may be mapped from or to: parent, attribute and element.
- Parent nodes host data elements and attributes related to those nodes. They may contain nested parent nodes. Parent nodes are mapped when a user wishes to loop through a dataset.
- Element nodes are XML tags which get populated through the mapping action. An element node may hold one or more attribute nodes.
- Attribute nodes are XML tags which get populated through the mapping action. They may be held under element nodes or directly under parent nodes.
Saving and reusing mappings
Any mapping that has been configured with the DTC can be saved as a mapping file for later re- use. A mapping file has the extension DTLX.
You select the Export button in the mappings pane. This opens a Save As dialog box, where you choose a location and name for the file.
A saved mapping is available for use in another deployment of TaskCentre or as a template for other mappings.
To make use of one, you select the Import button. This opens an Open dialog box, where you locate and select the required DTLX file.
The DTC: In Summary
The Data Transformation Component is inherent within certain tools that require their data input to be restructured or transformed before they can work effectively. The DTC transforms the source data into an XML schema for use by the tool.
The DTC is accessed from the Mapping tab in the tool interface. Input data is brought into a mappings pane, where it is transformed into an XML schema, which the tool then uses as its input data. Mapping is done between nodes in the data and tool input panes . Nodes are arranged in a parent, element and attribute hierarchy.
Several functions are provided for applying to the mappings. They include lookup functions, which query other data sources; data functions, which assign values or use VBScript to create transformations; looping functions, which apply simple loops or merge or split data; and aggregation functions, which sum nodes or values during run times. –>
