Wednesday, October 15, 2014

Implementing java transformation with example

JAVA transformation:- 


Explanation:-Java transformation in Informatica PowerCenter uses Java programming language to transform the data.It is not necessary to write the entire Java code or use an external Java development environment as the Java code can be entered as snippets in the PowerCenter Designer client.
The code in Java transformation can invoke Informatica's custom expressions, user-defined functions, unconnected transformations and mapping variables. Java methods, variables, third-party API's, built-in Java packages and static code can be invoked as well.
Java transformation can be re-usable and it can be defined as both active or passive Informatica object. 

Scenario:-
Below is the requirement.

Source:-
NAME          ID                    Requirement
RAVI            1                      (no need to repeat as it ID is 1)
KUMAR        3                     (repeat 3 times as it ID is 3)
John             4                     (repeat 4 times as it ID is 4)

Required Out Put:-
Name                   ID
RAVI                     1                  
KUMAR                3    
KUMAR                3
KUMAR                3
John                     4
John                     4
John                     4
John                     4

Step1:- The final Mapping looks like below
When we create Java transformation it will ask us to specify (Active or Passive). For our current requirement lets select it as Active (as its changing the no. of input records coming from source).
Step2:- Create Ports in Java
In this section we need to create ports (for input and output) as per our requirement we can uncheck either of one port.
Step3:- 
               In this section we can Import third-party Java packages, built-in Java packages, or custom Java packages.(for this scenario we don’t need any).
Step4:- Write the code and compile it.
Let’s write the required code and compile it.
For this requirement we need to increment the value of ‘I’ till it is less than ‘PROD_ID’.
Step5:-   Let’s run the workflow and check the result.
Target Output:-
Scenario 2:-
 Source Data:-
ID              NAME
1            NETEZZA, ORACLE
3           SQL Server, DB2, Teradata

Required Output:-
ID                   NAME
1                  NETEZZA
1                  ORACLE
3                  SQL Server
3                  DB2
3                  Teradata

Follow the above steps in Scenario1 and replace the below code(in the code section) to achieve the above output.

String speech = PROD_NM;  
 String[] result = speech.split(",");
 for (int x=0; x<result.length; x++) {
PROD_NM = result[x];
PRD_ID = PRD_ID;
generateRow(); 
 }

Secenario 3:-
Source Data 
ID                  NAME
1,2               NETEZZA,ORACLE
3,4,5            SQL Server, DB2, Teradata

Required Output:-
ID                   NAME
1                  NETEZZA
2                  ORACLE
3                  SQL Server
4                  DB2
5                  Teradata
Follow the above steps in Scenario1 and replace the below code (in the code section) to achieve the above output.

String str_var=PROD_NM;
String str_var1=PRD_ID;
String[] arr;
String[] arr1;
String delimiter = ",";
arr = str_var.split(delimiter);
arr1 = str_var1.split(delimiter);
for (int i =0; i < arr.length; i++){
PROD_NM = arr[i];
PRD_ID = arr1[i];
generateRow();
}

Scenario:-

Source data
NETEZZA,ORACLE               |1,2      |1|COBRAND
SQL Server, DB2, Teradata  |3,4,5   |2|LENDING
NUMBER                                 |8,9,10|3|LENDING
ANT,KANTI,RANTI               |6         |4|LENDING

Expected Output:-
NETEZZA                 1              1
ORACLE                   2              1
DB2                            4              2
SQL Server              3              2
Teradata                   5             2
NUMBER                 8              3
null                            10            3
null                             9             3
RANTI                    null           4
ANT                            6             4
KANTI                     null          4

Solution:-
String str_var=PROD_NM;
String str_var1=PRD_ID;
String[] arr;
String[] arr1;
String delimiter = ",";
arr = str_var.split(delimiter);
arr1 = str_var1.split(delimiter);
int len = 0;
if(arr.length &lt;= arr1.length)
{
    len = arr.length;
}
else
{
    len = arr1.length;
}
for (int i = 0; i &lt; len; i++)
{
    PRD_ID  = arr1[i];
    PROD_NM = arr[i];
    ID = ID;
    generateRow();
}
if (arr.length &lt; arr1.length)
{
    for (int j = len; j &lt; arr1.length; j++)
    {
        PRD_ID  = arr1[j];
        PROD_NM = "null";
        ID = ID;
        generateRow();
    }
}
else
{
    for (int k = len; k &lt; arr.length; k++)
    {
        PROD_NM = arr[k];
        PRD_ID  = "null";
        ID  = ID;
        generateRow();
    }
    }                      {
    for (int k = len; k &lt; arr.length; k++)
    {
        PROD_NM = arr[k];
        PRD_ID = "null";
        generateRow();
    }
}

No comments :