Informatica Powercenter (also simply called Informatica) is a popular ETL (Extract, Transform, Load) or data integration tool. It is a widely used tool developed by the American software development company, Informatica.
According to a Global data management company, “Informatica is in hyper-growth, and the global market is predicted to reach $146 billion by 2023, expanding at a CAGR of 12% over the forecast period 2017-23.”
Informatica is being used by enterprises across all business domains, resulting in high demand for Informatica professionals. If you are in a Business Intelligence career and are preparing for an Informatica interview, then the following commonly-asked Informatica interview questions and answers will help you ace your upcoming interview.
Top Informatica Interview Questions and Answers
Q1. Differentiate between a database, a data warehouse, and a data mart?
Ans. The database includes a set of sensibly affiliated data, which is usually small in size as compared to a data warehouse. In contrast, in a data warehouse, there are assortments of all sorts of data from where data is taken out only according to the customer’s needs. Datamart is also a set of data that is designed to cater to the needs of different domains.
Q2. What is a Sorter Transformation?
Ans. Sorter transformation helps us sort collections of data by port or ports.
Q3. What is Expression Transformation?
Ans. An expression transformation is a collective Powercenter mapping transformation.
Q4. What is Joiner Transformation?
Ans. The joiner transformation is an active and connected transformation used to join two heterogeneous sources.
Q5. What is a Decode in Informatica?
Ans. A decode in Informatica is a function used within an Expression Transformation.
Q6. What is a Router Transformation?
Ans. The Router Transformation allows users to split a single pipeline of data into multiple.
Q7. What is a Rank Transformation?
Ans. The Rank Transformation lets users sort and ranks the top or bottom set of records based on a specific port.
Q8. What is Filter Transformation?
Ans. Filter transformation is used to filter out rows in a mapping. It is active and connected.
Q9. What is a Sequence Generator Transformation?
Ans. Sequence Generator Transformation is used to generate primary fundamental values or a range of sequence numbers for calculations or processing. It is passive and connected.
Q10. What is a Master Outer Join?
Ans. A master outer join is a specific join typesetting within a joiner transformation.
Q11. What are some examples of Informatica ETL programs?
Ans. Some examples of Informatica ETL programs are mappings, workflows, and tasks.
Q12. Explain dimensional tables?
Ans. Dimension tables contain attributes that describe fact records in the table.
Q13. What is star schema?
Ans. It is the simplest form of data warehouse schema that consists of one or more dimensional and facts tables.
Q14. Describe snowflake schema?
Ans. A snowflake schema is a fact table that is connected to several dimensional tables.
Q15. What is a mapplet?
Ans. Mapplet is essential to create reusable mappings in Informatica.
Q16. What is a natural primary key?
Ans. A natural primary key uniquely identifies each record within a table and relates records to additional data stored in other tables.
Q17. What is a surrogate key?
Ans. A surrogate key is used in substitution for the natural primary key.
Q18. What is the difference between a repository server and a powerhouse?
Ans. A repository server controls the complete repository, which includes tables, charts, and various procedures, etc.
A powerhouse server governs the implementation of various processes among the factors of the server’s database repository.
Q19. How many repositories can be created in Informatica?
Ans. As many as required.
Q20. Describe Data Concatenation?
Ans. Data concatenation is the process of bringing different pieces of the record together.
Q21. How can one identify whether the mapping is correct or not without connecting the session?
Ans. With the help of debugging options.
Q22. Name the designer tools for creating transformations?
Ans. Mapping designer, transformation developer, and mapplet designer are used for creating transformations.
Q23. Differentiate between sessions and batches?
Ans. A session is a set of commands for the server to move data to the target, while a batch is a set of tasks that can include one or more tasks.
Also, Read>> Top AWS Architect Interview Questions & Answers
Q24. What is the difference between static cache and dynamic cache?
Ans. In the static cache, the data will remain the same for the entire session, whereas in the dynamic cache, whenever a row is inserted, the cache will also be updated.
Q25. What is the command used to run a batch?
Ans. The pmcmd command.
Q 26. What are the differences between the ROUTER and FILTER?
Ans. Differences between the ROUTER and FILTER are:
|Captures data rows that don’t meet the conditions to a default output group||Tests data for one condition and drops the data rows that don’t meet the condition|
|Single input and multi-output group transformation||Single input and single output group transformation|
|User can specify multiple filter conditions||User can only one filter condition|
|It does not block input rows and failed records||Chances that records can get blocked|
|Acts like IIF() function in Informatica or CASE||Works as SQL WHERE clause|
Q 27. What is Enterprise Data Warehousing?
Ans. Enterprise data warehousing is a process of creating a centralized repository of operational data so that it can be used as per the reporting and analytics requirements. It has a single point of access, and the data is provided to the server via an only source store.
Q 28. What are the different names of the Data Warehouse System?
Ans. The Data Warehouse System has the following names –
- Analytic Application
- Business Intelligence Solution
- Data Warehouse
- Decision Support System (DSS)
- Executive Information System
- Management Information System
Q 29. What is a Domain?
Ans. A Domain comprises nodes and services and serves as the fundamental administrative unit in the Informatica tool. It categorizes all related relationships and nodes into folders and sub-folders depending upon the administration requirement.
Q 30. Why should we partition a Session?
Ans. Partition not only helps in optimizing a Session but also helps in loading a colossal volume of data and improve the server’s operation and efficiency.
Q 31. What is Complex Mapping?
Ans. Complex Mapping is a mapping with huge requirements based on many dependencies. It doesn’t necessarily need to have hundreds of 100 transformations, it can be a complex map even with five odd transformations. In case the requirement has many business restrictions and constraints, it is complex mapping.
Q 32. What are the features of Complex Mapping?
Ans. The features of Complex Mapping are –
- Complicated and huge requirements
- Complex business logic
- Multiple transformations
Q 33. What is a Lookup Transformation?
Ans. Lookup Transformations are passive transformations with admission rights to RDBMS based data set. It is used to look up a source, source qualifier, or target to get the relevant data.
Q 34. What are the different types of Lookup Cache(s)?
Ans. Lookups can be cached or un-cached and can be divided as –
- Static cache
- Dynamic cache
- Persistent cache
- Shared cache
Q 35. Name different available editions of INFORMATICA PowerCenter.
Ans. Different editions of INFORMATICA PowerCenter are –
- Standard Edition
- Advanced Edition
- Premium Edition
Q 36. What are Mapplets?
Ans. Mapplets are a reusable object that can be created in the Mapplet Designer and has a set of transformations that allow the reuse of transformation logic in multiple mappings.
Q 37. What is the use of the Source Qualifier?
Ans. Source Qualifiers represent rows using the PowerCenter server that the integrations service reads during a session. The source qualifier transformation converts the source data types to the Informatica native data types, hence eliminating the need to alter the data types of the ports in the source qualifier transformation.
Q 38. Define Workflow.
Ans. Workflow is a set of multiple tasks that enable a server to communicate and get the tasks implemented. These tasks are connected with the start task link and trigger the required sequence to start a process.
Q 39. How many tools are there in the Workflow Manager?
Ans. There are three types of tools in the Workflow Manager –
- Task Developer – To create tasks that need to be run in the workflow.
- Workflow Designer – To create a workflow by connecting tasks with links.
- Worklet Designer – To create a worklet
Q40. What is a Target Load Order?
Ans. Also known as Target Load Plan, a Target Load Order specifies the order of target loading by integration service. It is dependent on the source qualifiers in a mapping.
Q41. What is the Command Task?
Ans. A Command Task is used to run the shell/UNIX commands in Windows during the workflow. It allows a user to specify UNIX commands in the command task to remove rejected files, create files, copy files, rename files, and archive files, among others.
Q42. What is a Standalone Command Task?
Ans. Standalone Command Task allows the shell commands to run anywhere during the workflow.
Q43. What is the PowerCenter Repository?
Ans. A PowerCenter Repository is a relational database like Oracle and SQL server. It consists of the following Metadata –
- ODBC Connection
- Session and session logs
- Source Definition
- Target Definition
Q44. What is the Snowflake Schema? What is its advantage?
Ans. Snowflake Schema is a logical arrangement where dimension tables are normalized in a multidimensional database. It is designed in a manner that looks like a snowflake, thus the name. It contributes to improving the Select Query performance.
Q45. What are the Different Components of PowerCenter?
Ans. A PowerCenter has eight crucial components –
- PowerCenter Service
- PowerCenter Domain
- Repository Service
- Integration Service
- PowerCenter Administration Console
- Web Service Hub
Q46. What does the PowerCenter Client application consist of?
Ans. PowerCenter Client application is comprised of the following tools:
- Mapping Architect for Visio
- Repository Manager
- Workflow Manager
- Workflow Monitor
Q47. How will you define the Tracing Level?
Ans. Tracing Level refers to the amount of information that the server writes in the session log. Tracing Level is created and configured either at –
- The transformation level
- The session-level
- Else at both the levels
Different types of Tracing Level are –
- Verbose Initialization
- Verbose Data
Q48. What is a Surrogate Key?
Ans. A Surrogate Key is any column or set of columns attached to every record in a Dimension table in a Data Warehouse. It is used as a substitute or replacement for the primary key when the update process becomes difficult for a future requirement.
Q49. What is a Session?
Ans. A session in Informatica is a set of instructions to be followed when data is being transferred from source to target using Session Command. A Session Command can be a pre-session command or a post-session command.
Q50. What is a User-Defined Event?
Ans. A User-Defined Event is a flow of tasks in a workflow. It allows users to create and name an event.
Also Read>> Google Cloud Platform Courses And Careers Guide
Q51. Explain the difference between partitioning of file targets and partitioning of the relational target?
Ans. Partitions can be accomplished on both relational and flat files. Informatica holds up the following partitions:
- Database partitioning
- Hash-Key partitioning
- Key Range partitioning
Q52. Mention what are the unsupported repository objects for a mapplet?
Ans. Following are the unsupported repository objects for a mapplet:
- COBOL source definition
- Normalizer transformations
- Pre or post-session stored procedures
- Target definitions
- Non-reusable sequence generator transformations
- Joiner transformations
- IBM MQ source definitions.
- Power mart 3.5 styles Look Up functions
- XML source definitions
Q53. Explain what are direct and indirect loading options in sessions?
Ans. The following are the differences between direct and indirect loading options in sessions:
- Direct loading is used for Single transformation, whereas indirect transformation can be used for multiple transformations or files.
- In the direction, we can perform the recovery process, but in Indirect, we can’t do it.
Q54. What is the difference between static and dynamic cache? Explain with one example.
Ans. Difference between static and dynamic cache are:
Static – Once the data is cached, it will not change, example unconnected lookup uses static cache.
Dynamic – The cache is updated to reflect the update in the table (or source) for which it is referring to. (ex. connected lookup).
Q55. Is it possible to start Batches within a batch?
Ans. It is not possible to start a batch within a batch if you want to start a batch that resides in a batch, create a new independent batch and copy the necessary sessions into the new batch.
Q56. What is the procedure to import VSAM files from source to target? Do I need a special plugin?
Ans. As far as I know, by using the power exchange tool to convert VSAM files to oracle tables then do mapping as usual to the target table.
Q57. Mention how many types of facts are there and what are they?
Ans. There are three types of facts:
- Additive fact: A fact that can be summarized by anyone of dimension or all dimensions EX: QTY, REVENUE
- Semi additive fact: a fact that can be summarized for a few dimensions, not for all dimensions. ex: current balance
- Non-additive fact: a fact that cannot be summarized by any of the dimensions. ex: percentage of profit
Q58. Mention the methods for creating reusable transformations.
Ans. There are two methods used for creating reusable transformations:
- By using the transformation developer tool.
- By converting a non-reusable transformation into a reusable transformation in mapping.
Q59. What is the procedure to use the pmcmd command in a workflow or run a session?
Ans. By using the command in the command task, there is an option pression. We can write a suitable command of pmcmd to run the workflow.
Q60. What is the default join that source qualifier provides?
Ans. Inner equi join is the default join provided by the source qualifier.
Also Read>> 7 Trending Tech Skills to Master in 2020
Q61. Explain the aggregate fact table and where is it used?
Ans. There are two types of fact tables:
- Aggregated fact table
- Factless fact table
The aggregated fact table consists of aggregated columns. Example- Total-Sal, Dep-Sal.
The factless fact table doesn’t consist of aggregated columns and it only has FK to the Dimension tables.
Q62. To provide support for mainframes source data, which files are used as a source definitions?
Ans. COBOL Copy-book files are used as a source definition.
Q63. What is the procedure to load the time dimension?
Ans. By using SCD Type 1/2/3, we can load any dimensions based on the requirement. We can also use the procedure to populate the time dimension.
Q64. Explain the difference between Informatica 7.0 and 8.0?
Ans. The main difference between Informatica 8.0 and Informatica 7.0 is that in 8.0 series Informatica corp has introduced powerexchnage concept.
Q65. Explain the difference between the summary filter and details filter?
Ans. Summary Filter- we can apply a record group that consists of common values.
Detail Filter- we can apply to each and every record in a database.
Q66. What are the differences between connected lookup and unconnected lookup?
Ans. The differences between connected lookup and unconnected lookup are:
|Connected Lookup||Unconnected Lookup|
|1. Gets the input directly from the other transformations and participates in the data flow.||1. It takes the input values from the result or the function of the LKP expression.|
|2. It can return to multiple output ports.||2. This Lookup returns to only one output port.|
|3. This can be both dynamic and static.||3. It cannot be dynamic.|
Q67. How many input parameters can be included in an unconnected lookup?
Ans. Any number of input parameters can be present in an unconnected lookup. Regardless of how many parameters are present, the return value would be only one.
For example, We can provide input parameters like column 1, column 2, column 3, and column 4 in an unconnected lookup, but there will be only one return value.
Q68. Mention the advantages of a partitioned session.
Ans. The advantages of a partitioned session in Informatica are:
- Increases the manageability, efficiency, and the operation of the server
- Involves the solo implementation sequences in the session
- Simplifies common administration tasks.
Q69. What is parallel processing in Informatica?
Ans. Parallel processing enhances the performance under hardware power. This method of the Power Center improves the performances of the Power Center by parallel data processing. It is done by using the partitioning sessions. With parallel processing, the large data set can be divided into a smaller subset to get better performance of the session.
Q70. Name the different methods for the implementation of parallel processing?
Ans. The different methods for the implementation of parallel processing are:
- Key Range
- Hash Auto-Keys
- Hash User-Keys
Q71. Name some of the mapping development practices?
Ans: The following are some of the mapping development practices in Informatica:
- Source Qualifier
Q72. Explain OLAP?
Ans: OLAP stands for Online Analytical Processing. It is used to analyze database information from multiple database systems at one time. It offers a multi-dimensional analysis of data for business decisions.
Q73. What are the different types of OLAP?
Ans: There are three types of OLAP techniques, namely:
- MOLAP (Multi-dimensional OLAP)
- ROLAP (Relational OLAP)
- HOLAP (Hybrid OLAP)
Q74. What are the advantages of using OLAP services?
Ans. The advantages of using OLAP services are as follows:
- It is a single platform for all types of analytical business needs.
- Offers consistency of information and calculations.
- It complies with regulations to safeguard sensitive data.
- Applies security restrictions on users and objects to protect data.
Q75. Explain Informatica PowerCenter.
Ans. Informatica PowerCenter is a GUI based ETL (Extract, Transform, Load) tool. This data integration tool that extracts data from different OLTP source systems, transforms it into a homogeneous format, and loads the data throughout the enterprise at any speed. It is known for its wide range of applications.
Q76. How will you filter rows in Informatica?
Ans. In Informatica, rows can be filtered in two ways:
- Source Qualifier Transformation: Rows are filtered while reading data from a relational data source.
- Filter Transformation: Rows are filtered within a mapped data from any source.
Q77. What are the different types of lookup transformation in Informatica?
Ans. The different types of lookup transformation in Informatica are:
- Relational Lookup (Flat File)
- Pipeline Lookup
- Cached/Uncached Lookup
- Connected/Unconnected Lookup
Q78. Explain pre-session and post-session shell commands?
Ans. You can call a Command task as the pre- or post-session shell command for a Session task. They can be called in COMPONENTS TAB of the session. They can be run in Pre-Session Command or Pre-Session Success Command or Post-Session Failure Command. The application of shell commands can be changed as per the use case.
Q79. Explain Junk Dimensions?
Ans. A Junk Dimension is a collection of some random codes or flags that do not belong in the fact table or any of the existing dimension tables. These attributes are unrelated to any particular dimension. The nature of these attributes is like random codes or flags, for example, non-generic comments or just yes/no.
Q80. Explain the Event and what are its types?
Ans. The event can be any action or functionality that can be implemented in a workflow. There are two types of events:
- Event Wait Task: It waits until an event occurs. The specific event for which the Event Wait task should wait can be defined. Once the event is triggered, this task gets accomplished and assigns the next task in the workflow.
- Events Raise Task: It triggers the specific event in the workflow.
Q81. What is a Fact Table? What are its different types?
Ans. A Fact table is a centralized table in the star schema. It contains summarized numerical and historical data (facts). There are two types of columns in a Fact table:
- Columns that contain the measure called facts
- Columns that are foreign keys to the dimension tables
The different types of Fact Tables are:
- Additive: These facts can be summed up through all of the dimensions in the fact table.
- Semi-Additive: The facts can be summed up for only some of the dimensions in the fact table.
- Non-Additive: The facts that cannot be summed up for any of the dimensions present in the fact table.
In case you have recently completed a professional course/certification, then