INFORMATICA Interview Questions for freshers experienced :-
1. What is Informatica? Why do we need it?
Informatica is a software development firm which offers some data integration solution for ETL, data virtualization, master data management, data quality, data replica, ultra messaging etc.
Some of the popular Informatica products are:
- Informatica PowerCenter
- Informatica PowerConnect
- Informatica Power Mart
- Informatica Power Exchange
- Informatica Power Analysis
- Informatica Power Quality
We need Informatica while working with data systems that contain data to perform certain operations along with a set of rules. Informatica facilitates operations line cleaning and modifying data from structured and unstructured data systems.
2. What is the format of Informatica objects in a repository? What are the databases that Informatica can connect to Windows?
Informatica objects can be written in XML format.
Following is the list of databases that Informatica can connect to:
- SQL Server
- Oracle
- MS Access
- MS Excel
- DB2
- Sybase
- Teradata
3. What is Informatica PowerCenter?
Informatica PowerCenter is an ETL/Data Integration Tool which is used to connect and retrieve data from different sources and data processing. PowerCenter processes a high volume of data and supports data retrieval from ERP sources such as SAP, PeopleSoft, etc.
You can connect PowerCenter to database management systems like SQL and Oracle to integrate data into the third system.
4. Which are the different editions of Informatica PowerCenter that are available?
Different editions of Informatica PowerCenter are:
- Standard Edition
- Advance Edition
- Premium Edition
The current version of PowerCenter available is v10 with a high-performance increase.
5. How many repositories can be created in informatica?
There can be any number of repositories in informatica but eventually it depends on number of ports.
6. What is the benefit of partitioning a session?
Partitioning a session means solo implementation sequences within the session. It’s main purpose is to improve server’s operation and efficiency. Other transformations including extractions and other outputs of single partitions are carried out in parallel.
7. How are indexes created after completing the load process?
For the purpose of creating indexes after the load process, command tasks at session level can be used. Index creating scripts can be brought in line with the session’s workflow or the post session implementation sequence. Moreover this type of index creation cannot be controlled after the load process at transformation level.
8. Explain sessions. Explain how batches are used to combine executions?
A teaching set that needs to be implemented to convert data from a source to a target is called a session. Session can be carried out using the session’s manager or pmcmd command. Batch execution can be used to combine sessions executions either in serial manner or in a parallel. Batches can have different sessions carrying forward in a parallel or serial manner.
9. How many number of sessions can one group in batches?
One can group any number of sessions but it would be easier for migration if the number of sessions are lesser in a batch.
10. Explain the difference between mapping parameter and mapping variable?
When values change during the session’s execution it’s called a mapping variable. Upon completion the Informatica server stores the end value of a variable and is reused when session restarts. Moreover those values that do not change during the sessions execution are called mapping parameters. Mapping procedure explains mapping parameters and their usage. Values are allocated to these parameters before starting the session.
11.What is complex mapping?
Following are the features of complex mapping.
- Difficult requirements
- Many numbers of transformations
- Complex business logic
12. How can one identify whether mapping is correct or not without connecting session?
One can find whether the session is correct or not without connecting the session is with the help of debugging option.
13. Can one use mapping parameter or variables created in one mapping into any other reusable transformation?
Yes, One can do because reusable transformation does not contain any mapplet or mapping.
14. Explain the use of aggregator cache file?
Aggregator transformations are handled in chunks of instructions during each run. It stores transitional values which are found in local buffer memory. Aggregators provides extra cache files for storing the transformation values if extra memory is required.
15. Briefly describe lookup transformation?
Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the look up condition for all look up ports delivered during transformations.
16. What does role playing dimension mean?
The dimensions that are utilized for playing diversified roles while remaining in the same database domain are called role playing dimensions.
17. How can repository reports be accessed without SQL or other transformations?
Repositoryreports are established by metadata reporter. There is no need of SQL or other transformation since it is a web app.
18. What are the types of metadata that stores in repository?
The types of metadata includes Source definition, Target definition, Mappings, Mapplet, Transformations.
19. Explain the code page compatibility?
When data moves from one code page to another provided that both code pages have the same character sets then data loss cannot occur. All the characteristics of source page must be available in the target page. Moreover if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due the fact the two code pages are not compatible.
20. How can you validate all mappings in the repository simultaneously?
All the mappings cannot be validated simultaneously because each time only one mapping can be validated.
21. Briefly explain the Aggregator transformation?
It allows one to do aggregate calculations such as sums, averages etc. It is unlike expression transformation in which one can do calculations in groups.
22. Describe Expression transformation?
Values can be calculated in single row before writing on the target in this form of transformation. It can be used to perform non aggregate calculations. Conditional statements can also be tested before output results go to target tables.
23. What do you mean by filter transformation?
It is a medium of filtering rows in a mapping. Data needs to be transformed through filter transformation and then filter condition is applied. Filter transformation contains all ports of input/output, and the rows which meet the condition can only pass through that filter.
24. What is Joiner transformation?
Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.
25. What is Lookup transformation?
It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.
26. How Union Transformation is used?
Ans: It is a diverse input group transformation which can be used to combine data from different sources. It works like UNION All statement in SQL that is used to combine result set of two SELECT statements.
27. What do you mean Incremental Aggregation?
Option for incremental aggregation is enabled whenever a session is created for a mapping aggregate. Power center performs incremental aggregation through the mapping and historical cache data to perform new aggregation calculations incrementally.
28. What is the difference between a connected look up and unconnected look up?
When the inputs are taken directly from other transformations in the pipeline it is called connected lookup. While unconnected lookup doesn’t take inputs directly from other transformations, but it can be used in any transformations and can be raised as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping.
29. What is a mapplet?
A recyclable object that is using mapplet designer is called a mapplet. It permits one to reuse the transformation logic in multitude mappings moreover it also contains set of transformations.
30.Briefly define reusable transformation?
Reusable transformation is used numerous times in mapping. It is different from other mappings which use the transformation since it is stored as a metadata. The transformations will be nullified in the mappings whenever any change in the reusable transformation is made.
31. What does update strategy mean, and what are the different option of it?
Row by row processing is done by informatica. Every row is inserted in the target table because it is marked as default. Update strategy is used whenever the row has to be updated or inserted based on some sequence. Moreover the condition must be specified in update strategy for the processed row to be marked as updated or inserted.
32. What is the scenario which compels informatica server to reject files?
This happens when it faces DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in the rows was condensed.
33. What is surrogate key?
Surrogate key is a replacement for the natural prime key. It is a unique identification for each row in the table. It is very beneficial because the natural primary key can change which eventually makes update more difficult. They are always used in form of a digit or integer.
34.What are the prerequisite tasks to achieve the session partition?
In order to perform session partition one need to configure the session to partition source data and then installing the Informatica server machine in multifold CPU’s.
35. Which files are created during the session rums by informatics server?
During session runs, the files created are namely Errors log, Bad file, Workflow low and session log.
36. Briefly define a session task?
It is a chunk of instruction the guides Power center server about how and when to transfer data from sources to targets.
37,What does command task mean?
This specific task permits one or more than one shell commands in UNIX or DOS in windows to run during the workflow.
38. What is standalone command task?
This task can be used anywhere in the workflow to run the shell commands.
39. What is meant by pre and post session shell command?
Command task can be called as the pre or post session shell command for a session task. One can run it as pre session command r post session success command or post session failure command.
40.What is predefined event?
It is a file-watch event. It waits for a specific file to arrive at a specific location.
41. How can you define user defied event?
User defined event can be described as a flow of tasks in the workflow. Events can be created and then raised as need arises.
42. What is a work flow?
Ans: Work flow is a bunch of instructions that communicates server about how to implement tasks.
43. What are the different tools in workflow manager?
Following are the different tools in workflow manager namely
- Task Designer
- Task Developer
- Workflow Designer
44. Tell me any other tools for scheduling purpose other than workflow manager pmcmd?
The tool for scheduling purpose other than workflow manager can be a third party tool like ‘CONTROL M’.
45. What is OLAP (On-Line Analytical Processing?
A method by which multi-dimensional analysis occurs.
46. What are the different types of OLAP? Give an example?
ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP
47. What do you mean by worklet?
When the workflow tasks are grouped in a set, it is called as worklet. Workflow tasks includes timer, decision, command, event wait, mail, session, link, assignment, control etc.
48. What is the use of target designer?
Target Definition is created with the help of target designer.
49. Where can we find the throughput option in informatica?
Throughput option can be found in informatica in workflow monitor. In workflow monitor, right click on session, then click on get run properties and under source/target statistics we can find throughput option.
50. What is target load order?
Ans: Target load order is specified on the basis of source qualifiers in a mapping. If there are multifold source qualifiers linked to different targets then one can entitle order in which informatica server loads data into targets.
51. What do you mean by Enterprise Data Warehousing?
When the organization data is created at a single point of access it is called as enterprise data warehousing. Data can be provided with a global view to the server via a single source store. One can do periodic analysis on that same source. It gives better results but however the time required is high.
52. What the difference is between a database, a data warehouse and a data mart?
Database includes a set of sensibly affiliated data which is normally small in size as compared to data warehouse. While in data warehouse there are assortments of all sorts of data and data is taken out only according to the customer’s needs. On the other hand datamart is also a set of data which is designed to cater the needs of different domains. For instance an organization having different chunk of data for its different departments i.e. sales, finance, marketing etc.
53. What is meant by a domain?
When all related relationships and nodes are covered by a sole organizational point, its called domain. Through this data management can be improved.
54. What is the difference between a repository server and a powerhouse?
Repository server controls the complete repository which includes tables, charts, and various procedures etc. Its main function is to assure the repository integrity and consistency. While a powerhouse server governs the implementation of various processes among the factors of server’s database repository.
55. Explain the features of the Connected and Unconnected lookup.
The features of Connected Lookup can be explained as follows:
- There is a direct source of input from the pipeline for connected lookup.
- It has active participation in data flow and both dynamic as well as static cache is used as the case is.
- It caches all lookup columns and returns the default values as the output when the lookup condition does not match.
- More than one column values can be returned to the output port.
- Multiple output values are passed as well as output ports are linked to another transformation.
- Connected lookup supports user-defined default values.
The features of unconnected lookup can be explained as follows:
- Unconnected lookup uses static cache and its source of input is the result received from the output of LKP expression.
- It caches only the lookup output ports and returns the value as NULL when the lookup condition does not match.
- Only one column is returned from each port.
- Only one output value is passed to another transformation.
- User-defined default values are not supported by unconnected lookup.
56. During the running session, output files are created by the INFORMATICA server. Enlist a few of them.
Mentioned below are the few output files:
- Cache files: These files are created at the time of memory cache creation. For circumstances like Lookup transformation, Aggregator transformation, etc index and data cache files are created by the INFORMATICA server.
- Session detail file: As the name defines, this file contains load statistics like table name, rows rejected or written for each target in mapping and can be viewed in the monitor window.
- Performance detail file: This file is a part of the session property sheet and contains session performance information in order to determine improvement areas.
INFORMATICA server log: The server creates a log for all status and error messages and can be seen in the INFORMATICA home directory. - Session log file: For each session, the server creates a session log file depending on the set tracing level. The information that can be seen in log files about sessions can be:
1. Session initialization process,
2. SQL commands creation for reader and writer threads,
3. List of errors encountered and
4. Load summary - Post-session email: This helps in communicating the information about the session (session completed/session failed) to the desired recipients automatically.
- Reject file: This file contains information about the data that has not been used/written to targets.
- Control file: In case, when the session uses the external loader, control file consists of loading instructions and data format about the target file.
- Indicator file: This file basically contains a number that highlights the rows marked for INSERT/UPDATE/DELETE or REJECT.
- Output file: The output file is created based on the file properties.
57. How to differentiate between the Active and Passive transformations?
To understand the difference between Active and Passive transformations, let us see its features which will explain the differences in a better way.
The action performed by Active transformations includes:
- Changing the number of rows as per the requirement, that passes through the transformations. For Example, Filter transformation that deletes the row that does not meet the condition.
- Changing the transaction boundary by setting the rollback and commit points. For Example, Transaction control transformation.
- Changing the row type for INSERT/ UPDATE/DELETE or REJECT.
The action performed by Passive transformations includes:
- The number of rows passing through the transformation is never changed.
- The transaction boundary is set.
- Row type is set.
58. Enlist the various Types of Transformations.
The various types of transformations are as follows:
- Aggregator transformation
- Expression transformation
- Normalizer transformation
- Rank transformation
- Filter transformation
- Joiner transformation
- Lookup transformation
- Stored procedure transformation
- Sorter transformation
- Update strategy transformation
- XML source qualifier transformation
- Router transformation
- Sequence Generator transformation
59. What is Dynamic Cache?
INFORMATICA lookups can be categorized either as cached or uncached. In the case of Dynamic cache, rows can be inserted or deleted at the time of passing the rows and is synchronized with the target. The cache memory is refreshed every time after delete/update operations within the session.
60. What is decode in Static cache?
Static cache is the one that is neither updated nor refreshed in the session run. It is the default cache and returns the value only when the return condition is true. In other cases, it returns Null value. Insert or Update cache cannot be performed in this case.
61. Mention a few advantages of Router transformation over Filter transformation.
Router transformation and Filter transformation are the same because both of them use a condition to test and filter the data.
However, the advantages of Router over filter transformation can be understood by the below-mentioned points.
Router Transformation:
- It allows more than one test condition.
- Provide the ability to test the same input data on multiple numbers of conditions.
- In the case of mapping, input data is processed only once by the server and hence performance is improved.
- Less complex and more efficient.
- The records that fail the test condition are never blocked instead are passed on to the default group.
62. Enlist some properties of sessions.
A session is available in the workflow manager and is configured by creating a session task. Within a mapping program, there can be multiple sessions and it can be either reusable or non-reusable.
Some of the properties of the session are as follows:
- As per the requirement, session tasks can be run either concurrently or sequentially.
- A session can be configured to analyze the performance.
- To create or run a session task, it is required to have general information about Session name, schedule and integration service.
- Other important property of session includes Session log file, the test load, error handling, commit interval, target properties, etc.
63. Enlist the tasks for which Source qualifier transformation is used.
Source qualifier is considered as an active transformation that reads the rows that are involved in integration service within the running session. It determines the way in which the data is fetched from the source and is automatically added while adding a source to mapping.
The list of different tasks where source qualifier is used is as follows:
- Rows filtering
- Data sorting
- Custom query creation
- Joining tables from the same source
- Selecting distinct values
64. Mention a few Power Centre client applications with their basic purpose?
Tasks like session and workflow creation, monitoring workflow progress, designing mapplets, etc are performed by Powercentre client applications.
Enlisted below is the list of Power center client applications with their purpose:
Repository Manager: It is an administrative tool and its basic purpose is to manage repository folders, objects, groups, etc.
Administration Console: Here the service tasks like start/stop, backup/restore, upgrade/delete, etc are performed.
Power center designer: The designer consists of various designing tools that serve various purposes. These designing tools are:
- Source Analyzer
- Target designer
- Transformation Developer
- Mapplet Designer
- Mapping Manager
Workflow Manager: Its basic purpose is to define a set of instructions/workflow that is required to execute mappings designed in the designer. To help develop a workflow, there are 3 tools available, namely Task developer, Workflow designer, Worklet Designer.
Workflow Monitor: As the name suggests, the Workflow monitor, monitors the workflow or tasks. The list of windows available are:
- Navigator Window
- Output window
- Time window
- Properties window
- Task view
- Gantt chart view
65. If You Want To Create Indexes After The Load Process Which Transformation You Choose?
Its usually not done in the mapping (transformation) level. Its done in session level. Create a command task which will execute a shell script (if Unix) or any other scripts which contains the create index command. Use this command task in the workflow after the session or else, You can create it with a post session command.