Use a Dynamic Lookup to generate the primary key during Data Warehouse dimension load.
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.
PowerCenter: Generate Sequence using Dynamic Lookup