Lookup Tables

A Lookup table is an object that is added to a Table Population to get information from a source DB table, LU table or Reference table. A lookup returns one record for each input record. The matching SQL query is generated for a Lookup table by Fabric:

Input fields are used for the WHERE clause of an SQL statement.
Output fields are used for the SELECT statement of an SQL statement.

The input in a Lookup table is optional if the Lookup has only one record. For example, when a Lookup is created based on the Customer LU table and the Customer is the Root Table of the LU. In this case the Customer LU table will only have one record.

How Can I Add a New Lookup To a Table Population?

Go to Project Tree > Logical Units > [LU Name].
Click Tables > [Table Name] > [Table Population].
Click the Objects tab in the right panel of the Table Population working area.
Do either:
- To create an LUI Table Lookup or Reference Table Lookup:
  Click LUI Tables Lookup / Reference Tables Lookup to display the list of tables, select the table and drag it to the working area.
- To create a Database Table Lookup:
  Click Database to display the list of tables, select the table and drag it to the working area and then click Create as DB Table Lookup.
Connect the Lookup’s Input and Output fields to the table columns.
- Optional: connect the Lookup Input fields to the Source Table.
- Connect the Lookup Output fields to the Target.
Right click the Lookup and select Minimize to display only the Lookup’s connected fields.
In the Properties Tab (right panel of the Table Population working area), click the dropdown list and select the action for when a Lookup Value is not found.

Notes

It is recommended to create a Lookup based on an LU table and not a DB table in order to improve performance. The reason is that an LU table is usually much smaller than the source DB table, since it includes only the data which is relevant / applicable for an LU. In addition, the LU table is kept in the Fabric memory and is faster to retrieve.
It is recommended to create an index on the LU table for the Input fields in order to improve performance of the Lookup table’s Search.
When creating a Lookup based on an LU table, check that the Lookup object is populated before the current table. Which means that the table has a higher execution order than the Lookup object.

Lookup Properties Tab

The following information is displayed in the Lookup Properties:

Main	Non-editable fields: ID, generated by Fabric. Name, displays the lookup name. Type, displays the lookup type: Table, when the Lookup is based on an LU table. DB object, when the Lookup is based on the source DB or reference table. Is Reference, indicates whether the lookup is a reference table or not. Editable fields: Alias, displays the Lookup alias.
Lookup Not Found	Dropdown list of Fabric actions when the Lookup value is not found: Reject Record (default), the record is rejected and is not inserted into the target table. Reject Instance, (for critical errors), the entire LUI is rejected and is not loaded into the Fabric database. For example, you can define that if no Billing Address is found for a Customer when the Customer is a Root Table of the LU to reject an entire Customer instance. Continue, ignore an error and load the record. Used when the lookup value is defined as optional in the target table. Report and Continue, reports a message to Fabric log file and continues with the load. You can check the issue later using the reported message.
Misc.	Non-editable fields, applicable only for source DB table lookups: Connection Name and Owner
Open Lookup Object	Link to open the Table Schema window.
Inputs Default value	User-defined default values for each Input field in the Lookup. Used when there is no link connected to the field or when a Null value is received.

Lookup Tables

Input fields are used for the WHERE clause of an SQL statement.
Output fields are used for the SELECT statement of an SQL statement.

How Can I Add a New Lookup To a Table Population?

Go to Project Tree > Logical Units > [LU Name].
Click Tables > [Table Name] > [Table Population].
Click the Objects tab in the right panel of the Table Population working area.
Do either:
- To create an LUI Table Lookup or Reference Table Lookup:
  Click LUI Tables Lookup / Reference Tables Lookup to display the list of tables, select the table and drag it to the working area.
- To create a Database Table Lookup:
  Click Database to display the list of tables, select the table and drag it to the working area and then click Create as DB Table Lookup.
Connect the Lookup’s Input and Output fields to the table columns.
- Optional: connect the Lookup Input fields to the Source Table.
- Connect the Lookup Output fields to the Target.
Right click the Lookup and select Minimize to display only the Lookup’s connected fields.
In the Properties Tab (right panel of the Table Population working area), click the dropdown list and select the action for when a Lookup Value is not found.

Notes

It is recommended to create a Lookup based on an LU table and not a DB table in order to improve performance. The reason is that an LU table is usually much smaller than the source DB table, since it includes only the data which is relevant / applicable for an LU. In addition, the LU table is kept in the Fabric memory and is faster to retrieve.
It is recommended to create an index on the LU table for the Input fields in order to improve performance of the Lookup table’s Search.
When creating a Lookup based on an LU table, check that the Lookup object is populated before the current table. Which means that the table has a higher execution order than the Lookup object.

Lookup Properties Tab

The following information is displayed in the Lookup Properties:

Main	Non-editable fields: ID, generated by Fabric. Name, displays the lookup name. Type, displays the lookup type: Table, when the Lookup is based on an LU table. DB object, when the Lookup is based on the source DB or reference table. Is Reference, indicates whether the lookup is a reference table or not. Editable fields: Alias, displays the Lookup alias.
Lookup Not Found	Dropdown list of Fabric actions when the Lookup value is not found: Reject Record (default), the record is rejected and is not inserted into the target table. Reject Instance, (for critical errors), the entire LUI is rejected and is not loaded into the Fabric database. For example, you can define that if no Billing Address is found for a Customer when the Customer is a Root Table of the LU to reject an entire Customer instance. Continue, ignore an error and load the record. Used when the lookup value is defined as optional in the target table. Report and Continue, reports a message to Fabric log file and continues with the load. You can check the issue later using the reported message.
Misc.	Non-editable fields, applicable only for source DB table lookups: Connection Name and Owner
Open Lookup Object	Link to open the Table Schema window.
Inputs Default value	User-defined default values for each Input field in the Lookup. Used when there is no link connected to the field or when a Null value is received.