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.

Showing posts with label Informatica interview questions. Show all posts
Showing posts with label Informatica interview questions. Show all posts

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, 29 October 2013

    Generating Dynamic Multiple Target files in Informatica

    Recently we came across a scenario to generate multiple dynamic Multiple Target files in Informatica. We receive vendor data through Legacy database in below table


    1. Invoice_ID
    2. Invoice_No
    3. Invoice_Amount
    4. Vendor_Id


    We need to separate all details related to one vendor in separate file so that we can pass data to third part vendors in separate file.


    INVOICE_DETAILS


    INVOICE_ID
    INVOICE_NO
    INVOICE_AMOUNT
    VENDOR_ID
    1
    A01
    100.00
    10
    2
    A02
    125.00
    10
    3
    A03
    150.00
    10
    4
    B01
    200.00
    20
    5
    B02
    500.00
    20
    6
    D01
    345.00
    40
    7
    C01
    230.00
    30
    8
    D02
    450.00
    40
    9
    E01
    40.00
    50







    In order to achieve this we need to follow below steps in mapping


    Step1 ) Import INVOICE_DETAILS and drag into mapping and connect all port from Source qualifier to expression and do below sql override

    SELECT INVOICE_DETAILS.INVOICE_ID, INVOICE_DETAILS.INVOICE_NO, INVOICE_DETAILS.INVOIC_AMOUNT, INVOICE_DETAILS.VENDOR_ID
    FROM
    INVOICE_DETAILS
    order by VENDOR_ID


    Step2) Create expression transformation with as below with 4 input port plus

    1. FILE_NAME (Output Port)  'Vendor_'||VENDOR_ID||'Details.csv'
    2. CHECK_VENDOR_ID(Variable Port)  iif (VENDOR_ID <> PREV_VENDOR_ID,1,0)
    1. OUTPUT_VENDOR_ID (Output)  CHECK_VENDOR_ID
    2. PREV_VENDOR_ID (Variable) VENDOR_ID




    Step 3 ) Create Transaction Control Transformation (TC_GENERATE_FILES) with below logic 

    iif(OUTPUT_VENDOR_ID=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)



    Step 4 ) Create a File Target with same structure as source table and new field in target using option “Add FileName to this Table”

    Connect FileName column from transaction control to FileName Port.



    Step5) Run the workflow and below files will be created






    Wednesday, 23 October 2013

    Informatica Scenario Based Interview Questions (Part1)


    Interviewer asked “How  will you get first 3 records from flat file source? “...You will say in your mind that’s simple

    You reply “We can use variable in expression and increment it and the use filter transformation to pass just first two records”

    Interviewer will come up with a new trick in his sleeve “How  will you get last 3 records from flat file source? “

    Déjà vu …J

    You will think and try to build some logic on the fly and try to explain may we can use this transformation or some another transformation and will get bit confused.

    I have tried to explain this with a simple example

    Suppose we have customer data coming in below format and we want to have last 3 rows in target


    Step1)

    Create expression transformation (exp_Build_Logic)  drag all three input ports and create three extra ports as below

    Ø  v_dummy (Output Port) –Assign Value 1 to it
    Ø  v_Total_Count (Variable Port) := v_Total_Count+1
    Ø  o_Total_Count := v_Total_Count






    Step2) Create Aggregator transformation with all ports and do not choose any port as group by port





    Step 3)

     Create Joiner transformation jnr_Agg_Expression as Sorted Input (You need to choose this option as you are trying to use two sources originating from same source)

    Take two ports from aggregator and rest from expression transformation and join on field v_dummy


    Output from joiner



    Step 4)  Create filter transformation after joiner with following conditions
    o_Total_Count_Agg-o_Total_Count1<=2


    This will past last three records

    Step5)

    Output will be



    Mapping



    XML 


    Wednesday, 11 February 2009

    Informatica interview questions (Part 3)

    We will continue with third set of questions

    Q 1 What is the use of Shared Folder?
    Ans : Shared folders allow users to create shortcuts to objects in the folder.If you have an object that you want to use in several mappings or across multiple folders, we can place the object in a shared folder.we can then access the object from other folders by creating a shortcut to the object. Shortcuts inherit changes to their shared object. Shared folders in global repositories can be used by any folder in the domainOnce you make a folder shared, you cannot reverse it.

    Q2 What are the different ways to migrate from one environment to another in Informatica
    Ans
    1.We can export repository and import into the new environment
    2. We can use informatica deployment groups
    3. We can Copy folders/objects
    4. We can Export each mapping to xml and import in new environment

    Q3 What is difference between mapping parameter and variable
    Ans A Mapping parameter is a static value that you define before running the session and it value remains till the end of the session.when we run the session PowerCenter evaluates the value from the parameter and retains the same value throughout the session. When the session run again it reads from the file for its value.

    A Mapping variable is dynamic or changes anytime during the session. PowerCenter reads the intial value of the variable before the start of the session and changes its value by using variable functions and before ending the session its saves the current value (last value held by the variable). Next time when the session runs the variable value is the last saved value in the previous session.

    Q4 What is DTM
    Ans DTM (Data Transformation Manager) is the process associated with the session taskThe Load Manager creates one DTM process for each session in the workflow. The DTM process performs the following tasks:
    1.Reads session information from the repository.
    2.Expands the server, session, and mapping variables and parameters.
    3.Creates the session log file. Validates source and target code pages.
    4.Verifies connection object permissions.
    5.Runs pre-session shell commands, stored procedures and SQL.
    6.Creates and runs mapping, reader, writer, and transformation threads to extract, transform, and load data.
    7. Runs post-session stored procedures, SQL, and shell commands. Sends post-session email.

    Q5 How to delete duplicate record in Informatica
    Ans : Following are ways to remove duplicate records
    1. In source qualifier use select distinct
    2. Use Aggregator and group by all fields
    3. Override SQL query in Source qualifier

    Q6 What are different type of repositories that can be created using Informatica Repository Manager?
    Ans
    1. Standalone Repository : A repository which functions individually and is unrelated to any other repositories.
    2. Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.
    3. Local Repository : Local repository is within a domain . Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

    Q 7 How to find all invalid mappings in a folder
    Ans Use following query
    SELECT MAPPING_NAME FROM REP_ALL_MAPPINGS WHERE SUBJECT_AREA='YOUR_FOLDER_NAME' AND PARENT_MAPPING_IS_VALIED <>1

    Q 8 What are the data movement modes in informatica?
    Ans Data movement modes determines how power center server handles the character data. We choose the data movement in the informatica server configuration settings. Two types of data movement modes available in informatica.
    1.ASCII mode
    2.Unicode mode.

    Q9 What are Limitations on joiner transformation ?
    Ans
    1.Both pipelines begin with the same original data source.
    2.Both input pipelines originate from the same Source Qualifier transformation.
    3.Both input pipelines originate from the same Normalizer transformation.
    4.Both input pipelines originate from the same Joiner transformation.
    5.Either input pipelines contains an Update Strategy transformation.
    6.Either input pipelines contains a Sequence Generator transformation.

    Q10 What will happen if we connect only current value port from seq generator to next transformation (without connecting nextval)
    Ans Each target will get the value 1.

    If you have any doubt or need more questions please mail us at support@itnirvanas.com

    Thursday, 29 January 2009

    Informatica interview questions (Part 2)

    We will continue with second set of questions

    Q 1. What is the difference between Connected and Unconnected Lookups?

    Ans : Connected Lookup

    1.Connected Lookup Receives input values directly from the pipeline.

    2.Connected Lookup You can use a dynamic or static cache.

    3.Connected Lookup Cache includes all lookup columns used in the mapping

    4.Connected Lookup Can return multiple columns from the same row or insert into the dynamic lookup cache.

    5.Connected Lookup If there is no match for the lookup condition, the Informatica Server returns the default value for all output ports. If you configure dynamic caching, the Informatica Server inserts rows into the cache.

    6.Connected Lookup Pass multiple output values to another transformation. Link lookup/output ports to another transformation.

    7.Connected Lookup Supports user-defined default values

    Unconnected Lookup

    1.Unconnected Lookup Receives input values from the result of a :LKP expression in another transformation..

    2.Unconnected Lookup You can use a static cache.

    3.Unconnected Lookup Cache includes all lookup/output ports in the lookup condition and the lookup/return port.

    4.Unconnected Lookup The dynamic lookup cache, Designate one return port (R). Returns one column from each row

    5.Unconnected Lookup If there is no match for the lookup condition, the Informatica Server returns NULL.

    6.Unconnected Lookup Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling: LKP expression.

    7.Unconnected Lookup Does not support user-defined default values

    Q 2. Difference between Active and passive transformation?

    Ans: Active transformation :Active transformation can change the no of records passing through it.Active transformations that might change the no of record are advance ext procedure, aggregator, filter, joiner, normalizer, rank, and update strategy, source qualifier

    We can connect only 1 active transformation to the same transformation or target can connect any no of pass transformation. Advanced External Procedure/Aggregator/Application Source Qualifier/Filter/Jo iner/Normalizer/Rank/Router/Update Strategy

    Passive transformation :Passive transformation can never change the no of records.

    Ex. lookup, expression, external procedure, sequence generator, stored procedure
    Expression/External Procedure/Mapplet- Input/Lookup/Sequence generator/XML Source Qualifier/Mapplet - Output

    Q 3. What is an indicator file and how it can be used?

    Ans: Indicator file is used for Event Based Scheduling when you don’t know when the Source Data is available., A shell command ,script or a batch file creates and send this indicator file to the directory local to the Informatica Server. Server waits for the indicator file to appear before running the session.

    Q 4. Is lookup a Active transformation or Passive transformation?

    Ans: Passive.

    Q 5. What are the objects that you can’t use in a mapplet?

    Ans :1. COBOL source definition

    2. Joiner transformations

    3.Normalizer transformations

    4.Non reusable sequence generator transformations.

    5.Pre or post session stored procedures

    6.Target definitions

    7.Power mart 3.5 style Look Up functions

    8.XML source definitions

    9.IBM MQ source definitions

    Q6. What is incremental aggregation?

    Ans: When using incremental aggregation, we apply capture d changes in the source to aggregate calculations in a session. If the source changes only incrementally and we can capture changes, we can configure the session to process only those changes. This allows the Informatica Server to update our target incrementally, rather than forcing it to process the entire source and recalculate the same calculations each time you we the session.

    Q7. What is tracing level and what are the different types of tracing level?

    Tracing level represents the amount of information that informatica server writes in a log file.

    Types of tracing level

    1.Normal

    2.Verbose

    3.Verbose init

    4.Verbose data

    Q8. How you will recover sessions?

    Ans : If we stop a session or if an error causes a session to stop, refer to the session and error logs to determine the cause of failure. Correct the errors, and then complete the session. The method we use to complete the session depends on the properties of the mapping, session, and Informatica Server configuration.

    Use one of the following methods to complete the session:

    1. Run the session again if the Informatica Server has not issued a commit.

    2. Truncate the target tables and run the session again if the session is not recoverable.

    3. Consider performing recovery if the Informatica Server has issued at least one commit.

    Q9. When to use Abort, Decode functions?

    Ans: Abort can be used to Abort / stop the session on an error condition. If the primary key column contains NULL, and you need to stop the session from continuing then you may use ABORT function in the default value for the port. It can be used with IIF and DECODE function to Abort the session.

    Q10 What is constraint based loading ?

    Ans Constraint based loading. the data was loaded into the target table based on the Constraints.i.e if we want to load the EMP&DEPT data, first it loads the data of DEPT then EMP because DEPT is PARENT table EMP is CHILD table. In simple terms, it loads PARENT table first then CHILD table.

    My Profile

    Tuesday, 27 January 2009

    Informatica interview questions (Part 1)

    Following is the list of some of Informatica interview questions.We will try to come up with more questions in later posts.

    Q.1 Flat file is having 10 records as input and I want to push 5 records to the target?
    Ans : one variable with the name $$count at mapping variable with a Count aggregation typeand initial value for that $$count=0. Create one expression and use SETCOUNTVARIABLEv_count=SetCountVariable ($$Count) Create one filter after expression and put condition v_count <=5
    Q2 What is difference between direct and indirect loading options in sessions?
    Ans: Use file type direct when we are loading single file into Target. Use Indirect when we want to load multiple files through single session in the mapping

    Q3 When we create a target as flat file and source as oracle. How can we have first rows as column names in flat files.
    Ans :We can add a union all clause in the Source Qualifier of the Relational source to add the header values in SELECT clause, so as to populate them as header.

    Q 4 How to capture the user information (Username) of the person, who is executing a workflow or session in workflow manager.
    Ans : SELECT USER_NAME,WORKFLOW_NAME FROM REP_WFLOW_RUN;

    Q 5 How to join two tables, which reside in two different databases in the Source qualifier.
    Ans: By using DB links in SQ ,but throughput will be very slow. Joiner will be better approach in this case

    Q. 6 What is a code page?
    A. A code page contains encoding to specify characters in a set of one or more languages. The code page is selected based on source of the data. For example if source contains Japanese text then the code page should be selected to support Japanese text.
    When a code page is chosen, the program or application for which the code page is set, refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.

    Q 7. Is there any way to handle sequence generation over 2 billion records, As sequence generator can generate max 2 billion records
    Ans
    Method1
    it will be done by unconnected lookup create lookup transformation on target just keep one field(id_field) and remove all remaing fields.create i/p field with same data type as out field(id_field) select return port for id_field in loook up override write a query as select nvl(max(id_field),0) from table and ' lookup condition is id_field>=in_field in exp transformation v_dummy(v)--->it always to be zero v_lkp_return iif(:lkp_target(v_dummy) ---here u can pass v_dummy value or just zero o_id_field(0).v_lkp_return+1 __.____._

    Method 2
    Onw way would be to use expression trans after sequence generator. Another way could be to use expression instead of sequence genearator. Here you will use variable of agg type of count to create sequences.

    Q .8 What is difference between $ and $$ parameters/variables
    Ans $ are System defined and $$ are User defined variables

    Q 9.How to use data from SAP/Oracle Apps /Mainframe
    Ans Through power exchange.

    Q 10 How to find all invalid mappings in a folder
    Ans we can find the invalid mappings information from the rep_all_mappings view with a query Select mapping_name from rep_all_mappings Where subject_area='Folder_Name' And parent_mapping_is_valid <>1 My Profile