Unconnected Lookups
An unconnected Lookup transformation enables you to incorporate a data source The unconnected Lookup receives input from the result of an expression in another transformation.
This allows you to use an unconnected lookup similar to using a function. The parameter you pass to the unconnected lookup captures a value from another source and returns it to the mapping pipeline.
Note: You can use an unconnected lookup only with a database or flat file data source.
Functionality | Connected Lookup | Unconnected Lookup |
|---|
Input values | Receives input values directly from the pipeline. | Receives input values from an expression in another transformation. |
Cache | Static or Dynamic Cache includes all lookup columns used in the mapping. This includes columns in the lookup condition and columns linked as output fields to other transformations. | Static Cache includes all lookup/output fields in the lookup condition and the lookup/return field. |
Return values | Returns multiple values from the same row or insert into dynamic lookup cache. | Returns the specified field for each row. |
Lookup conditions | If there is no match for a lookup condition, the Data Integration Server returns the default value for all output fields. If you configure dynamic caching, the DI Server inserts rows into the cache or leaves it unchanged. If there is a match, the DI Server returns the results of the lookup condition for all lookup/output fields. If you configure dynamic caching the DI Server either updates the row in the cache or leaves the row unchanged. | If there is no match for the lookup condition, the Data Integration Server returns NULL. If there is a match, the DI Server returns the result of the lookup condition to the return field. |
Output values | Passes multiple output values to another transformation. Links lookup/output fields to another transformation. | Passes one output value to another transformation. The lookup/output/return field passes the value to the transformation calling the lookup expression. |
User-defined values | Supported | Not supported |
Unconnected Lookup Example
Unconnected lookups are commonly used to implement data warehousing requirements, such as the following scenario.
In this example, billing log files in Amazon Web Services (AWS) contain billing information that you need for dimension tables in the data warehouse. You might use an unconnected Lookup transformation to load the billing information into the data mart tables.
The following mapping shows how you might create the DIM_PRODUCT table:
The DIM_PRODUCT target table contains unique values from the billing log files for the ProductName column and you need to process only records with the RecordType= 'LineItem' and ProductName is not null.
The mapping includes these transformations:
- •Unconnected Lookup, to bring additional data into the mapping.
- •Source, to read data from the billing log file.
- •Filter, to restrict the number of incoming fields.
- •Sort, to limit the data stream to distinct rows.
- •Expression, to map to the unconnected Lookup.
- •Filter, to omit rows where the ProductName is null.
- •Expression, to create a timestamp for the load date.
- •Target, to load data to the Product dimension table.
After the mapping runs, the DIM_PRODUCT table includes these values:
Transformation Properties
The example uses these properties for the unconnected Lookup transformation:
- 1. Unconnected Lookup in the General properties.
- 2. Incoming Field, in_product_name.
- 3. Lookup Object, DIM_PRODUCT.
- 4. Lookup Condition to specify PRODUCT_NAME = in_product_name.
- 5. Return Fields PRODUCT_NAME, PRODUCT_ID, and LOAD_DATE.
The first Filter transformation restricts incoming fields with the Field Selection Criteria:
This transformation also configures a filter condition, RecordType='LineItem' AN NOT ISNULL(ProductName).
The mapping includes these additional transformations:
- •A Sort transformation with the following properties:
- - Sort: sort ProductName in ascending order.
- - Advanced: check Distinct.
- •An Expression transformation mapped to the unconnected Lookup, as shown here:
:LKP.lkp_Product(123, 'ABC')
