In this Informatica interview questions list, you will come to know the top questions asked in the Informatica job interview. The topics you will learn here include the difference between a database and a data warehouse, Informatica Workflow Manager, mapping parameter vs mapping variable, lookup transformation, aggregator transformation, connected lookup vs unconnected lookup, and more. Learn Informatica from Intellipaat Informatica Certification Course and fast-track your career!
Informatica PowerCenter is an ETL/data integration tool that has a wide range of applications. This tool allows users to connect and fetch data from different heterogenous sources and subsequently process the same.
For example, users can connect to a SQL Server Database or an Oracle Database, or both, and also integrate the data from both these databases to a third system.
Learn more about Business Objects vs Informatica in this insightful blog!
There are many typical use cases of Informatica, but this tool is predominantly leveraged in the following scenarios:
Learn for free ! Subscribe to our youtube Channel.
There are two ways to filter rows in Informatica, they are as follows:
Go through the Informatica Course in London to get a clear understanding of Informatica!
Depending upon the number of ports that are required, repositories can be created. In general, however, there can be any number of repositories.
There are four different types of lookup transformation:
Learn more about Informatica in this Informatica Training in New York to get ahead in your career!
A command task can be called as a pre- or post-session shell command for a session task. Users can run it as a pre-session command, a post-session success command, or a post-session failure command. Based on use cases, the application of shell commands can be changed or altered.
Aggregator performance improves dramatically if records are sorted before passing to the aggregator and if the ‘sorted input’ option under Aggregator Properties is checked. The record set should be sorted on those columns that are used in the Group By operation. It is often a good idea to sort the record set in the database level, e.g., inside a source qualifier transformation, unless there is a chance that the already sorted records from the source qualifier can again become unsorted before reaching the aggregator.
A target table can be updated without using ‘Update Strategy.’ For this, we need to define the key in the target table in Informatica level, and then we need to connect the key and the field we want to update in the mapping target. In the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check box.
Let us assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’ Suppose if we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key in Informatica level, and we will have to connect ‘Customer ID’ and ‘Customer Address’ fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the ‘Customer Address’ field for all matching customer IDs.
Basically, mapping parameters and mapping variables represent values in mappings and mapplets.
A surrogate key is basically an identifier that uniquely identifies modeled entities or objects in a database. Not being derived from any other data in the database, surrogate keys may or may not be used as primary keys. It is basically a unique sequential number. If an entity exists in the outside world and modeled within the database, or represents an object within the database, it is denoted by a surrogate key. In these cases, surrogate keys for specific objects or modeled entities are generated internally.
A session is nothing but a teaching set which is ought to be implemented to convert data from a source to a target. To carry out sessions, users need to leverage the session’s manager or use the pmcmd command. For combining sessions, in either a serial or a parallel manner, batch execution is used. Any number of sessions can be grouped into batches for migration.
Basically, incremental aggregation is the process of capturing changes in the source and calculating aggregations in a session. This process incrementally makes the integration service to update targets and avoids the process of calculating aggregations on the entire source.
Upon the first load, the table becomes as below:
On the next load, the data will be aggregated with the next session date.
We can delete duplicate rows from flat files by leveraging the sorter transformation and selecting the distinct option. Selecting this option will delete the duplicate rows.
From an Informatica Developer’s perspective, some of the new features in Informatica Developer 9.1.0 are as follows:
First up, Informatica is a data integration tool, while Teradata is an MPP database with some scripting and fast data movement capabilities.
Advantages of Informatica over Teradata:
Advantages of Teradata over Informatica:
Star schema is the simplest style of data mart schema in computing. It is an approach which is most widely used to develop data warehouses and dimensional data marts. It features one or more fact tables referencing to numerous dimension tables.
Snowflake Schema A logical arrangement of tables in a multidimensional database, snowflake schema is represented by centralized fact tables which are connected to multidimensional tables. Dimensional tables in a star schema are normalized using snowflaking. Once normalized, the resultant structure resembles a snowflake with the fact table at the middle. Low-cardinality attributes are removed, and separate tables are formed.
Fact Constellation Schema
Fact constellation schema is a measure of online analytical processing (OLAP), and OLAP happens to be a collection of multiple fact tables sharing dimension tables and viewed as a collection of stars. It can be seen as an extension of the star schema.
OLAP or Online Analytical Processing is a specific category of software that allows users to analyze information from multiple database systems simultaneously. Using OLAP, analysts can extract and have a look at business data from different sources or points of view.
Types of OLAP:
When a mapplet is used in a mapping, Designer allows users to set target load order for all sources that pertain to the mapplet. In Designer, users can set the target load order in which Integration Service sends rows to targets within the mapping. A target load order group is basically a collection of source qualifiers, transformations, and targets linked together in a mapping. The target load order can be set to maintain referential integrity while operating on tables that have primary and secondary keys.
Steps to Set the Target Load Order
Step 1: Create a mapping that contains multiple target load order groups Step 2: Click on Mappings and then select Target Load Plan Step 3: The Target Load Plan dialog box lists all Source Qualifier transformations with targets that receive data from them Step 4: Select a Source Qualifier and click on the Up and Down buttons to change the position of the it Step 5: Repeat Steps 3 and 4 for other Source Qualifiers if you want to reorder them Step 6: Click on OK after you are done
If we are required to perform ETL operations, we need source data, target tables, and the required transformations. Target Designer in Informatica allows us to create target tables and modify the pre-existing target definitions.
Target definitions can be imported from various sources, including flat files, relational databases, XML definitions, Excel worksheets, etc.
For opening Target Designer, click on the Tools menu and select the Target Designer option.
The advantages of Informatica as a data integration tool are many.
Interested in learning Informatica? Check out this Informatica Training in Sydney!
Available in the Workflow Manager, sessions are configured by creating a session task. Within a mapping program, there can be multiple sessions which can be either reusable or non-reusable.
Properties of Sessions
The various types of transformations are:
The features of connected lookup are as follows:
Junk dimensions are structures that consist of a group of a few junk attributes such as random codes or flags. They form a framework to store related codes with respect to a specific dimension at a single place, instead of creating multiple tables for the same.
Become a master of Informatica by going through this online Informatica Course in Toronto!
Be it active or connected, rank transformation is used to sort and rank a set of records either from the top or from the bottom. It is also used to select data with the largest or smallest numeric value based on specific ports.
Available in both passive and connected configurations, the Sequence Generator transformation is responsible for the generation of primary keys or a sequence of numbers for calculations or processing. It has two output ports that can be connected to numerous transformations within a mapplet. These ports are:
When invoked, the INITCAP function capitalizes the first character of each word in a string and converts all other characters to lowercase.
When the data of an organization is developed at a single point of access, it is known as enterprise data warehousing.
Learn more about Informatica in this Informatica Tutorial!
Database has a group of useful information which is brief in size as compared to data warehouse. In data warehouse, there are sets of every kind of data whether it is useful or not, and the data is extracted as per the requirement of the customer.
Read this blog to get a clear understanding of Data Warehousing!
The term ‘domain’ refers to all interlinked relationship and nodes that are undertaken by sole organizational point.
A repository server mainly guarantees the repository reliability and uniformity, while a powerhouse server tackles the execution of many procedures between the factors of server’s database repository.
Learn Complete Informatica Course at Hyderabad in 42 hours!
The main advantage of partitioning a session is to make the server’s process and competence better. Another advantage is that it implements the solo sequences within the session.
With the help of command task at the session level, we can create indexes after the load procedure.
Are you interested in learning Informatica from experts? Enroll in our Informatica Course in Bangalore!
A session is a teaching group that requires the transformation of information from the source to a target.
We can have any number of sessions, but it is advisable to have lesser number of sessions in a batch because it will become easier for migration.
The values that alter during the session’s implementation is known as mapping variables, whereas the values that don’t alter during the session’s implementation is known as mapping parameters.
Interested in learning Informatica? Well, we have a comprehensive Informatica Online Training Course!
The features of complex mapping are as follows:
With the help of the debugging option, we can identify whether a mapping is correct or not without connecting sessions.
Yes, we can use mapping parameters or variables into any other reusable transformation because they doesn’t have any mapplet.
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 the local buffer memory.
The transformation that has entrance right to RDBMS is known as lookup transformation.
The dimensions that are used for playing diversified roles while remaining in the same database domain are known as role-playing dimensions.
We can access repository reports by using a metadata reporter. There is no need of using SQL or other transformation as it is a web app.
The types of metadata, which is stored in repository, are Target definition, Source definition, Mapplet, Mappings, and Transformations.
Transfer of data takes place from one code page to another such that both code pages have the same character sets; then, data failure will not occur.
At a time, we can validate only one mapping. Hence, mapping cannot be validated simultaneously.
It is different from expression transformation in which we can do calculations in set, but in aggregator transformation we can do aggregate calculations such as averages, sum, etc.
It is used for performing nonaggregated calculations. We can test conditional statements before the output results are moved to the target tables.
Filter transformation is a way of filtering rows in a mapping. It has all ports of input/output, and the row which matches with that condition can only pass by that filter.
It combines two associated mixed sources located in different locations, while a source qualifier transformation can combine data rising from a common source.
Lookup transformation is used for maintaining data in a relational table through mapping. We can use multiple lookup transformations in a mapping.
It is a different input group transformation that is used to combine data from different sources.
The incremental aggregation is done whenever a session is developed for a mapping aggregate.
In a connected lookup, inputs are taken straight away from various transformations in the pipeline. While, an unconnected lookup doesn’t take inputs straight away from various transformations; it can be used in any transformations and can be raised as a function using LKP expression.
A mapplet is a recyclable object that uses a mapplet designer.
This transformation is used various times in mapping. It is different from other mappings which use the transformation as it is stored as a metadata.
Whenever a 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 ticked as Update or Insert.
When it faces DD_Reject in update strategy transformation, then it sends server to reject files.
It is a substitute for the natural prime key. It is a unique identification for each row in the table.
In order to perform session partition, one need to configure the session to partition source data and then install the Informatica server machine in multifold CPUs.
The following types of files are created during the session RUMs:
It is a mass of instruction that guides PowerCenter server about how and when to move data from sources to targets.
This task permits one or more shell commands in UNIX or DOS in Windows to run during the workflow.
This task can be used anywhere in the workflow to run the shell commands.
A predefined event is the file-watch event. It waits for a specific file to arrive at a specific location.
User-defined events are a flow of tasks in the workflow. Events can be developed and then raised as per requirement.
The group of directions that communicates with server about how to implement tasks is known as workflow.
The different tools used in Workflow Manager are:
‘CONTROL M’ is a third-party tool used for scheduling purpose.
It is a process by which multi-dimensional analysis occurs.
Take charge of your career by going through our professionally designed Informatica Certification Course!
Different types of OLAP are ROLAP, HOLAP, and DOLAP.
Worklet is said when the workflow tasks are collected in a group. It includes timer, decision, command, event wait, etc.
With the help of a Target Designer, we can create target definition.
In Workflow Monitor, we can find the throughput option. By right-clicking on session, then pressing on get run properties, and, under source/target statistics, we can find this option.
It is specified on the criteria of source qualifiers in a mapping. If there are many source qualifiers attached to various targets, then we can entitle an order in which Informatica loads data in targets.
Informatica is a tool, supporting all the steps of Extraction, Transformation, and Load (ETL) process. Nowadays, Informatica is also being used as an integration tool. Informatica is an easy-to-use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for data extraction, transformation, and load.
These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background, Informatica server takes care of fetching data from the source, transforming it, and loading it to the target.
Aggregator performance improves dramatically if records are sorted before passing to the aggregator and the ‘sorted input’ option under aggregator properties is checked. The record set should be sorted on those columns that are used in the ‘Group By’ operation. It is often a good idea to sort the record set in the database level, e.g., inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.
Informatica Lookups can be cached or uncached (no cache). A cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built, and it remains the same during the session run. On the other hand, a cache refreshed during the session run by inserting or updating the records in cache based on the incoming source data.
By default, Informatica cache is static cache. A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after the completion of session run or deletes it.
A target table can be updated without using ‘Update Strategy.’ For this, we need to define the key in the target table in Informatica level, and then we need to connect the key and the field we want to update in the mapping target. In the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check-box. Let’s assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’
If we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key in Informatica level and connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.
From an Informatica Developer’s perspective, some of the new features in Informatica 9.x are as follows:
Informatica ETL tool is the market leader in data integration and data quality services. Informatica is a successful ETL and EAI tool with significant industry coverage. ETL refers to extract, transform, and load. Data integration tools are different from other software platforms and languages.
They have no inbuilt feature to build user interface where end user can see the transformed data. Informatica ETL tool “power center” has the capability to manage, integrate, and migrate enterprise data.
The following are the advantages of Informatica:
The problem comes with traditional programming languages where we need to connect to multiple sources and then handle errors. For this, we have to write complex code. ETL tools provide a ready-made solution for this. We don’t need to worry about handling these things, and hence we can concentrate on coding the required part.
Gain expertise in Informatica by checking out our Informatica Training!
Great Work,Thank you. This will be very helpful to me. Continue this work.
Nice work good collection of question and answers.
Your email address will not be published. Required fields are marked *
Solve : * 16 + 10 =