1

Unit 6. Lookups and Reusable Transformations

Unit 6

2

Unit Objectives

• Understand when and how to use:• Lookup transformation• Reusable transformations

• Use these in a mapping

Unit 6

3

Lookup Functionality

Lookup Source

Lookup Transformation

Output with Lookup values

Input value

Input value

Lookup value(s)

Lookup condition:ITEM_ID = IN_ITEM_IDPRICE <= IN_PRICE

Unit 6

4

Lookup Transformation

Passive

Ports• Mixed• Check O column for

lookup values required

Usage:• Returns related values

(or else nulls)

Returns values from a database table or flat file associated with a given input value

Specify lookup condition

Unit 6

5

Lookup Transformation Properties

Unit 6

6

Reusable Transformations

• Create in Transformation Developer

• Or create in Mapping Designer and ‘promote’

• Listed in Transformations node of navigator

• Drag and drop into any mapping

Unit 6

7

Unit 6 Lab A Load Employee Staging Table

employees_central.txt,employees_east.txt,employees_west.txt(flat files)

STG_EMPLOYEES(Oracle table)

salaries.txt

Lookup

Create a mapping using:• Lookup transformation• Reusable Expression transformation

Create/run a workflow for this mapping using a file list

StagingAreaSources

Unit 6

8

Lab A Review

• What did we accomplish with these labs?

• Questions?

Unit 6

9

Unit 6 Lab B Load Dates Staging Table

Create a mapping using an Expression transformation with:• Date function• Variable ports

Create/run a workflow for this mapping

dates.txt (flat file)

STG_DATES(table)

Source StagingArea

Unit 6

10

Lab B Review

• What did we accomplish with these labs?

• Questions?

Unit 6

11

Unit 6 Quiz

1. When would you use the Lookup transformation?

2. What is a Lookup condition?

Unit 6