use unconnected lookup transformation in informatica level scd type-2

SCD Type 2 a new record with the new attributes is added to the dimension table. Historical fact table rows continue to reference the old dimension key with the old roll-up attribute; going forward, the fact table rows will reference the new surrogate key with the new roll-up thereby perfectly partitioning history. 

   Source table name :Customer_Test

Target Table Name:CUSTOMERS_DIM_TEST

                                                       Mapping over view 

Step 1:

          First defined the soure and target definitions in Desinger.

Step2:

create the one unconnected lookup ,It is lookup the target table and create the one input port like 

 this CUSTOMER_ID_in

1. Go the lookup properties set the Lookup policy on multiple match=Use any value
2. Lokup condition CUSTOMER_ID = CUSTOMER_ID_in
3. Lookup sql override :
5. SELECT TO_CHAR(CUSTOMERS_DIM_TEST.CUST_KEY ||','||CUSTOMERS_DIM_TEST.LOCATION) as CUST_KEY, CUSTOMERS_DIM_TEST.CUSTOMER_ID as CUSTOMER_ID FROM CUSTOMERS_DIM_TEST WHERE   CUSTOMERS_DIM_TEST.END_DATE IS NULL

Step 3:

create the expression_1  transformation and drag to  all ports from source qualifer, 

And create the one ouput port LKP_VALUE

:LKP.lkp_CUSTOMER_ID(CUSTOMER_ID)

Again create the three output ports names:

LKP_VALUE_O=LKP_VALUE

lkp_CUST_KEY=SUBSTR(LKP_VALUE,1,INSTR(LKP_VALUE,',',1,1)-1)

lkp_CUST_LOCATION=SUBSTR(LKP_VALUE,INSTR(LKP_VALUE,',',1,1)+1)

Step4:

Again create the expression transformation ,the expression transformation name exp_CUSTOMERS_DIM_TEST,

1. create the 4 outputs 
2. INSERT_FLG=IIF(ISNULL(lkp_CUST_KEY),'Y','N')
3. UPDATE_FLG=IIF(NOT ISNULL(lkp_CUST_KEY) ANDsrc_LOCATION<>lkp_CUST_LOCATION,'Y','N')
4. Begin_Date=sysdate
5. End_Date=sysdate

Step5:-

create the one route transformation ,the router transformation name rtr_CUSTOMERS_DIM_TEST

* Drag the from all the second expression t/r  all ports  to router transformation 

• create the two groups like 
• INSERT=INSERT_FLG='Y'
• Update =UPDATE_FLG='Y'

Step 6:-

• create the two update stratgey t/r
• one for update and another one for update 

Step 7:-

create the squence t/r  and two out port like NEXT_VAL,CURRVAL,AND connect  to the 

from sql t/r nextval port  to taget_insert instance connect.

From update _update T/r  to connect target_update insteces,

Note:-Keep the target key column  primary key ,if not there in database  asking to DBA team or you can put the informatica level primary key column.

Thanks You Folks 

please let me know you have any update 

Gmail:informaticafolks@gmail.com

Facebook: dwbi folks

Blog:http://dwbifolkstalk.blogspot.in/