Informatica Interview Questions and Answers
ANS: Informatica is software which allows the user to create a data warehouse with ease. It makes the process of designing, ETL and maintaining the data warehouse a breeze as well. The Informatica is known to be the most trusted and widely popular brand for data integration tools, offering varied range of products to the customers.
2. Explain how the batch processing works in Informatica? When would it be useful in real-time projects?
ANS: When we run multiple sessions in a single workflow sequentially that is called batch processing. This is useful when we create relational database for any company.
3. How do you configure mapping in Informatica?
ANS: You should configure the mapping with the least number of transformations and expressions to do the most amount of work possible. You should minimize the amount of data moved by deleting unnecessary links between transformations.
Limiting the number of connected input/output or output ports reduces the amount of data the transformations store in the data cache.
4. How can you define a transformation? What are different types of transformations available in Informatica?
ANS: A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data. The various transformations available in Informatica:
Application Source Qualifier
5. Explain lookup transformation?
ANS: Transformations which have access to RDBMS based datasets are called lookup transformations. The Informatica server quickens access by using the look up tables to point at specific table data or views from the database. Final data is retrieved by matching the look up condition for all look up ports issued during transformation.
6. How to execute PL/SQL script from Informatica mapping?
ANS: Stored Procedure (SP) transformation can be used to execute PL/SQL Scripts. In SP Transformation PL/SQL procedure name can be specified. Whenever the session is executed, the session will call the pl/sql procedure.
7. What is aggregator transformation?
ANS: The Aggregator transformation allows performing aggregate calculations, such as averages and sums. Unlike Expression Transformation, the Aggregator transformation can only be used to perform calculations on groups. The Expression transformation permits calculations on a row-by-row basis only.
8. What is the difference between a connected look up and unconnected look up?
ANS: Connected lookup takes input values directly from other transformations in the pipleline.
Unconnected lookup doesn’t take inputs directly from any other transformation, but it can be used in any transformation (like expression) and can be invoked as a function using: LKP expression. So, an unconnected lookup can be called multiple times in a mapping.
9. How many types of dimensions are available in Informatica?
ANS: There are three types of dimensions available are:
10. What is redo log file in Informatica?
ANS: Redo log file stores the value only after the” Commit “Transaction Occurs.
11. What are 2 modes of data movement in Informatica Server?
ANS: There are two modes of data movement are:
Normal Mode in which for every record a separate DML stmt will be prepared and executed.
Bulk Mode in which for multiple records DML stmt will be prepared and executed thus improves performance.
12. What is a mapplet?
ANS: A mapplet is a reusable object that is created using mapplet designer. The mapplet contains set of transformations and it allows us to reuse that transformation logic in multiple mappings.
13. What does reusable transformation mean?
ANS: Reusable transformations can be used multiple times in a mapping. The reusable transformation is stored as a metadata separate from any other mapping that uses the transformation. Whenever any changes to a reusable transformation are made, all the mappings where the transformation is used will be invalidated.
14. What is update strategy and what are the options for update strategy?
ANS: Informatica processes the source data row-by-row. By default every row is marked to be inserted in the target table. If the row has to be updated/inserted based on some logic Update Strategy transformation is used. The condition can be specified in Update Strategy to mark the processed row for update or insert.
15. What is Data warehouse?
ANS: A Data warehouse is a subject oriented, integrated, time variant, non volatile collection of data in support of management’s decision making process.
There are three types of data warehouses
Enterprise Data Warehouse
ODS (operational data store)
16. What is Data mart?
ANS: A data mart is a subset of data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, data can be derived from an enterprise wide data warehouse. In an independent data mart can be collected directly from sources.
17. What is star schema?
ANS: A star schema is the simplest form of data warehouse schema that consists of one or more dimensional and fact tables.
18. Without using ETL tool can u prepare a Data Warehouse and maintain?
ANS: Yes, we can do that using PL/ SQL or Stored procedures when all the data are in the same databases. If you have source as flat files you can’t do it through PL/ SQL or stored procedures.
19. How the Informatica server increases the session performance through partitioning the source?
ANS: Partitioning the session improves the session performance by creating multiple connections to sources and targets and loads data in parallel pipe lines.
20. What happens if Informatica server doesn’t find the session parameter in the parameter file?
ANS: Workflow will fail.
21. What is meant by clustering?
ANS: It will join two (or more) tables in single buffer, will retrieve the data easily.
22. What are ETL Tools?
ANS: ETL Tools are stands for Extraction, Transformation, and Loading the data into the data warehouse for decision making. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database.
23. What is the Difference between OLTP and OLAP?
ANS: OLTP is nothing but OnLine Transaction Processing which contains a normalised tables.
But OLAP (Online Analytical Programming) contains the history of OLTP data which is non-volatile acts as a Decisions Support System.
24. Why did you use stored procedure in your ETL Application?
ANS: usage of stored procedure has the following advantages
1 checks the status of the target database
2 drops and recreates indexes
3 determines if enough space exists in the database
4 performs a specialized calculation