This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

Wednesday, 12 March 2014

Common Error Part1

These are errors which i encountered working today..I have put solution for them with details

Error :  Scheduling Error for workflow, workflow [id = X] could not be retrieved from the repository
Reason : Your workflow and related object are not checked in
Solution : Checkin workflow and related objects


Error :  Schedule Workflow: ERROR: Workflow [wf_name]: Workflow not scheduled
Reason :   Your are using resuable scheduler and it is not checked in
Solution :  Checkin resuable scheduler

Error :  Input sources to SQ SQ_TRADE have no relationship....there are data flow validation errors.
Reason : Created Source qualifier by manually dragging item from diffrent source but sql override provide
Solution : Over rid the SQL query in source qualifier

How to change the mapping associated in the session:


Often during development cycle we come across scenario when you have to change session’s existing mapping with a new one.

Informatica should have provided a direct option to choose new mapping unfortunately it is not straight forward you can follow below method to achieve this

1. Invalidate the current mapping temporarily and refresh the session. This would allow you to choose a different mapping for your session. Pick the new one.
2. XML export. Export the session object and then edit the xml file where you need to change the mapping name. Now when you'd import it, it'll ask for the mapping to associate with. This way you can do it.
3. Change Metadata tables This is one of method which I would not recommend ,It is only to be used if nothing else works

Informatica preserves existing settings and it will require to set connections for new objects in the session.

Saturday, 1 March 2014

How to pass a mapping value of mapping variable to workflow and worklet


Normally we come across various scenarios where have to use value from inside of mapping as session or workflow/worlklet variable.

Two scenarios I can think of 

1.       You have to use sessstarttime in sending attached files by email in email task. You can’t do it normally as sessstart time is not available in email task.

2.       You have a mapping to capture errors in mapping and you want to send error details in email to user with all error details like file name, column name and error code and error description.

It can be achieved in by below

1.       Create a mapping variable for example in second case with name $$FILE_NAME with type max or min and data type string

2.       Assign value to $$FILE_NAME using variable function inside mapping SETVARIABLE($$FILE_NAME,CurrentlyProcessedFileName)



3.       Create two workflow variable with name $$FILE_NAME and $$BLANK

4.       Use Session Pre assignment task to assign a blank value to mapping variable, Reason behind is that if you don’t do this then it will take persistent value from repository and it will compare result from last session to current session and can result in inconsistent output depending on whether you are using max or min function.So at start of session FILE_NAME mapping variable has been assigned a blank value



5.       Use Session Post session assignment (success/ failure) to assign a value to



Workflow Variable FILE_NAME has been assigned value from Mapping Variable FILE_NAME (Which has been assigned value by mapping variable function when session ended) So Workflow Variable FILE_NAME has value which can be used in email task.

6.if you further want to assign value from worklet to workflow then double click on work let inside workflow  and click on variable tab ,you can use it in work let now


Friday, 28 February 2014

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..

Wednesday, 26 February 2014

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..


    Tuesday, 25 February 2014

    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.

    Friday, 14 February 2014

    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.