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.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Informatica Tuning - Step by Step Approach

    This is the first of the number of articles on the series of Data Warehouse Application performance tuning scheduled to come every week. This one is on Informatica performance tuning.

  • Implementing SCD2 in Informatica Using ORA_HASH at Source

    In this article we shall see how we can implement SCD type2 in Informatica using ORA_HASH, which is an ORACLE function that computes hash value for a given expression. We can use this feature to find the existence of any change in any of the SCD...

  • Informatica Metadata Tables - Overview and Tutorial

    Informatica PowerCentre stores all the information about mapping, session, transformation, workflow etc. in a set of database tables called metadata tables. While these tables are used internally by Informatica, one can get useful information by...

  • Informatica Reject File - How to Identify rejection reason

    When we run a session, the integration service may create a reject file for each target instance in the mapping to store the target reject record. With the help of the Session Log and Reject File we can identify the cause of data rejection in the...

  • Informatica Dynamic Lookup Cache

    A LookUp cache does not change its data once built. But what if the underlying table upon which lookup was done changes the data after the lookup cache is created? Is there a way so that the cache always remain up-to-date even if the underlying...

  • CDC Implementation using Informatica Variable

    This article explains the Change Data Capture mechanism using Informatica Mapping Variable. We can use the Informatica Mapping Variable to extract the CDC data without using any other custom table. Here it goes.

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • PowerCenter SOA Components

    PowerCenter has a Service-Oriented Architecture that provides the ability to scale services and share resources across multiple machines. Let us know more about the components and services associated with Powercenter.

  • How to get Folders and Mapping names from Informatica Metadata Query

    We can use OPB_MAPPING and OPB_SUBJECT tables residing under informatica Repository to obtain information about all the mappings under each Informatica Folder. Following SQL query shows you how to do it.

  • What is Active Lookup Transformation

    Informatica 9x allows us to configure Lookup transformation to return multiple rows. So now we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation type.