Informatica Interview questions with answers
Question.1 What are the components of Informatica? And what is the purpose of each?
Answer: Informatica Designer, Server Manager & Repository Manager. Designer for Creating Source & Target definitions, Creating Mapplets and Mappings etc. Server Manager for creating sessions & batches, Scheduling the sessions & batches, Monitoring the trig jered sessions and batches, giving post and pre session commands, creating database connections tc various instances etc. Repository Manage for Creating and Adding repositories, Creating & editing folders within a repository, Establishing users, groups. privileges & folder permissions, Copy. delete, bac :up a repository, Viewing the history of sessions, Viewing the locks on various objects and removing those locks etc.
Question.2 What is a repository? And how to add it in an informatica client?
Answer: It’s a location where all the mappings and sessions related information is stored. Basically it’s a database where the metadata resides. We can add a repository through the Repository manager.
Question.3 Name at least 5 different types of transformations used in mapping design and state the use of each.?
Answer: Source Qualifier — Source Qualifier represents all data queries from the source, Expression = Expression performs simple calculations, Filter — Filter serves as a conditional filter, Lookup — Lookup looks up values and passes to other objects, Aggregator – Aggregator performs aggregate calculations.
Question.4 How can a transformation be made reusable?
Answer: In the edit properties of any transformation there is a check box to make it reusable, by checking that it becomes reusable. You can even create reusable transformations in Transformation developer.
Question. 5 How are the sources and targets definitions imported in informatica designer? How to create Target definition for flat files?
Answer: When you are in source analyzer there is a option in main menu to Import the source from Database, Flat File, Cobol File & XML file, by selecting any one of them you can import a source definition. When you are in Warehouse Designer there is arp option in main menu to import the target from Database, XML from File and XML from sources you can select any one of these.
There is no way to target definition as file in Informatica designer. So while creating the target definition for a file in the warehouse designer it is created considering it as a table, and then in the session properties of that mapping it is specified as file.
Question.6 Explain what is sql override for a source table in a mapping?
Answer: The Source Qualifier provides the SQL Query option to override the default query. You can enter any SQL statement supported by your source database. You might enter your own SELECT statement, or have the database perform aggregate calculations, or call a stored procedure or stored function to read the data and perform some tasks.
Question.7 What is lookup override?
Answer: Ails: This feature is similar to entering a custom query in a Source Qualifier transformation. When entering a Lookup SQL Override, you can enter the entire override, or generate and edit the default SQL statement.
The lookup query override can include WHERE clause.
Question.8 What are mapplets? How is it different from a Reusable Transformation?
Answer: A mapplet is a reusable object that represents a set of transformations. It allows you to reuse transformation logic and can contain as many transformations as you need. You create mapplets in the Mapplet Designer.
Its different than a reusable transformation as it may contain a set of transformations, while a reusable transformation is a single one.
Question.9 How to use an oracle sequence generator in a mapping?
Answer: We have to write a stored procedure, which can take the sequence name as input and dynamically generates a nextval from that sequence. Then in the mapping we can use that stored procedure through a procedure transformation.
Question. 10 How to create the source and target database connections in server manager?
Answer: In the main menu of server manager there is menu “Server Configuration”, in that there is the menu “Databasesi connections”. From here you can create the Source and Target database connections.
Question. 11 Where are the source flat files kept before running the session?
Answer: The source flat files can be kept in some folder on the Informatica server or any ether machine, which is in its domain.
Question. 12 What are the oracle DML commands possible through an update strategy?
Answer: dd_insert, dd_update, dd_delete & dd_reject.
Question. 13 How to update or delete the rows in a target, which do not have key fields?
Answer: To Update a table that does not have any Keys we can do a SQL Override of the Target Transformation by() specifying the WHERE conditions explicitly. Delete cannot be done this way. In this case you have to specifically
mention the Key for Target table definition on the Target transformation in the Warehouse Designer and delete the– row using the Update Strategy transformation.
Question. 14 What is option by which we can run all the sessions in a batch simultaneously?
Answer: In the batch edit box there is an opticn called concurrent. By checking that all the sessions in that Batch will run concurrently.
Question. 15 Informatica settings are available in which file?
Answer: Informatica settings are available in a file pmdesign.ini in Windows folder.
Question. 16 How can we join the records from two heterogeneous sources in a mapping?
Answer: By using a joiner.
Question. 17. Difference between Connected & Unconnected look-up.
Answer: An unconnected Lookup transformation exists separate from the pipeline in the mapping. You write an’j expression using the :LKP reference qualifier to call the lookup within another transformation. While the connected -lookup forms a part of the whole flow of mapping.
Question. 18. Compare Router Vs Filter & Source Qualifier Vs Joiner.
Answer: A Router transformation has input ports and output ports. Input ports reside in the input group, and output ports reside in the output groups. Here you can test data based on one or more group filter conditions. But in filter you can filter data based on one or more conditions before writing it to targets.
Question.19 What are Aggregator Transformation?
Answer: Aggregator transformation is an Active and Connected transformation. This transformation is useful to perform calculations such as averages and sums (mainly to perform calculations on multiple rows or groups)
register here now for Informatica Course