Saturday 28 November 2015

INFORMATICA_LOOKUP_PRACTICALS

WORKING WITH LOOK-UP TRANSFORMATION
===================================
SC1: LOADING MATCHED DATA INTO ONE DESTINATION AND UNMATCHED INTO ANOTHER DESTINATION
--------------------------------------------------------------------------------------
CREATE EMP(EID,ENM,DID),DEPT(DID,DNM) TABLES.
NEED OUTPUT LIKE MATCHED DID VALUES RESULT IN TO ONE TABLE AND UNMATCHED RESULT INTO OTHER TABLE.
DEST1: EID,ENM,DID,DNM
DEST2: EID,ENM,DID

EMPSOURCE-->SQ->LOOKUP(DEPT)-->EXPRESSION->ROUTER->TWO DESTINATIONS.
LOOKUP-->TABLE (DEPT),CONDITION--EMP.DID=DEPT.DID (FOR THIS DRAG ONE INPUT COLUMN DID FROM SQ TO LOOKUP), LOOKUP PORTS DID,DNM
EXPRESSION: DRAG LOOKUP COLUMNS FROM LOOKUP,EID,ENM,DID FROM SQ,CREATE ONE ADDITIONAL PORT WITH THE BELOW OPERTION
 MATCH_UNMATCH--> IIF(DID=DID,"MATCH","UNMATCH")
ROUTER:
 MATCH_UNMATCH ="MATCH" --ONE GROUP
 MATCH_UNMATCH="UNMATCH" --ANOTHER GROUP






SC2:IMPLEMENTING INCREMENTAL LOADING
---------------------------------------
CREATE EMP1,EMP2 WITH COLUMNS (EID,ENM,ELOC),TAKE DATA INTO EMP1
EMP1->SQ->LOOKUP->EXP->FILTER->EMP2
LOOKUP-->TABLE(EMP2),FROM SOURCE DRAG AND DROP EID,ENM,ELOC,CONDITION(EID=EID,ENM=ENM,ELOC=ELOC),LOOKUP PORTS (ALL THESE THREE)
EXPRESSION-->TAKE EID,ENM,ELOC LKP PORTS FROM LOOKUP,EID,ENM,ELOC FROM SQ.
   ADD ADDITIONAL PORT LIKE BELOW
  NEW_OLD= IIF(EID=EID AND ENM=ENM AND ELOC=ELOC,"OLD","NEW")
FILTER-->NEW_OLD="NEW"
FIRST RUN: NEW REORDS ADDED
2ND RUN: EMP1 TABLE TAKE SOME OLD AND SOME NEW,RUN WORK FLOW ONLY THE RECENTLY ADDED NEW RECORDS
 SHOULD ONLY BE INSERTED.

3. UNCONNECTED LOOK UP EXAMPLE PRACTICE
-------------------------------------------
GET DNM FROM DEPT TABLE (LOOKUP -UNCONNECTED) BASED ON DID MATCH BETWEEN EMP AND DEPT
1.SOURCE(EMP)-->EXPRESSION(CONNECT EMP ALL COLUMNS ,CREATE ONE ADDITIONAL PORT FOR DNAME CAPTURING WITH THE BELOW EXPRESSION)
     DNAME STRING :LKP.LKP(DID) -->DESTINATION TABLE (EID,ENM,DID,DNM)
2.IN THE MAPPING CREATE LOOK UP LIKE BELOW-> CHOOSE DEPT TABLE
     A) ADD NEW PORT WHICH RECEIVE INPUT DID
      I_DID INTEGER INPUT
     B) TICK MARK DNM RETURN PORT
     C) CONDITION (DID=I_DID)
3. RUN WORKFLOW,SO THAT DESIRED OUPUT WE WILL GET.