OWB - Key lookup operator

About

Use the Key Lookup operator to lookup (retrieve) data from a set of data (filter,table, view, cube, or dimension …)

For example, use the Key Lookup operator

when you define a mapping that loads a cube
when you define surrogate keys on the dimension.

The Simplified Sql statement is :

select tableA.* 
from 
  tableA,
  tableB
where
  tableA.value1 = tableB.value1 and
  tableA.value2 = tableB.value2

Here, tableA is the lookup table. It's the table where we want to retrieve the data.

Articles Related

Property

You have the following options to have access to the properties of create a Key Lookup operator:

Define a new Key Lookup operator: Drag a Key Lookup operator from the Palette onto the mapping. The Mapping Editor launches a wizard.
Edit an existing Key Lookup operator: Right-click the Key Lookup operator and select Open Details.

How to define the input value (tableB)

You must go to the input connections page and do your choice with moving element from the left pane to the right pane. Here for instance, we chose for tableB three values :

sales_organisation_id
division_id
customer_id

How to define the lookup table (tableA and the lookup condition) ?

To define the end of the lookup condition, go to the lookup page.

To provide the lookup details, select values for the following:

Object that has the lookup result. Select here the object on which you want to perform the lookup (for our instance : tableA).
Lookup Condition. Specify the condition used to match the input attributes with the records in the lookup result. Here, you can defined the lookup condition.

 tableA.value1 = tableB.value1 and
 tableA.value2 = tableB.value2

Level that has the lookup result. When you select a dimension as the lookup result, you must specify which level within the dimension contains the lookup result.

For instance
In this page, we have defined this sql simplified instruction :

select customer.* 
from
   customer,
   tableB
where
  customer.sales_organisation = tableB.sales_organisation_id
  customer.division = tableB.division_id
  customer.customer = tableB.customer_id

Type 2 History Lookup

Use this page only if you selected a Type 2 SCD as the lookup result on the Lookup page. When the lookup result is a Type 2 SCD, you must specify which version of a particular record is to be used as a lookup. The options you can choose are as follows:

Use the most current record. This option returns the current record that corresponds to the attribute being looked up using the lookup condition. The current record is the one with the latest timestamp.
Specify the historic date as a constant value. This option returns the record that contains the constant value that is specified using the Date and Time drop-down lists.
Choose an input attribute that holds the historic value. This option enables you return records that pertain to a date and time that is contained in one of the input attributes. Use the Input Attribute drop-down list to select the attribute that contains the historic value.

No-match Rows (Orphan Management)

Use the No-match Rows page to indicate the action to be taken when there are no rows that satisfy the lookup condition specified on the Lookup page (Orphan Management).

Select one of the following options:

Return no row. This option does not return any row when no row in the lookup result satisfies the matching condition.
Return a row with the following default values. This option is implemented in the SQL generated with the NVL function and an outer join.

This option returns a row that contains default values when the lookup condition is not satisfied by the lookup result. Use the table below this option to specify the default values for each lookup column.

You can set the default value as an attribute of the IN group such as the below picture

Documentation / Reference

Key Lookup operator Documentation