informatica

Informatica Scenario based interview questions (Convert row into columns)

This is the one of most frequently asked informatica scenario based interview question

We have source data like below in table

A

B

C

Output required is

A

B

C

I have seen rarely a candidate answer this question correctly. Their answer is mostly a crammed (by reading on various website) without understanding core concept of informatica transformations.

I will explain three methods to achieve this then we will find out which one is best method

1.       Router Transformation

2.       Normalizer Transformation        

3.       Java Transformation

Router Transformation : This method take advantage of the fact that router is an active transformation and one row can fall into multiple groups i.e. if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Step1)

Suppose we have a source with three input columns

Step2)  Create an expression Transformation with field name FLD_OUTPUT to concatenate three fields separated by # sign

Step3)  Create a router transformation with three output groups with condition

GROUP1 : substr(FLD_OUTPUT,1,instr(FLD_OUTPUT,'#')-1)=FLD1

GROUP2 : substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#')+1,instr(FLD_OUTPUT,'#',2)-1)=FLD2

GROUP3: substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#',3)+1)=FLD3

Purpose of this is that row will fall into three groups ..we are extracting string from concatenated string are comparing to respective field.i.e value A will be in first group and B in second group and so on…

Step4) Create union transformation to combine output from three output group from router..Take FLD1 from first group, FLD2 from second group ,FLD3 from third group

Output from union will be

A

B

C

Core Concept: if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Easy isn’t it …No need to cram. We will now move to other methods…

Normalizer  : Normalizer transformation has a property  “Occurs : The number of instances of a column or group of columns in the source row. “

You can define number of occurrence of source field, Once you define number of occurrence then informatica automatically creates that number of input ports. In this case 3

Connect FLD1, FLD2 and FLD3 from source or expression in step2 of first method and FLD as output connected to target.

Output from normalizer will be

A

B

C

Core Concept: Normalizer transformation has a property “Occurs: The number of instances of a column or group of columns in the source row. “

Let us move to final and best method

Java Transformation: We will take advantage of property, Java transformation is most underutilized transformation of informatica, If used properly it can do magic

Generate Transaction

The transformation generates transaction rows. You can enable this property for active Java transformations.

Create a java transformation with one input and one output field

Now connect concatenated field (A#B#C) from expression in step2 of first method to FLD_INPUT of java transformation

Now go to java code and copy below

String[] str_fld_nm=FLD_INPUT.split("#");

for(int i=0; i<str_fld_nm.length; i++){

                FLD_OUTPUT =str_fld_nm[i];

                generateRow();

}

It is using java functionality of array to break input field and multiple fld and then taking advantage of generateRow() to produce multiple rows.

Now connect FLD_OUTPUT to target and it will be

A

B

C

Core concept: Java functionality to break concatenated string into multiple fields and generateRow() to produce multiple rows

Now we have covered all three features. Let us see which one is best

Feature	Router	Normaliser	Java
Flexiblity	Low (As you have to hardcode number of fields)	Low (As you have to hardcode number of occurrence )	High (No Hard coding as it can handle any number of fields)
Ease	High as developer are comfortable with using router transformation	Medium as developer don’t use it frequently	Low. As most developer are not from java background. It is difficult for them to understand it

If you need xml of mapping then please send a mail to support@itnirvanas.com or lalits77@gmail.com

I request again …don’t cram answers..Instead understand the concepts..

Read More

Informatica Scenario based interview questions (Convert columns into rows)

This is the one of most frequently asked informatica scenario based interview question.

We have source data like below in table

A

B

C

Output required is

A

B

C

I have seen rarely a candidate answer this question correctly. Their answer is mostly a crammed (by reading on various website) without understanding core concept of informatica transformations.

I will explain three methods to achieve this then we will find out which one is best method

1.       Router Transformation
2.       Normalizer Transformation        
3.       Java Transformation

Router Transformation : This method take advantage of the fact that router is an active transformation and one row can fall into multiple groups i.e. if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Step1)

Suppose we have a source with three input columns

Step2)  Create an expression Transformation with field name FLD_OUTPUT to concatenate three fields separated by # sign

Step3)  Create a router transformation with three output groups with condition

GROUP1 : substr(FLD_OUTPUT,1,instr(FLD_OUTPUT,'#')-1)=FLD1

GROUP2 : substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#')+1,instr(FLD_OUTPUT,'#',2)-1)=FLD2

GROUP3: substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#',3)+1)=FLD3

Purpose of this is that row will fall into three groups ..we are extracting string from concatenated string are comparing to respective field.i.e value A will be in first group and B in second group and so on…

Step4) Create union transformation to combine output from three output group from router..Take FLD1 from first group, FLD2 from second group ,FLD3 from third group

Output from union will be

A

B

C

Core Concept: if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Easy isn’t it …No need to cram. We will now move to other methods…

Normalizer  : Normalizer transformation has a property  “Occurs : The number of instances of a column or group of columns in the source row. “

You can define number of occurrence of source field, Once you define number of occurrence then informatica automatically creates that number of input ports. In this case 3 

Connect FLD1, FLD2 and FLD3 from source or expression in step2 of first method and FLD as output connected to target.

Output from normalizer will be

A

B

C

Core Concept : Normalizer transformation has a property “Occurs: The number of instances of a column or group of columns in the source row. “

Let us move to final and best method

Java Transformation : We will take advantage of property, Java transformation is most underutilized transformation of informatica, If used properly it can do magic

Generate Transaction

The transformation generates transaction rows. You can enable this property for active Java transformations.

Create a java transformation with one input and one output field

Now connect concatenated field (A#B#C) from expression in step2 of first method to FLD_INPUT of java transformation

Now go to java code and copy below

String[] str_fld_nm=FLD_INPUT.split("#");

for(int i=0; i

                FLD_OUTPUT =str_fld_nm[i];

                generateRow();

}

It is using java functionality of array to break input field and multiple fld and then taking advantage of generateRow() to produce multiple rows.

Now connect FLD_OUTPUT to target and it will be

A

B

C

Core concept : Java functionality to break concatenated string into multiple fields and generateRow() to produce multiple rows.

Now we have covered all three features. Let us see which one is best

Feature	Router	Normaliser	Java
Flexiblity	Low (As you have to hardcode number of fields)	Low (As you have to hardcode number of occurrence )	High (No Hard coding as it can handle any number of fields)
Ease	High as developer are comfortable with using router transformation	Medium as developer don’t use it frequently	Low. As most developer are not from java background. It is difficult for them to understand it

If you need xml of mapping then please send a mail to support@itnirvanas.com or lalits77@gmail.com

I request again …don’t cram answers..Instead understand the concepts..

Read More

Informatica Common Errors :The Target Definition has more than one Transaction Control point connected to it

Error : The Target Definition has more than one Transaction Control point connected to it

Scenario :

Create a mapping with two streams( one with Transaction control and Second without Transaction control)..You will get  above error

Src1 - SQ1- EXP1- TGT1

Src2 - SQ2- Exp2-TC-TGT2

Reason :

Informatica processes transactional data row wise, hence,while using Transaction control for one of the targets to define the transaction point, Informatica fails to identify the transaction point for the other targets,This results in "The Target Definition has more than one Transaction Control point connected to it".

Solution :

You need to define Transaction Control transformation in other pipeline with property TC_CONTINUE_TRANSATION.

Read More

Issue with informatica client in Dual Monitor

Problem

Recently i started with informatica in dual monitor and found few issues (Like Expression editor size and sq editor getting greyed out)

Solution

At commend prompt type regedit and  go to below path (Please take backup of registry before making any changes)

HKEY_CURRENT_USER\Software\Informatica\PowerMart Client Tools\9.1.0\Designer\Options\Global\Editor\SQL

Change the values of the following to 0

Expression Editor Position

Expression Editor Splitter Position

SQL Editor Position

SQL Editor Splitter Position

Please restart your pc after changing the registry values.

Hope it helps to solve your issue.

Read More