top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

update dynamic lookup value

0 votes
41 views

how can I update a value in a dynamic lookup in the follow manner:

say I have an integer column in the lookup and on the next lookup I get a new integer that should be added to the existing value.

how can i achieve it?

for example:

I have the empty source table

emp_id | value

row insert                        emp_id | value
1) 101, 1000  expecting insert      101    1000
2) 101, 1005  expecting update      101    2005    
3) 101, -300  expecting update      101    1705

I hope my example is clear

posted Aug 19, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

There is an option within the "Properties" tab of the lookup transformation that will give you what you need.

However, if set this can cause performance degradation within the mapping and is something I generally try and avoid setting. I am usually able to avoid setting this parameter by including more logic in the Source Qualifier SQL override, e.g. in your example to ensure I only return the most recent record for each employee.

answer Aug 21, 2014 by Shweta Singh
Similar Questions
0 votes

can somebody explain to me when does the dynamic lookup cache file is updated?

is it when the lookup transform is done or when the row source to target path has ended?

+3 votes

Using Informatica 9.1.0

Scenario

Get the Dimension key generated and inserted to the Fact table from the Fact load.

I have to load the Fact table with a dimension key along with other columns. This dimension record is created from within the same mapping. There are five different sessions using the same mapping and executes simultaneously to load the Fact table. In this case I'm using a dynamic lookup with 'Synchronize dynamic cache' enabled to get unique dimension records generated from the 5 sessions using some conditions. The dimension ID is generated using the Sequence-ID in associated expression of the lookup. When a single session alone is run it worked perfectly fine. But when the sessions were run parallely it started to show unique key violation error as random sessions tried to insert the same sequence which was already there.

To fix the issue I had to give persistent lookup cache enabled and Cache file name prefix. But I did not find this solution or this issue in any of the forums or in INFA communities. So not sure this is the right way of doing it or this is a bug of some kind.

Please let me know if you had similar issue or some different thoughts.

Thanks in advance

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...