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 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.

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

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.

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.

Ans: Given below are the differences between PowerCenter and Power Map.

1. Processes high volume of data Processes low volume of data
2. Supports global and local repositories Supports only local repositories
3. Supports data retrieval from ERP
Sources like SAP, PeopleSoft etc.
Do not support
Data retrieval from ERP sources
4. Converts local repositories to global Do not convert local repositories to global

Ans: Given below are the 7 important components of PowerCenter:

  • PowerCenter Service
  • PowerCenter Clients
  • PowerCenter Repository
  • PowerCenter Domain
  • Repository Service
  • Integration Service
  • PowerCenter Administration Console
  • Web Service Hub

Ans: Here is the list of PowerCenter clients:

  • PowerCenter designer
  • PowerCenter workflow monitor
  • PowerCenter workflow manager
  • PowerCenter repository manager

Ans: PowerCenter Repository is a Relational Database or a system database that contains metadata such as,

  • Source Definition
  • Target Definition
  • Session and Session Logs
  • Workflow
  • ODBC Connection
  • Mapping

There are two types of Repositories:

  • Global Repositories
  • Local Repositories

PowerCenter Repository is required to perform Extraction, Transformation, and Loading(ETL) based on metadata.

Ans: Tracing Level can be defined as the amount of information that the server writes in the log file. Tracing Level is created and configured either at the transformation level or at session level else at both the levels.

Given below are the 4 types of tracing level:

  • None
  • Terse
  • Verbose Initialization
  • Verbose Data

Integration Services control the workflow and execution of PowerCenter processes.

Integration Service Process: It is called as pmserver, Integration Service can start more than one processes to monitor the workflow.

Load Balancing: Load Balancing refers to distributing the entire workload across several nodes in the grid. Load Balancer conducts different tasks that include commands, sessions etc.

Data Transformation Manager(DTM): Data Transformation Manager allows to perform the following data transformations:

Ans: INFORMATICA has the feature of Grid Computing which can be utilized for largest data scalability in order to the performance. Grid feature is used for load balancing and parallel processing.

PowerCenter domains contain a set of multiple nodes to configure the workload and then run it on the Grid.

A domain is a foundation for efficient service administration served by the PowerCenter.

Node is an independent physical machine which is logically represented for running the PowerCenter environment.

Ans: When a large amount of data is assembled at a single access point then it is called as Enterprise Data Warehousing. This data can be reused and analyzed at regular intervals or as per the need of the time requirement.

Considered as the central database or say a single point of access, Enterprise data warehousing provides a complete global view and thus helps in decision support.

It can be more understood from the following points which define its features:

  • All important business information stored in this unified database can be accessed from anywhere across the organization.
  • Although the time required is more, periodic analysis on this single source always produces better results.
  • Security and integrity of data are never compromised while making it accessible across the organization.

Ans: While Integration Service is running in the environment the workflow is partitioned for better performance. These partitions are then used to perform Extraction, Transformation, and Loading.

Ans: Command Tasks are used to create Index. Command Task scripts can be used in a session of workflow to create an index.

Ans: Session is a Set of Instructions that are used while moving data from the source to destination. We can partition the session to implement several sequences of sessions to improve the server performance.

After creating session we can use server manager or command line program pmcmd to stop or start the session.

Ans: Batches are the collection of sessions which is used to migrate the data from the source to target on a server. Batches can have the largest number of sessions in it but they cause more network traffic whereas less number of sessions in a batch can be moved rapidly.

Ans: Mapping is a collection of source and targets which are linked with each other through certain sets of transformations such as Expression Transformation, Sorter Transformation, Aggregator Transformation, Router Transformation etc.

Ans: Transformation can be defined as a set of rules and instruction that are to be applied to define data flow and data load at the destination.

Ans: It is a mapping transformation which is used to transform data in one record at a time. Expression Transformation can be passive or connected. The expression is used for data manipulation and output generation using conditional statements.

Ans: Update Strategy in Informatica is used to control data passing through it and tag it such as INSERT, UPDATE, DELETE and REJECT. We can set a conditional Logic within the Update Strategy Transformation to tag it.

Ans: Sorter Transformation is used to sort large volume of data through multiple ports. It is much likely to work as the ORDER BY Clause in SQL. Sorter Transformation can be Active, Passive or Connected.

Active Transformation passes through Mapping and changes a number of rows whereas Passive Transformation passes through Mapping but does not change the number of rows.

Most of the INFORMATICA Transformations are Connected to the Data Path.

Ans: Router Transformation is used to filter the source data. You can use Router Transformation to split out a  single data source.

It is much like Filter Transformation but the only difference is that Filter Transformation uses only one transformation condition and returns the rows that do not fulfill the condition, Whereas Router Transformation uses multiple transformation conditions and returns the rows that match even a single condition.

Ans: Rank Transformation is Active as well as Connected. It is used to sort and rank a set of records either top or bottom.

Rank Transformation is also used to select data with a largest or smallest numeric value based on a specific port.

Ans: Rank Index is assigned by the task designer to each record. The rank index port is used to store ranking position for each row. Rank Transformation identifies each row from the top to bottom and then assigns Rank Index.

Ans: Code provides Error Handling Mechanism during each session. Status Code is issued by stored Procedure to recognize whether it is committed successfully or not and provides information to the INFORMATICA server to decide whether the session has to be stopped or continued.

Ans: Junk Dimension is a structure that consists a group of some junk attributes such as random codes or flags. It forms a framework to store related codes with respect to a specific dimension at a single place instead of creating multiple tables for the same.

Ans: Mapplet is a reusable object in INFORMATICA that contains a certain set of rules for transformation and transformation logic that can be used in multiple mappings. Mapplet is created in Mapplet Designer in the Designer Tool.

To understand Decode in an easy way, let’s consider it as similar to the CASE statement in SQL. It is basically the function that is used by an expression transformation in order to search a specific value in a record.

There can be unlimited searches within the Decode function where a port is specified for returning result values. This function is usually used in cases where it is required to replace nested IF statements or to replace lookup values by searching in small tables with constant values.

Decode is a function that is used within Expression Transformation. It is used just like CASE Statement in SQL to search a specific record.

Ans: With the help of Joiner Transformation, you can make use of Joins in INFORMATICA.

It is based on two sources namely:

  • Master Source
  • Detail Source

Following joins can be created using Joiner transformation as in SQL.

  • Normal Join
  • Full Outer Join
  • Master outer join(Right Outer Join)
  • Detail outer join(Left Outer Join)

Ans: Aggregator Transformation can be active or connected. It works as the GROUP BY clause in SQL. It is useful to perform aggregate calculations on groups in INFORMATICA PowerCenter. It performs an aggregate calculation on data using aggregate type function viz. SUM, AVG, MAX, and MIN.

Ans: Sequence Generator Transformation can be Passive or Connected. Its basic use is to generate integer value with NEXTVAL and CURRVAL.

Ans: Union Transformation is used to combine the data from different sources and frame it with the same port and data type. It is much like a Clause in SQL.

Ans: Source Qualifier Transformation is useful in Mapping, whenever we add relational flat files it is automatically created. It is an active and connected transformation that represents those rows which are read by integration service.

Ans: Worklet works as a Mapplet with the feature of Reusability, the only difference is that we can apply worklet to any number of workflows in INFORMATICA, unlike mapplet. Worklet saves the logic and tasks at a single place to reuse.

Worklet is much similar to the Mapplet and is defined as the group of tasks that can be either reusable or non-reusable at the workflow level. It can be added to as much number of workflows as required. With its reusability feature, much time is saved as reusable logic can be developed once and can be placed from where it can be reused.

In the INFORMATICA Power center environment, Mapplets are considered as the most advantageous feature. Mapplets are created in Mapplet designer and are a part of the Designer tool.

It basically contains a set of transformations that are designed to be reused in multiple mapping.

Mapplets are said to be reusable objects which simplify mapping by:

  • Including multiple transformations and source definitions.
  • Not required to connect to all input and output ports.
  • Accept data from sources and pass to multiple transformations

Well, overall when it is required to reuse the mapping logic then the logic should be placed in Mapplet.

Ans: SUBSTR is a function in INFORMATICA that extracts or remove a set of characters from a larger character set.

Syntax: SUBSTR( string, start [,length] )


string defines the character that we want to search.

start is an integer that is used to set the position where the counting should get started.

Length is an optional parameter that is used to count the length of a string to return from its starting position.

Example, SUBSTR(Contact, 5, 8), where we start at 5th character of our contact and returns to next 8 characters.

Ans: When a data is transferred from source code page to target code page then all the characteristics of source page must be present in the target page to prevent from data loss, this feature is called Code Page Compatibility.     

Code page compatibility comes into picture when the INFORMATICA server is running in Unicode data movement mode. In this case, the two code pages are said to be identical when their encoded characters are virtually identical and thus results in no loss of data.

For complete accuracy, it is said that the source code page is the subset of target code page.

Ans: Connected Lookup is part of the data flow which is connected to another transformation, it takes data input directly from another transformation which performs a lookup. It uses both static and dynamic Cache.

Unconnected Lookup does not take the data input from another transformation but it can be used as a function in any transformation using LKP(LookUp) Expression. It uses the only static cache.

Ans: Incremental Aggregation is generated as soon as a session created. Incremental Aggregation is used to calculate changes in the source data that do not change target data with significant changes.

Ans: A surrogate key is a sequentially generated integer value which is used as another substitute or replacement for the primary key which is required as a unique identification of each row in a table.

The primary key can be changed frequently as per the need which makes the update process more difficult for a future requirement, Surrogate Key is the only solution for this problem.

Ans: Session Task is a set of instructions that are to be applied while transferring data from source to target using Session Command. Session Command can be either pre-session command or post session command.

Command Task is a specific task that allows one or multiple shell commands of UNIX to run in Windows during workflow

Ans: Standalone command task can be used to run Shell Command anywhere and anytime in the workflow.

Ans: Workflow is the way of a manner in which the task should be implemented. It is a collection of instructions that inform the server about how to implement the task.

Given below are the three major components of Workflow Manager:

  • Task Designer
  • Task Developer
  • Workflow Designer

Ans: The event can be any action or function that occurs in the workflow.

There are two tasks related to it, which includes:

  • Event Wait Task: This task waits until an event occurs, once the event is triggered this task gets accomplished and assigns the next task.
  • Events Raise Task: Event Raise Task triggers the specific event in the workflow.

Ans: Predefined Events are system-Defined Events that wait until the arrival of a specific file at a specific Location. It is also called as File-Watcher Event.

User Defined Events are created by the user to raise anytime in the workflow once created.

Ans: Target Designer is used for defining the Target of data.

When there are multiple sources or a single source with multiple partitions linked to different targets through INFORMATICA server then the server uses Target Load Order to define the order in which the data is to be loaded at a target.

Ans: Staging Area is a database where temporary tables connected to the work area are stored or fact tables to provide inputs for data processing.

Ans: Differences are as follows:

  • STOP command runs on Session task, once it is raised. the integration service stops only reading the data in the data source but continues processing and writing it to the target.
  • ABORT command is used to completely stop the integration service from reading, processing and writing data to the target. It has its own timeout period of 60 seconds to finish the processing and writing of data through integration service if not then it simply kills the session.

Ans: INFORMATICA Lookup Can be either cached or uncached. Basically, it is divided into five parts.

They are:

  • Static Cache
  • Dynamic Cache
  • Recache
  • Persistent Cache
  • Shared Cache

Static Cache remains as it is without change while a session is running.

Dynamic Cache keeps updating frequently while a session is running.

Ans: There are two ways to update source definition in INFORMATICA.

They are:

  • You can edit the existing source definition.
  • You can import new source from the database.

Ans: There are 3 ways to implement security measures.

They are:

  • Folder Permission within owner, groups, and users.
  • Locking (Read, Write, Retrieve, Save and Execute).
  • Repository Privileges viz.
    • Browse Repository.
    • Use Workflow Manager(To create session and batches and set its properties).
    • Workflow Operator(To execute Session and batches).
    • Use Designer, Admin Repository(Allows any user to create and manage repository).
    • Admin User(Allows the user to create repository server and set its properties).
    • Super User(All the privileges are granted to the user).

Start the change

Register to become an Instructor

Please login to fill in this form.

  • Your name
  • Your email
  • Your phone number
  • Your message

I’m a Copywriter in a Digital Agency, I was searching for courses that’ll help me broaden my skill set. Before signing up for Rob’s.