Table of Contents
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
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 Lookups||Internal Lookups|
|Large tables of data with many thousands of rows||Small tables with relatively low numbers of records|
|Contain dynamic data subject to change||Tend to contain stable, static data|
|Any database with an ODBC or OLE DB driver, where TaskCentre has connectivity and access to the database||Stored 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.
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”.
In place of the Transformation Mappings pane, you can open the function’s settings in this pane by double-clicking the function icon.
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.
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.
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.
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.
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)
TaskCentre > System > Tools > Input > Database Query (OLEDB).
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.
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.
- Overview of the (DTC) Data Transformation Component
- Transform functions – External lookup
- Transform functions – Internal lookup
- Transform functions – Fixed/Dynamic
- Transform functions – Run VBScript
- Transform functions – Simple looping
- Transforms functions – Split-by-type
- Transform functions – Interleaved merge looping
- Transform functions – Node count aggregation
- Transform functions – Sum aggregation