The Lookup transformation

The Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data. You can look up flat file and relational tables. The Lookup transformation works on similar lines as the joiner, with a few differences. For example, lookup does not require two sources. Lookup transformations can be connected and unconnected. They extract the data from the lookup table or file based on the lookup condition.

Creating a Lookup transformation

Perform the following steps to create and configure a Lookup transformation:

  1. In Mapping Designer, click on Transformations and create a Lookup transformation. Specify the name of the Lookup transformation and click on OK.
  2. A new window will ask you to select Source, Target, or Source Qualifier that you wish to look up. You will get a list of all the sources, targets, and source qualifiers available in your repository. Click on the required component and then click on OK, as shown in the following screenshot:
    Creating a Lookup transformation

If the required source or target is not available in the repository, you can import it before you use it to look up. Click on the Import button to import the structure, as shown in the preceding screenshot.

The Lookup transformation with the same structure as the source, target, or source qualifier appears in the workspace.

Configuring the Lookup transformation

We have learned how to create a Lookup transformation in the previous section. Now we will implement a mapping similar to the one shown in the Joiner transformation using the Lookup transformation. We will use the EMPLOYEE Oracle table as the source and will look up the EMP_FILE flat file. Perform the following steps to implement the mapping:

  1. Drag the EMPLOYEE table as the source in Mapping Designer and drag the target.
  2. We have already created the Lookup transformation.
  3. To get the relevant data based on the matching conditions of the EMPLOYEE table and the EMP_FILE file, drag the EMPLOYEE_ID column from the EMPLOYEE table to the Lookup transformation. Drag the corresponding columns from the EMPLOYEE source qualifier and Lookup transformation to the target, as shown in the following screenshot:
    Configuring the Lookup transformation
  4. Click on the Condition tab and create a new condition, as shown in the following screenshot:
    Configuring the Lookup transformation

Setting up the Lookup transformation

When you create the Lookup transformation, you can configure it to cache the data. Caching the data makes the processing faster, as the data is stored internally after the cache is created. Once you choose to cache the data, the Lookup transformation caches the data from the file or table once. Then, based on the condition defined, the lookup sends the output value. As the data gets stored internally, processing becomes faster as it does not need to check the lookup condition in the file or database. Integration Service queries the cache memory instead of checking the file or table to fetch the required data.

When you choose to create the cache, Integration Service creates cache files in the $PMCacheDir default directory. The cache is created automatically and is also deleted automatically once the processing is complete. We will discuss what cache is later in the chapter.

Lookup ports

A Lookup transformation has four different types of ports. To view the ports of the Lookup transformation, click on its Ports tab.

Lookup ports

The ports are as follows:

  • Input Ports (I): The input ports receive the data from other transformations. This port will be used in the lookup condition. You need to have at least one input port.
  • Output Port (O): Output ports pass the data from the Lookup transformation to other transformations.
  • Lookup Port (L): Each column is assigned as the lookup and output port when you create the Lookup transformation. If you delete the lookup port from the flat file lookup source, the session will fail. If you delete the lookup port from the relational lookup table, Integration Service extracts the data with only the lookup port. This helps to reduce the data extracted from the lookup source.
  • Return Port (R): This is only used in the case of an unconnected Lookup transformation. It indicates which data you wish to return in the Lookup transformation. You can define only one port as the return port, and it's not used in the case of connected Lookup transformations.

Lookup queries

Similar to the Source Qualifier transformation, which generates a default query when you use the source as a relational database table, the Lookup transformation also generates a default query based on the ports used in the Lookup transformation. To check the default query generated by the Lookup transformation, click on the Properties tab and open Lookup Sql Override, as shown in the following screenshot:

Lookup queries

Similar to overriding the default SQL in the Source Qualifier transformation, you can override the default query generated by the Lookup transformation. If you override the default query generated by the Lookup transformation, it is referred to as a lookup SQL override.

Unconnected Lookup transformations

As mentioned, unconnected transformations are not connected to any other transformation, source, or target by any links. An unconnected Lookup transformation is called by another transformation with the :LKP function. Using the :LKP function, you can pass the required value to the input port of the Lookup transformation, and the return port passes the output value back to the transformation from which the lookup was called. A mapping using an unconnected Lookup transformation is as follows:

Unconnected Lookup transformations

In the preceding mapping, we are implementing the same scenario that we implement using a connected Lookup transformation. Perform the following steps to implement this scenario:

  1. Create an Expression transformation and drag all the ports from the Source Qualifier transformation to Expression transformation.
  2. Create an EMPLOYEE_ID input port in the Lookup transformation that will accept the value of EMPLOYEE_ID using the :LKP function.
  3. Create another AGE output port in the Expression transformation that is used to call an unconnected Lookup transformation using the :LKP function. Link the AGE port to the target. Write the :LKP.LKP_FILE(EMPLOYEE_ID) function in the expression editor of the AGE column, as shown in the following screenshot:
    Unconnected Lookup transformations
  4. Double-click on the Lookup transformation, click on ports, and make AGE the return port, as shown in the following screenshot:
    Unconnected Lookup transformations

    When you execute the mapping, row-wise data will flow from the source to the Expression transformation. The :LKP function passes the data to the Lookup transformation, which compares the data based on the condition defined in the Condition tab, which in turn returns the data from the Return port to the Expression transformation, from where the data is passed further in the mapping.

  5. Similarly, add another LOCATION output port to the Expression transformation. Then, create another Lookup transformation. We cannot use the same Lookup transformation, as the unconnected Lookup transformation can return only one port. Follow the process described here to look up for the AGE port to complete the mapping.

We have seen the implementation of connected and unconnected Lookup transformations.

Lookup transformation properties

Let's discuss the properties of the Lookup transformation:

Property

Description

Lookup SQL override

This is similar to a SQL override. When you override the default query generated by the Lookup transformation to extract the data from relational tables, it is referred to as a lookup SQL override.

Lookup table name

This is the name of the table that you are looking up using the Lookup transformation.

Lookup source filter

Integration Service will extract only those records that satisfy the filter condition defined.

Lookup cache enabled

This property indicates whether Integration Service caches data during the processing. Enabling this property enhances performance.

Lookup policy on multiple match

You can decide to choose a particular value if the Lookup transformation returns multiple values based on the condition defined. The various options available are:

  • Use First Value: Integration Service will return the first matching record.
  • Use Last Value: Integration Service will return the last matching record.
  • Use Any Value: When you select this option, Integration Service returns the first matching value.
  • Report Error: When you select this option, Integration Service gives out an error in the session log. This indicates that your system has duplicate values.

Lookup condition

This is the lookup condition you defined in the Condition tab of the Lookup transformation.

Connection information

This property indicates the database connection used to extract data in the Lookup transformation.

Source type

This gives you information indicating that the Lookup transformation is looking up on flat file, a relational database, or a source qualifier.

Tracing level

This specifies the level of detail related to the Lookup transformation you wish to write.

Lookup cache directory name

This indicates the directory where cache files will be created. Integration Service also stores the persistent cache in this directory. The default is $PMCacheDir.

Lookup cache persistent

Check this option is you wish to make the cache persistent. If you choose to make the cache persistent, Integration Service stores the cache in the form of files in the $PMCacheDir location.

Lookup data cache size

This is the size of the data cache you wish to allocate to Integration Service in order to store the data. The default is Auto.

Lookup data index size

This is the size of the index cache you wish to allocate to Integration Service in order to store the index details such as the lookup condition. The default is Auto.

Dynamic lookup cache

Select this option if you wish to make the lookup cache dynamic.

Output old value on update

If you disable this option, Integration Service sends the old value from the output ports, so if the cache is to be updated with a new value, it first sends an old value to the output ports that are present in the cache. You can use this option if you enable dynamic caching.

Cache file name prefix

This indicates the name of the cache file to be created when you enable persistent caching.

Recache from lookup source

When you check this option, Integration Service rebuilds the cache from the lookup table when the lookup is called.

Insert else update

If you check this option, Integration Service inserts a new row into the cache and updates existing rows if the row is marked INSERT. This option is used when you enable dynamic caching.

Update else insert

If you check this option, Integration Service updates the existing row and inserts a new row if it is marked UPDATE. This option is used when you enable dynamic caching.

Date/time format

This property indicates the format of the date and time. The default is MM/DD/YYYY HH24:MI:SS.

Thousand separator

You can choose this separator to separate values. The default is no separator.

Decimal separator

You can choose this separator to separate the decimal values. The default is no period.

Case-sensitive string comparison

This property indicates the type of comparison to be made when comparing strings.

Null ordering

This specifies how the Integration Service orders the null values while processing data. The default is to sort the null value as high.

Sorted input

Check this option if you are passing sorted data to the Lookup transformation. Passing the sorted data enhances performance.

Lookup source is static

This indicates that the lookup source is not changing while processing the data.

Pre-build lookup cache

This indicates whether Integration Service builds the cache before the data enters the Lookup transformation. The default is Auto.

Subsection precision

This property indicates the subsection precision you wish to set for the date/time data.

We have now seen all the details of Lookup transformations.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset