Interview Questions on Informatica

1. What are the different type of transformations in informatica?
In informatica basically two types of transformations are there.
Active Transformation: Active Transformation is one which will change the input row count.
If a Transformation is supplied with ‘N’ input rows and if it returns less than or greater than ‘N’ output rows, then it is Active Transformation.
Ex: Filter, Rank, Sorter, etc.
Passive Transformation: Passive Transformation is one which will not change the input row count.
If a Transformation supplied with ‘N’ input rows and returns the same ‘N’ output rows then it is called Passive Transformation.
Ex: Expression, Sequence Generator, Lookup, etc.
2. What is the need of the source qualifier transformation in informatica?
Source qualifier is a default transformation which comes with a source, it is a replica of the source.It converts the source data types into informatica compatible datatypes. It is an  active trnasformation. We can not alter the datatypes of the ports in the source qualifier transformation.
3. What is the use of the "distinct" property in source qualifier?
The "distinct" property in the Source qualifier is used to eliminate the duplicates from the relational objects.
4. What is Pre SQL and Post SQL in source qualifier transformtion?
Pre SQL: In pre SQL the sql statements will be executed before source execution.
Ex: Create index ID on table
Creating index on source before reading the data.
Post SQL: In post sql the sql statements will be executed after source execution.
Ex: Drop index ID on table
Dropping the index on target table before loading table.
5. What are the differences between Filter and router transformation?
Filter Transformation
  • If the given condition is not matching it will reject the rows.
  • Filter works like a where class.
  • Multiple conditions re not possible in filter.
  • It gives less performance
Router Transformation
  • If the given condition is not matching it will send to the default group.
  • Router works like  case statement.
  • Multiple conditions are possible in router.
  • It gives good performance
6. What is sorter transformation?
Sorter is an active transformation which is used to sort the input data in ascending or descending order.
7. What is the use of distinct property in Sorter transformation?
  • By using the distinct property we can eliminate the duplicates from relational table or flat file.
  • In source qualifier we eliminate duplicates only for relational tables.
8. Why sorter is active transformation?
Sorter transformation eliminate the duplicates by using distinct property. The number of source rows will be different from the target rows, so sorter is an active transformation.
9. What is Aggregator transformation?
Aggregator is an active transformation which is used to perform aggregate calculations on group of records.
10. What is the use of "group by" in aggregator transformation?
If we don't specify group by on any column aggregator will process the last record as output.
11. What is the use of sorted input property in aggregator transformation?
  • If we don't select this property aggregator will not sort the data, it performs aggregation directly which will save the input.
  • If we select this property and pass unsorted data aggregator will fail the session.
12. What is aggregator cache?
Aggregator cretae two types of cache. It will improve the performance
Aggregator data cache : It stores row data.
Aggregator index cache: It stores index information
13. What is the use of sequence generator transformation?
A sequence generator is used to create unique primary key values, It will generate sequence numbers.
14. What is 'cycle' in Sequence generator?
It will repeat the sequence numbers between start and end values.
15.What is 'reset' in Sequence generator?
Reset will start from the current value.
16. What is rank transformation?
Rank is an active transformation which is used to assign the ranks on input data.
17. What is the difference between rank and dense rank?
Both rank and dense rank will assign the ranks. But dense rank will assign the consecutive ranks.
18. What is rank cache?
Rank will create cache. It has two types.
Rank data cache: It will store row data.
Rank index cache: It will store index information.
19. What is  Joiner transformation in informatica?
Joiner is active transformation which is used to join heterogeneous and homogeneous tables. We can join one oracle table and one flat file or two flat files or two relational tables.
20. What is the use of joiner when we have user defined join in source qualifier?
Source qualifier is used to join homogeneous relational objects, where as joiner is used to join both heterogeneous and homogeneous tables.
21. What are the different types of joins in informatica?
It indicates the type of join we can perform in informatica. There are 4 types of joins are there.
    Normal Join: Returns matching rows from both master and detail tables.
    Master Outer Join: All rows from detail table and matching rows from master table.
    Detail Outer Join: All rows from master and matching from detail table.
    Full Outer Join: All (Matching & Non matching) from both tables.
22. What is joiner cache?
Joiner will cretae two types of cache. It will improve the performance
Joiner data cache : It stores row data.
Joiner index cache: It stores index information.
23. In joiner transformation which table can be taken as master table?
In joiner transformation we have multiple tables, so we always make the less number of record table as master. Drag it in to the mapping designer after drag the deatail table.
24. How many joins need for 'n' tables?
For 'n' tables (sources) we need 'n-1' joins required.
25. How can we perform Cartesian join by using joiner transformation?
Create a dummy port  in both sources o returns same value(1) and use that in a joiner condition.











Powered by Blogger.