Top 30 Informatica Analyst Interview Questions You Must Prepare 24.May.2024

A surrogate key can simply be explained as the replacement within the database of the primary key. Surrogate key acts as a unique identification factor for each row within a table. And the surrogate key will always be in the form of a digit or an integer.

The term DMT stands for Data Trformation Manager (DMT). The concept of Data Trfer Management can simply be explained. DMT is nothing but when the load manager performs respective validations among the session, it generates the DTM process.

By just simply doing sorting of records before they are passed through the aggregator the Informatica aggregator performance can be drastically enhanced. Sorting of the record set can be performed on the columns that are used in Group By operation.

A trformation in Informatica is can be interpreted as object of repository that can simply modifies, generates, or trforms the data. Informatica supports different types of trformation functions that are listed here below. 

  • Aggregator trformation
  • Expression trformation
  • Filter trformation
  • Joiner trformation
  • Lookup trformation
  • Normalizer trformation
  • Rank trformation
  • Router trformation

This is a broad concept which is extensively being widely used in mappings. By attaining a change in the reusable trformations the concerned effect will be nullified in the mappings. It is completely different from the other mappings where the trformations in mappings as they are used to store metadata.

While installing the Informatica Powercenter, the following components also gets installed as well

  • Powercenter clients
  • Integration services
  • Repository service
  • Powercenter Domain
  • Administration console for Powercenter

Active trformation:

Active trformation can simply be interpreted as the process where the number of rows which have gone through the mapping gets changed. This process is termed as an Active trformation.

Some of the Active trformations include:

  • Sorter trformations
  • Filter trformations
  •  Joiner trformations
  • Rank trformations
  • Router trformations and some other trformations as well.

 Passive trformation:

A Passive trformation can be termed as the process where after having gone through the mapping the number of rows doesn’t get changed. This process is called as Passive trformation.

Some of the Passive trformations are:

  • Expression trformation
  • Sequence Generator trformation
  • Lookup trformation
  • External procedure trformation
  • Output trformation
  • Input trformation and more.

A source qualifier trformation comes handy whenever a relational or a flat file source simply gets added to a mapping. The source qualifier trformation holds the records of all records which are read by the Informatica server whenever it the session is under the run.

A total of three dimensions can be seen in the Informatica which are stated below:

  1. Junk dimension 
  2. Conformed Dimension     
  3. Degenerative Dimension

Connected lookup and unconnected lookups are mainly differentiated by the way they take up the input values from the other trformation in the pipeline.

Connected Lookup takes the input values directly from the pipeline trformations where as the Unconnected Lookup doesn’t directly get the input from the other trformations. But the Unconnected Lookup can be used in any sort of trformations for which it can be called multiple times in the mapping.

Upon the execution of STOP command in the currently executing session task, the concerned integrating service stops reading the data from the source. However, it continuous the process of processing, writing and committing the data to their concerned targets. If the Integration

Abort command can be issued when a service stops performing processing or writing the data.

ABORT command assigns a time span of 60 seconds in which the DMT gets aborted and the session automatically gets terminated if the system doesn’t finish its tasks on the data source.

Mapplet can simply be interpreted as a set of trformations that help you build within the mapplet designer which can be availed to be used in the multiple mappings.

Error handling function in the Informatica is performed by the Status code within the concerned session. A status code gets issued by the stored procedure which simply notifies whether the stored procedure is completed successfully or not. This value will not be visible to the user. IT helps in Informatica server to simply state whether to keep the session running or to stop the session.

Within the workflow monitor of the Informatica, the throughput option can be found. You can access it by viewing the workflow monitor and then right click on the session followed by clicking on the run properties. And there we can spot the throughput option under source / target statistics.

Maplet can be validated as a rule & a rule is nothing but a logic which defines all the conditions applied to the source data. Maplet can be validated as a rule when it meets the following requirements.

  • Maplet must & should contain input and output trformations.
  • Active trformations should not be present.
  • Cardinality between input groups is not specified.

Status code is an error handling technique for the Informatica server during a session. A notification occurs in the form of a status code stating whether or not the stored procedure is successfully completed. It then helps the user to decide whether to run the procedure ahead or to stop it.

Different lookup cache is explained below:

  • Informatica Lookups can be classified into two types either cached or uncached (No cache).
  • Cached lookup can also be classified as being either static or dynamic.
  • A static cache doesn't get modified during the session run and remains constant.  
  • A dynamic cache shows variation in the results during the session run. This can be done by simply inserting or updating the records in the incoming source data. Informatica cache will be in the state of static cache by the default.
  • Well, coming to the Lookup cache it also gets divided into two different categories.This includes persistent or non-persistent based lookup caches.

  • A Source qualifier performs the following tasks.
  • It simply joins different data which are originating from the same source of database.
  • It automatically records the data when the Informatica reads a data.
  • It creates a custom query that helps in issuing a special SELECT statement in the server to read a specific data source.

In case if you have to make any session partition then you need to begin with configuring the session to partition to source data & then the next thing which is to be performed is installing the Informatica server machine in a different CPU which is also known as the multifold CPU.

This aggregate cache is the place where the aggregate stores the data until the completion of the aggregate calculations. In the case of a session which performs a aggregator trformation, the server being used by the Informatica will be creating an index & it is here where the data catches the memory in to commence with the process of the ongoing trformation. And also, in the cases where the server requires additional space, the cache files will be handy to store these overflow values.

In general, Informatica is considered to be having about two different types of leading techniques. One is “Normal Loading” & the other is “Bulk Loading” Normal Loading is considered be tremendously time taking process as it has to load the records one after the other and a log can be written for every loading. In Bulk Loading process, it has the scope of loading a number of records at a same time to the target database. This ensures saving a lot of time in delivering data to the target.

A parameter file can be interpreted as a file which is mainly been created in a text editor or a word pad.

The different values which can be defined in a parameter file are:

  • Mapping parameters
  • Mapping variables
  • Session parameters

A session task can simply be interpreted as a set of instructions that are guided towards a power center server where it gets decided when the data is needed to be trferred from the source region to the targets.

“A Data warehouse is a collection of data which is a great help in strengthening the management decision making process. It consists of time variant, subject-oriented, integrated, and non volatile collection of data. Bill Inman is called as the father of Data warehousing.

The major difference between the “Static Cache” & “Dynamic Cache” are as follows:

Dynamic cache: Dynamic cache greatly decreases the systems total performance and productivity much to the contrast in comparison with the static cache

Static Cache: Static cache can simply be interpreted as a process where the data gets inserted all the time. It doesn’t mind the number of times a particular data is being inserted; all that it cares is to insert the data.

Target and Order are one among the major concepts of Informatica. Target load order can simply be interpreted as a list of all the activities one can easily define the priority based on which it will become easy to load the data into the Informatica server.  In the case where you are having a list of source qualifiers which are connected to multiple targets then you can define the order in which data can be loaded into the targets in the Informatica servers.

Session: A session can also simply be interpreted as a complete group of instructions which states how and when exactly to move the data from the source to its respective targets.

Mapping: Mapping can be defined as primarily linked set of source and the target by trformation of objects which in general define the rules for trformation.

Repository is one among the eminent concept of Informatica. Informatica repository is present at the center of the Informatica suite. A set of metadata tables can be created within the repository database where it can be accessed by the Informatica application and tools. The repository is then accessed by the Informatica client and server for the function of saving and retrieving the metadata.

Between the Informatica server and the stored procedure, a total of three types of data pass between them.

The three different data types include:

  1. Input / Output parameters
  2. Status code
  3. Return Values

A “Session” and a “Batch” can be defined as:

Session: A session can be interpreted as a group of commands which lets the server describe to move the targeted data.

Batch: A Batch can be interpreted as a collection of tasks which also covers one or more number of tasks.