Informatica

Informatica Java Transformation

Saurav Mitra

Feel the Power of Java programming language to transform data in PowerCenter Informatica. Java Transformation in Informatica can be used either in Active or Passive Mode.

Suppose we have a requirement where our source data looks like below:

Source Data:

NAME CUST_ID SVC_ST_DT SVC_END_DT
TOM 1 31/08/2009 23/03/2011
DICK 2 01/01/2004 31/05/2010
HARRY 3 28/02/2007 31/12/2009

Here we have a service start date and service end date tied to a customer.

Now we want our target table data in a flattened manner like below:

Target Data:

NAME CUST_ID SVC_ST_DT SVC_END_DT
TOM 1 31/08/2009 31/12/2009
TOM 1 01/01/2010 31/12/2010
TOM 1 01/01/2011 23/03/2011
DICK 2 01/01/2004 31/12/2004
DICK 2 01/01/2005 31/12/2005
DICK 2 01/01/2006 31/12/2006
DICK 2 01/01/2007 31/12/2007
DICK 2 01/01/2008 31/12/2008
DICK 2 01/01/2009 31/12/2009
DICK 2 01/01/2010 31/05/2010
HARRY 3 28/02/2007 31/12/2007
HARRY 3 01/01/2008 31/12/2008
HARRY 3 01/01/2009 31/12/2009

i.e. We want to split the service start date and service end dates on a yearly basis.

The first thing that comes to our mind with this situation is to use Informatica Normalizer. That's TRUE. But if you think twice, you will find that we need to assume or hardcode one thing. That means you should consider that either the time span should have a fixed maximum value. Actually say the maximum span between the start and end date should be 5 years.

Knowingly you are trying to set the number of occurrences of the Normalizer. Next you will be using a expression transformation followed by a filter to achieve the requirement. But in this manner the requirement would not be satisfied when a customer having tenure more than 5 years.

Now here we will be using a small portion of Java Code. The real raw power of Java programming language called from Informatica Powercenter will do the data transformation. Lets go straight to the mapping and the code.

Using Java Transform in Informatica Mapping Java Transform Ports Tab Java Transform Properties Tab Java Transform Java Code Tab

Find the Java Code:-

try
{
    DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    
    int st_yr, ed_yr, st_mon, ed_mon, st_date, ed_date, st_ldm, ed_ldm;
    String str;
    Date st_dt = (Date)formatter.parse(SVC_ST_DT);
    Date ed_dt = (Date)formatter.parse(SVC_END_DT);

    cal1.clear();
    cal1.setTime(st_dt);
    cal2.clear();
    cal2.setTime(ed_dt);

    st_yr = cal1.get(Calendar.YEAR);
    ed_yr = cal2.get(Calendar.YEAR);
        
    do
    {
        OUT_NAME = NAME;
        OUT_CUST_ID = CUST_ID;
        
        OUT_SVC_ST_DT = formatter.format(st_dt);
        if(ed_yr != st_yr)
        {
            str = "31/12/" + st_yr;
            st_dt = (Date)formatter.parse(str);
            cal1.setTime(st_dt);
            OUT_SVC_END_DT = formatter.format(st_dt);
        }
        else
        OUT_SVC_END_DT = formatter.format(ed_dt);
        generateRow();
        
        st_yr = st_yr + 1;
        str = "01/01/" + st_yr;
        st_dt = (Date)formatter.parse(str);
        cal1.setTime(st_dt);
        st_yr = cal1.get(Calendar.YEAR);

    }while(ed_yr >= st_yr);

}
catch (ParseException e)
{
    System.out.println(e);
}

Next now if we want to transform and load the data on a monthly basis. Simply find the Mapping and the Code.

Using Java Transform in Informatica Mapping Java Transform Ports Tab Java Transform Properties Tab Java Transform Java Code Tab

Find the Java Code:-


try
{
    DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    DateFormat formatter1 = new SimpleDateFormat("dd/M/yyyy");
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    
    int yr, st_mon, ed_mon, st_ldm;
    String str;
    Date st_dt = (Date)formatter.parse(SVC_ST_DT);
    Date ed_dt = (Date)formatter.parse(SVC_END_DT);

    cal1.clear();
    cal1.setTime(st_dt);
    cal2.clear();
    cal2.setTime(ed_dt);

    yr = cal1.get(Calendar.YEAR);
    st_mon = cal1.get(Calendar.MONTH)+1;
    ed_mon = cal2.get(Calendar.MONTH)+1; 
    st_ldm = cal1.getActualMaximum(Calendar.DAY_OF_MONTH);
        
    while(ed_mon != st_mon)
    {
        OUT_NAME = NAME;
        OUT_CUST_ID = CUST_ID;
        OUT_SVC_ST_DT = formatter.format(st_dt);

        if(ed_mon != st_mon)
        {
            str = st_ldm + "/" + st_mon +"/" + yr;
            st_dt = (Date)formatter1.parse(str);
            cal1.clear();
            cal1.setTime(st_dt);
            OUT_SVC_END_DT = formatter.format(st_dt);
        }
        else
        {
            OUT_SVC_ST_DT = formatter.format(ed_dt);
        }
        generateRow();
        
        st_mon = st_mon + 1;
        str = "01/" + st_mon + "/" + yr;
        st_dt = (Date)formatter1.parse(str);
        cal1.clear();
        cal1.setTime(st_dt);
        st_mon = cal1.get(Calendar.MONTH)+1;
        st_ldm = cal1.getActualMaximum(Calendar.DAY_OF_MONTH);
    }

    OUT_NAME = NAME;
    OUT_CUST_ID = CUST_ID;
    OUT_SVC_ST_DT = formatter.format(st_dt);
    OUT_SVC_END_DT = formatter.format(ed_dt);
    generateRow();
}
catch (ParseException e)
{
    System.out.println(e);
}

Note: You can extend PowerCenter functionality with the Java transformation which provides a simple native programming interface to define transformation functionality with the Java programming language. You can use the Java transformation to quickly define simple or moderately complex transformation functionality without advanced knowledge of the Java programming language.

For example, you can define transformation logic to loop through input rows and generate multiple output rows based on a specific condition. You can also use expressions, user-defined functions, unconnected transformations, and mapping variables in the Java code.