Top 50 Informatica interview Questions and Answers 2018
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 Power Mart
INFORMATICA Power Exchange
INFORMATICA Power Analysis
INFORMATICA Power Quality
We need INFORMATICA while working with data systems which 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:
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:
The current version of PowerCenter available is v10 with a high-performance increase.
5.Differentiate between joiner and Lookup Transformation.
Below are the differences between lookup and joiner transformation:
In lookup we can override the query but in joiner we cannot.
In lookup we can provide different types of operators like – “>,<,>=,<=,!=” but, in joiner only “= “ (equal to )operator is available.
In lookup we can restrict the number of rows while reading the relational table using lookup override but, in joiner we cannot restrict the number of rows while reading.
In joiner we can join the tables based on- Normal Join, Master Outer, Detail Outer and Full Outer Join but, in lookup this facility is not available .Lookup behaves like Left Outer Join of database.
6.What is meant by Lookup Transformation? Explain the types of Lookup transformation.
Lookup transformation in a mapping is used to look up data in a flat file, relational table, view, or synonym. We can also create a lookup definition from a source qualifier.
We have the following types of Lookup.
Relational or flat file lookup. To perform a lookup on a flat file or a relational table.
Pipeline lookup. To perform a lookup on application sources such as JMS or MSMQ.
Connected or unconnected lookup.
7.How can you increase the performance in joiner transformation?
Below are the ways in which you can improve the performance of Joiner Transformation.
Perform joins in a database when possible.
In some cases, this is not possible, such as joining tables from two different databases or flat file systems. To perform a join in a database, we can use the following options:
Create and Use a pre-session stored procedure to join the tables in a database.
Use the Source Qualifier transformation to perform the join.
Join sorted data when possible
For an unsorted Joiner transformation, designate the source with fewer rows as the master source.
For a sorted Joiner transformation, designate the source with fewer duplicate key values as the master source.
8.What are the types of Caches in lookup?
Un- cached lookup– Here, the lookup transformation does not create the cache. For each record, it goes to the lookup Source, performs the lookup and returns value. So for 10K rows, it will go the Lookup source 10K times to get the related values.
Cached Lookup– In order to reduce the to and fro communication with the Lookup Source and Informatica Server, we can configure the lookup transformation to create the cache. In this way, the entire data from the Lookup Source is cached and all lookups are performed against the Caches.
9.Why update strategy and union transformations are Active?
The Update Strategy changes the row types. It can assign the row types based on the expression created to evaluate the rows. Like IIF (ISNULL (CUST_DIM_KEY), DD_INSERT, DD_UPDATE). This expression, changes the row types to Insert for which the CUST_DIM_KEY is NULL and to Update for which the CUST_DIM_KEY is not null.
The Update Strategy can reject the rows. Thereby with proper configuration, we can also filter out some rows. Hence, sometimes, the number of input rows, may not be equal to number of output rows.
10.I have two different source structure tables, but I want to load into single target table? How do I go about it? Explain in detail through mapping flow.
We can use joiner, if we want to join the data sources. Use a joiner and use the matching column to join the tables.
We can also use a Union transformation, if the tables have some common columns and we need to join the data vertically. Create one union transformation add the matching ports form the two sources, to two different input groups and send the output group to the target.
The basic idea here is to use, either Joiner or Union transformation, to move the data from two sources to a single target. Based on the requirement, we may decide, which one should be used.
11.What is Dimensional Table?
Dimension table is the one that describes business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, products etc.
12.What is Fact Table? Explain the different kinds of Facts.
The centralized table in the star schema is called the Fact table. A Fact table typically contains two types of columns. Columns which contains the measure called facts and columns, which are foreign keys to the dimension tables. The Primary key of the fact table is usually the composite key that is made up of the foreign keys of the dimension tables.
Types of Facts in Data Warehouse
A fact table is the one which consists of the measurements, metrics or facts of business process. These measurable facts are used to know the business value and to forecast the future business. The different types of facts are explained in detail below.
Additive facts are facts that can be summed up through all of the dimensions in the fact table. A sales fact is a good example for additive fact.
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Eg: Daily balances fact can be summed up through the customers dimension but not through the time dimension.
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
Eg: Facts which have percentages, ratios calculated.
Factless Fact Table:
In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called “Factless Fact tables”.
13.Differentiate between Reusable Transformation and Mapplet.
Any Informatica Transformation created in the Transformation Developer or a non-reusable promoted to reusable transformation from the mapping designer which can be used in multiple mappings is known as Reusable Transformation.
When we add a reusable transformation to a mapping, we actually add an instance of the transformation. Since the instance of a reusable transformation is a pointer to that transformation, when we change the transformation in the Transformation Developer, its instances reflect these changes.
A Mapplet is a reusable object created in the Mapplet Designer which contains a set of transformations and lets us reuse the transformation logic in multiple mappings.
A Mapplet can contain as many transformations as we need. Like a reusable transformation when we use a mapplet in a mapping, we use an instance of the mapplet and any change made to the mapplet is inherited by all instances of the mapplet.
14.What do you understand by a term domain?
Domain is the term in which all interlinked relationship and nodes are under taken by sole organizational point.
15.Differentiate between a repository server and a powerhouse?
Repository server mainly guarantees the repository reliability and uniformity while powerhouse server tackles the execution of many procedures between the factors of server’s database repository.
16.In Informatica WorkFlow Manager, how many repositories can be created?
It mainly depends upon the number of ports we required but as general there can be any number of repositories.
Go through this Informatica Tutorial to learn more about Informatica.
17.Write the advantages of partitioning a session?
The main advantage of partitioning a session is to get better server’s process and competence. Other advantage is it implements the solo sequences within the session.
18.How we can create indexes after completing the load process?
With the help of command task at session level we can create indexes after the load procedure.
19.Define sessions in Informatica ETL.
Session is a teaching group that requires to be to transform information from source to a target.
20.In one group how many number of sessions can we have?
We can have any number of session but it is advisable to have lesser number of session in a batch because it will become easier for migration.
21.Differentiate between mapping parameter and mapping variable?
At the time values alter during the session’s implementation it is known as mapping variable whereas the values that don’t alter within the session implementation is called as mapping parameters.
Interested in learning Informatica? Well, we have the comprehensive Informatica Online Training Course.
22.What are the features of complex mapping?
The features of complex mapping are:
Many numbers of transformations
tricky needscompound business logic
23.How we can identify whether mapping is correct or not without connecting session?
With the help of debugging option we can identify whether mapping is correct or not without connecting sessions.
24.Can we use mapping parameter or variables developed in one mapping into any other reusable transformation?
Yes, we can use mapping parameter or variables into any other reusable transformation because it doesn’t have any mapplet.
25.What is the use of aggregator cache file?
If extra memory is needed aggregator provides extra cache files for keeping the transformation values. It also keeps the transitional value that are there in local buffer memory.
26.What is lookup transformation?
The transformation that has entrance right to RDBMS Is known as lookup transformation.
27.What do you understand by term role playing dimension?
The dimensions that are used for playing diversified roles while remaining in the same database domain are known as role playing dimensions.
28.How we can access repository reports without SQL or other transformations?
We can access repository reports by using metadata reporter. No need of using SQL or other transformation as it is a web app.
29.Write the types of metadata those stores in repository?
The types of metadata which is stored in repository are Target definition, Source definition, Mapplet, Mappings, Transformations.
30.What is code page compatibility?
Transfer of data take place from one code page to another keeping that both code pages have the same character sets then data failure cannot occur.
30.How we can confirm all mappings in the repository simultaneously?
At a time we can validate only one mapping. Hence mapping cannot be validated simultaneously.
31.Define Aggregator transformation?
It is different from expression transformation in which we can do calculations in set but here we can do aggregate calculations such as averages, sum, etc.
32.What is Expression transformation?
It is used for performing non aggregated calculations. We can test conditional statements before output results move to the target tables.
33.Define filter transformation?
Filter transformation is a way of filtering rows in a mapping. It have all ports of input/output and the row which matches with that condition can only pass by that filter.
34.Define Joiner transformation?
It combines two associated mixed sources located in different locations while a source qualifier transformation can combine data rising from a common source.
35.What do you mean by Lookup transformation?
Lookup transformation is used for maintaining data in a relational table through mapping. We can use multiple lookup transformation in a mapping.
Go through our Informatica Training Video Course to learn more.
36.How we can use Union Transformation?
It is a different input group transformation that is used to combine data from different sources.
Define Incremental Aggregation?
The incremental aggregation is done whenever a session is developed for a mapping aggregate.
37.Differentiate between a connected look up and unconnected look up?
In connected lookup inputs are taken straight away from various transformations in the pipeline it is called connected lookup. While unconnected lookup doesn’t take inputs straight away from various transformations, but it can be used in any transformations and can be raised as a function using LKP expression.
A mapplet is a recyclable object that is using mapplet designer.
39.What is reusable transformation?
This transformation is used various times in mapping. It is divest from other mappings which use the transformation as it is stored as a metadata.
40.Define update strategy.
Whenever the row has to be updated or inserted based on some sequence then update strategy is used. But in this condition should be specified before for the processed row to be tick as update or inserted.
41.Explain the scenario which compels informatica server to reject files?
When it faces DD_Reject in update strategy transformation then it sends server to reject files.
42.What is surrogate key?
It is a substitute for the natural prime key. It is a unique identification for each row in the table.
43.Write 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.
44.In informatics server Which files are created during the session rums?
Errors log, Bad file, Workflow low and session log namely files are created during the session rums.
45.Define a session task?
It is a mass of instruction that guides power center server about how and when to move data from sources to targets.
46.Define command task?
This task permits one or more than one shell commands in UNIX or DOS in windows to run during the workflow.
47.Explain standalone command task?
This task can be used anywhere in the workflow to run the shell commands.
48.What is 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.
49.What is predefined event?
Predefined event are the file-watch event. It waits for a specific file to arrive at a specific location.
50.Define user defied event?
User defined event are a flow of tasks in the workflow. Events can be developed and then raised as need comes.