Overview of the (DTC) Data Transformation Component

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.

top-image

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

1-dtc1-V2

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

2-dtc2-reformat-V2

  1. The DTC accepts any type of data as input
  2. The input data flows into the receiving tool’s DTC mapping and transformation section
  3. It is then mapped by the DTC to an input XML schema, which is presented to the tool
  4. 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.

3-dtc3-reformated

4-dtc5

The tool opens with the General tab displayed. Selecting the Mapping tab displays the Mapping window.

5-dtc4-reformated-V2

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.

6-dtc6

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

7-dtc7

3 – Tool Input

The Tool Input pane displays the transformed tool input data in the form of an XML schema.

8-dtc8

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.

9-dtc9

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

11-functions-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

12-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

13-functions-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

14-functions-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

15-dtc11-reformated-V2

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

16-vbs-mappingThe 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.
17-vbs-V2

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

18-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.

19-dtc16and17reformated-V2

  1. 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.
  2. Element nodes are XML tags which get populated through the mapping action. An element node may hold one or more attribute nodes.
  3. 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.

20-dtc20-reformated

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.

21-dtc21-reformated

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.

Related Articles

Transform functions – External lookup

What are lookups used for? When data is imported from one system into another, the source data has to be compatible with the destination system. Where two systems are being integrated, a lookup may be used to provide translation so that they are compatible.…

Read More
BPA-8162

Transform functions – Internal lookup

What are lookups used for? When data is imported from one system into another, the source data has to be compatible with the destination system. Where two systems are being integrated, a lookup may be used to provide translation so that they are compatible.…

Read More
BPA-8163

Transform functions – Fixed/Dynamic Data

The data functions in the DTC Among the pre-defined functions provided in the Data Transformation Component (DTC) to support data transformation, there are two that are classed as “data” functions, Fixed / Dynamic Data and Run VBScript. This article will will focus on the…

Read More
BPA-8164

Transform functions – Run VBScript

The data functions in the DTC Among the pre-defined functions provided in the data transformation component to support data transformation, there are two that are classed as data functions, Fixed/Dynamic Data and Run VB Script. Here we focus on the Run VB Script function.…

Read More
BPA-8165

Transform functions – Simple looping

The DTC looping functions Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there is a group that is used to apply looping in a data transformation. There are three looping functions: Simple Split-by-type Interleaved merge We will briefly look at looping…

Read More
BPA-8166

Transforms functions – Split-by-type

The DTC looping functions Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there is a group that is used to apply looping in a data transformation. There are three looping functions: Simple Split-by-type Interleaved merge We will briefly look at looping…

Read More
BPA-8167

Transform functions – Interleaved merge looping

The DTC looping functions Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there is a group that is used to apply looping in a data transformation. There are three looping functions: Simple Split-by-type Interleaved merge We will briefly look at looping…

Read More
BPA-8169

Transform functions – Node count aggregation

The DTC aggregation functions Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there are two that are classed as aggregation functions, Node Count and Sum. The focus of this article is on the Node Count function. The purpose of the aggregation…

Read More
BPA-8172

Transform functions – Sum aggregation

The DTC aggregation functions Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there are two that are classed as aggregation functions, Node Count and Sum. The focus of this article is on the Sum function. The purpose of the aggregation functions…

Read More
BPA-8173