Skip navigation
Contact Us

Mapping : Generate Sequence using Dynamic Lookup

Use a Dynamic Lookup to generate the primary key during Data Warehouse dimension load.

Posted by:Vipul Jain
Price: Free

The Lookup transformation is normally used to query the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup. When a lookup is configured to use dynamic cache, the Integration Service updates the lookup cache when it processes each row based on whether it is new or existing row. A dynamic lookup also allows associating a sequence ID to lookup columns and the Integration Service generates a primary key for inserted rows in the lookup cache. When a dynamic lookup is used query a dimension target, this sequence ID can be used to generate the Dimension key.


Business Case :

Consider the Employee source which contains employee details which needs to be loaded into the dimension table Employee_Dim. Instead of using a sequence generation transformation the dynamic lookup with the Sequence ID feature can be used to generate the dimension key. You can download a mapping that demonstrates this usage.


You can download this listing as part of the Bundle : Various Transformations - 2.

Video :

PowerCenter: Generate Sequence using Dynamic Lookup

  • PowerCenter version 9.1 and 9.5

Comments (0)