Skip navigation
Contact Us

Additional Information

Mapping : Generate Sequence using Dynamic Lookup

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

Posted by:Vipul Jain
Attributes:
Price: Free
Action:


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 block as part of the Bundle : Various Transformations - 2.


Video :


PowerCenter: Generate Sequence using Dynamic Lookup



  • PowerCenter version 9.1 and 9.5


Comments (0)