cloudetlinformaticainformatica-cloud

SCD Type - 2 implementation in IICS (Informatica Cloud)


I am new in IICS. Does anyone please help me implement SCD type 2?  Or any walk-through tutorial weblink. Thanks in advance. 


Solution

  • I have implemented using code and invoke through Informatica

    create two table

    select * from db.Customer_Source
    select * from EDW.Customer_Master
    
    
    
    
    
    INSERT INTO dbo.Customer_Master           --Insert into master 
    SELECT
        Source_Cust_ID,
        First_Name,
        Last_Name,
        Eff_Date,
        End_Date,
        Current_Flag
    FROM
        ( MERGE EDW.Customer_Master CM
            USING EDW.Customer_Source CS                                             
        ON (CM.Source_Cust_ID = CS.Source_Cust_ID)                                
        
        WHEN NOT MATCHED THEN
            INSERT VALUES (CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,
            convert(char(10), getdate()-1, 101), '12/31/2199', 'y')                         -- if not match then insert new values
            
        WHEN MATCHED AND CM.Current_Flag = 'y' AND (CM.Last_Name <> CS.Last_Name ) THEN            -- if match then it will insert new row and fkag records as "Y" and old one as "N" 
             UPDATE SET CM.Current_Flag = 'n', CM.End_date = convert(char(10), getdate()-2, 101)
        OUTPUT $Action Action_Out,
                CS.Source_Cust_ID,
                CS.First_Name,
                CS.Last_Name,
                convert(char(10), getdate()-1, 101) Eff_Date,
                '12/31/2199' End_Date,
                'y'Current_Flag)
    AS MERGE_OUT
    WHERE MERGE_OUT.Action_Out = 'UPDATE';