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.

An example of a lookup

pic1

As an example of the translation function of lookups, the data being processed may include country codes. The source data system may refer to the United Kingdom as “United Kingdom” the destination system as “UK”.

The lookup function will use the reference to apply the correct codes, translating all references to “United Kingdom” as “UK”. The lookup ensures that the source and destination systems are compatible.

External or internal?

Two types of lookups are provided by the data transformation component, external and internal.

External LookupsInternal Lookups
Large tables of data with many thousands of rowsSmall tables with relatively low numbers of records
Contain dynamic data subject to changeTend to contain stable, static data
Any database with an ODBC or OLE DB driver, where TaskCentre has connectivity and access to the databaseStored locally on the TaskCentre server, where content may be added by the task designer

Focus on external lookup

The external lookup function is useful for looking up data from within the destination system or another third-party database for translation purposes.

It uses an ODBC or OLE DB query to look up data in the destination system’s tables. The data connections used are the same as those used for the other query tools.

Alternatively, the external lookup function may be used to look up data in a completely independent table, which is not part of either the source or the destination system, but maintained by another task.

Using the external lookup function

Let’s take a look at a situation where an external lookup function could be used.

A company may wish to process orders in several currencies. Instead of using a live web site to look up exchange rates, they prefer to use their own application, which applies a smoothing algorithm to produce rates more favourable to their method of trading.

This application hosts a database separate from their ERP system. Here, an external lookup could be used to connect to the database from the DTC and read in the current exchange rate based on the lookup key.

pic2

Above you can see the DTC (for the Web Service Connector tool), showing a data transformation mapping for the scenario outlined.

The external lookup function is being applied to a transformation mapping between the data input element “CurrencyID” and the tool input element “Rate”.

pic3 In place of the Transformation Mappings pane, you can open the function’s settings in this pane by double-clicking the function icon.

pic4

Above: Note that the pane is now titled Transformation Function. After using the Transformation Mappings pane to drag functions and connectors into position between the data and tool input panes, you can use the Transformation Function pane to specify various transformation settings.

pic6-a

pic6-b

In the tables under the Input and Output tabs, you can view the input and output parameters that have been mapped in the Transformation Mappings pane, together with their mappings.

pic7

The settings on the Options tab allow you to specify what actions should take place in the event of;

  • A missing input parameter
  • More than one result returned

The default methods of handling these exceptions are shown here. You can change the settings to handle exceptions in the other ways provided.

pic8

On the Data Source tab, you select the data store or other source you wish to connect to for the external lookup.

A drop-down list contains the configured connections. Each connection’s data objects are listed below it. You double-click a data object to select it for use in the function.

Only connections already configured globally appear in the list. However, you can add configurations to it by accessing the connectors (tools) from here, provided the TaskCentre client logon has been assigned permission to access them.

pic9-v2

The global connection configurations are carried out using the Database Query (ODBC) or Database Query (OLEDB) connector tools, according to your needs.

You access them from;

TaskCentre > System > Tools> Input > Database Query (ODBC)

or

TaskCentre > System > Tools > Input > Database Query (OLEDB).

pic10

The Columns tabbed page, shown above, lets you select the database fields that you wish to map to the function input and output.

There are drop down lists which contain all the available fields from the selected table. Here you can see them mapped to the input and output of this function.

It is possible for a function to have many inputs but normally just one output.

pic11

The query tab, shown above, lets you inspect and test the SQL query that has been generated from the configuration.

External Lookup Functions: In summary

The lookup function is used to provide a reference against which to check and translate input data, to ensure that it is compatible with a destination system.

TaskCentre provides two types, internal and external. Internal lookups are suitable for static data and low-volume records. They are stored locally on the TaskCentre server. Their data content can be added by the task designer. External lookups are better for dynamic data and data records containing many thousands of rows.

The external lookup may be any database where TaskCentre has ODBC or OLE DB connectivity. Global connections to databases must have been pre-configured; or they may be added at task design time provided that the client logon has been assigned permission to access the ODBC or OLE DB connector tools.

To apply an external lookup, you use the DTC mappings pane to drag connections between data input and tool input parameters. You can also open the lookup function’s settings dialogue and specify lookup settings, including input and output parameters and their mappings, exception behaviour and location of the data store.

Links to related DTC articles