Join the Community

Sponsor

Free BI Digest

Need more help on this subject?

Informatica Java Transformation

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 I have the requirement where my source data looks like this:

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 I have a service start date and service end date tied to a customer.

Now I want my target table data in a flattened manner like this:

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. I want to split the service start date and service end dates on a yearly basis.

The first thing that comes to mind with this situation is to use Informatica Normalizer. Thats TRUE. But if you think twice, you will find that we need to assume or hard-code 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 occurences 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 I 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

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.

About the Author

Saurav Mitra

Saurav Mitra is a business intelligence professional.
Connect with the author via Google+
 



If you have any doubt or question on the above article, please Ask your question here. We will surely help you out!

Before you leave, Kindly provide your comments / suggestions / feedback below. Thank You!

 

READ OUR RECOMMENDED ARTICLES