Tuesday, 3 January 2017

Informatica Wiki | Informatica Careers | Informatica Scenarios

Informatica Wiki:

There are many websites in the world which will provide all the information about this etl tool from basics. In short we can say it as tutorials. Among them Indian Sites are giving best info for the beginners to learn all. The experienced candidates are maintaining these sites for various causes like some write articles to earn money, some write in brief to get the students to their institutions. What some clever people do is they will keep some videos which were explained clearly in the youtube with their institution names and mobile numbers. This is one type of business for them. But only few sites give real information about Informatica from the starting on wards they are Informatica Wiki which means a famous site called wikipedia site that gives entire description about the Informatica. It will provide all the information when it was started and who are the developers and the latest versions information like that.

Informatica Careers:

There are many opportunities in this company as it is a product based company. It has many branches all over the world. Many thousands of employees are working in the company with high salaries when compared to others. So many people will keep their target to get the job in Informatica. There are many ways to get the job in this company. They are as follows. If you have any people or employee who are in the high cadre in the company, you can ask them for the reference. They will upload your details in their office portal. So that you will be given good preference. but you be ready to answer all the questions that are asked in the interviews. If you don't know any one in the company then you can do one more thing. What you have to do is just go the official website of Informatica and go to 'Informatica Careers' and then register your email and fill all your details asked and upload your latest resume if necessary. Then you will get the emails whenever there are job openings in the company. Don't forget to give all the correct details there. Because once you fill your details in the informatica portal you can not change them once again. That is the reason why I am saying to be careful while filling all the columns.

Informatica Scenarios:

Informatica provides a separate forums page in the main site to raise your queries and doubts to post. You must be a registered user to post your doubts.  The people who know the answer and the people are interested will reply to your questions but you must be patience to get the answer. Because it will take time to get the replies, as many will be busy with their own works in office works etc., Also there are many other sites which will provide many discussions. Now a days after the technology has been developed there are many facebook groups where you can get the latest information about the jobs in various locations. The Informatica Scenarios are very important to practice as these are one of the most important part in the Informatica Interview Process. You have to practice various scenarios regularly to get perfect in this etl tool.

Tuesday, 8 November 2016

Types of Transformations in Informatica with examples

Definition of Transformation:

Transformation is a predefined object in Informatica Client. These are used to develop the business logics to transform the data from one format to another format. Every Transformation receives input columns, applies specific logic to transform the data and then sends the output. Transformations play a very important role while designing the mappings. Now here comes a very important answer for one interview question because it is one of the most Frequently asked informatica interview questions in many multi national companies like Accenture and Wipro. Read this upcoming point twice. In Informatica, these Transformations are categorized into 2 types as mentioned below:

  1. Active Transformation
  2. Passive Transformation


Active Transformation:

A Transformation which changes the number of rows while moving the data from the source to target is called as an 'Active Transformation'.
Examples: Filter Transformation is active transformation. The other T/Rs that come into this category are Router T/R, Aggregation T/R and many others.


Passive Transformation:


A Transformation which does not change the number of the rows while moving the data from source to target is known as 'Passive Transformation'.
Examples: Expression Transformation, Sequence Generator, Stored Procedure and others.

In the other way, the Transformations are also classified into 2 types.
  • Connected Transformations
  • Unconncected Transformations

Connected Transformation:

A Transformation which is connected in the mapping flow or which is a part of the mapping flow is called as 'Connected Transformation'. These T/Rs will receive multiple input ports and returns any number of output ports. For example: Router is Connected Transformation because it is always used as a part in the mapping flow.


Unconnected Transformation:

A Transformation which is not connected in the mapping flow is known as 'Unconnected Transformation'. It is a stand alone(separate) in the mapping. So that we can use any number of times in the mappings and whenever we want. These T/Rs will receive multiple input ports but can return only one output port.

The following 2 Transformations can in Informatica can be used as connected as well as unconnected.
  • Lookup Transformation and
  • Stored Procedure

These 2 can be used differently depending upon our usage. All the other T/Rs are connected only.
Each Transformation has its own specific use and used for different purpose depending up on our usage. Though there are many transformations in the Informatica Powercenter, we use only around half of the existing as it depends upon the nature of the project. In some projects, only direct mapping is enough to load the data into the relational target systems or flat files.

Uses of Transformations in Informatica:

There are many uses with the transformations that are used in this Informatica Tool that are used for the ETL purpose. Each transformation has its own use and applies specific logic which is a predefined. What it does is just take the input and send the output after applying those logics. Read the following to know the specific uses.
  1. Filter transformation is used to filter the records or the input that are coming from the different types of sources or from any other transformation. The sources may be a flat file or relational sources.
  2. Router Transformation is also used to filter the records but here you can apply as many filter conditions you want to load the data into many targets. When we apply each filter condition we can load that data into target systems. The main advantage with this transformation is we can also catch the rejected which is not possible in the filter transformation. This is the slight difference. This will help in various scenarios when we don't any type of logics to apply simply.
  3. Expression Transformation is for data scrubbing and data cleansing purposes. In this we can also create variable ports to store the value of the expressions or to store the values of the other ports temporarily.
  4. 'Sorter Transformation' is used to keep the records in any order. We can sort them in any order like ascending order or in descending order. We can also sort the records depending on the characters. When we want to sort the records in alphabetical order then Informatica Server sorts based on the value of binary values.
  5. 'Rank Transformation' is used to give the ranks for the records in top wise or bottom wise. Here the rank port plays a key role in giving the ranks. Rank Index port is created by default. In this transformation we will have input port, output port, rank port, variable port and group by ports. The group by port is used whenever we want to give the ranks in the group wise. Otherwise we no need to select the group by port. Don't forget to select the number of records in Top wise or bottoms wise. Otherwise it will display all the records that are sent in to it.

Sunday, 6 November 2016

Informatica Interview Questions and Answers

Informatica Interview Questions:

Now a days there are many jobs on Informatica PowerCenter and the salaries on Informatica tool are also very high. Before reading this post completely, Please check the below video. It is the best video I have seen till now. I have found this on youtube. It will be very useful for those who are trying for jobs on Informatica as a fresher and experienced. It will give you a clear picture on how to prepare and what to prepare.

Now, I have to say about my personal experience and few things about me. Currently, I am working in TCS company in Bengaluru. I have more than 3 years of Real Time Experience now. Before getting this job, I have attended more than 15 interviews in different companies. I faced different types of questions there and I mentioned all those questions with answers here.

If you are keeping your gap as fake experience, this post is especially for you. You need to prepare the following before attending to the interviews on Informatica:

  1. You must be perfect in all Informatica Topics
  2. You must learn Informatica Scenarios which are used in the real time
  3. You must be ready to answer about any Informatica Transformations and related questions
  4. You should strong knowledge on SQL
  5. You must know the basics of UNIX
  6. Knowledge on data ware house concepts

 Informatica Scenarios Based Interview Questions:

Informatica questions for experienced and freshers will be different. For the experienced candidates, the interviews will be little bit tough when compared to the freshers. Informatica Real Time Scenarios are the main concepts where the interviewers mainly concentrate and this will decide whether you are a real time employee or fake. The theory part questions can be answered very easily by every one if you know the Informatica Basics.

In their point of view, if you have worked in the real time, you will answer these scenarios perfectly. Because your daily routine will be working with these scenarios only.
To answer Scenario Based Interview Questions, SQL knowledge is compulsory. If you use little bit common sense and apply the SQL knowledge, answering these questions is not a matter to you.If you know PLSQL, it will be an added advantage to you. Though PLSQL is not mandatory. Without any doubt, You will be given more preference if you know at least basics of PLSQL. This knowledge is required only in few projects where the procedures are necessary.
Procedures are used in some projects like while adding the indexes in the target systems after running the workflows. You must answer the real time scenarios keeping the best logics in mind. Because the best logics in the transformations will make the workflow to run very fast.

Informatica Transformations:

There are many transformations available in Informatica like Filter Transformation, Router Transformation. We use particular transformations for a specific use. But the interviewer will ask only about the Informatica Transformations that you have used in your project. 
You have to answer that and why you have used that. And the main thing is they will ask more and more questions on the Slowly Changing Dimensions(SCDs). These are the key concepts. The technical team or the interviewer will spend atleast 20 to 30 minutes of time on these SCD's.
That is the reason why I am asking you to know about the transformations in informatica.
The questions on SCDs will be like this.

  • How many types of SCD are there in Informatica and at what situation you will use them
  • Which SCD you have used in your project.
  • List out the order of the transformations that you have used in your SCD during the project.
  • Why did you choose only that particular SCD.
Most probably, everyone will use SCD of type 2 in the major number of projects. In few projects, SCD type 1. Some times both will used in the same project. Only in the rare cases the last one will be used.
If you answer the above type of questions and know the basic commands of unix commands and where and how you apply those commands, you can crack the interview very easily. See the below questions. These are the questions which I faced during the interviews in Informatica.

Filter Transformation in Informatica - Explanation

Filter Transformation:

  • It is Active Transformation and Connected Transformation.
  • It is used to filter the input data or to remove the unwanted data based on the given condition.

  • Filter Transformation returns 2 values internally. i.e., True or False
  • It returns true for a record if it satisfies the condition. It returns false for a record which does not satisfies the given condition.
  • Filter Transformation receives record by record as input, applies the specific condition. It allows the record which satisfies the condition otherwise it rejects them.

Important notes on Filter Transformation:
  1. Condition in the filter can be defined with multiple ports.
  2. In Informatica Level, we call the columns as 'Ports'.
  3. To improve the performance while designing the mappings, use this transformation very close to the source because it will filter the unnecessary records in the starting itself.
The third point is very important for the people who attend the interviews on Informatica as it is asked in many interviews in various companies. 

Filter Transformation Real Time Scenarios with example:

Design a mapping to load only the employees whose salary is greater than 300.

1. Create a target with the name EMP_FILTER with the same structure as EMP table using target designer.
2. Create a mapping with the name m_filter
3. From the repository drag the source(emp) and drag the target(emp_filter)
4. From the transformations menu, select create and then select the transformation type as filter from the drop down list.
5. Enter a new name for this transformation as Fil_Sal. Create and Done.
6. Connect all the ports of SQ to filter transformation.
Select the properties tab. Set the following property as shown below:
In the transformation attribute, you will find the filter condition and write the condition as 'sal>3000'
7. Apply Ok.
8. Connect the ports of filter transformation to the target and save the mapping.
9. Create a session and workflow and execute it.

Difference between Filter and Router Transformation in Informatica

Filter Transformation vs Router Transformation:

In this Informatica Tutorial, I will clearly explain the differences in a simple way. We know that these 2 transformations are active and connected transformations. Both Router and Filter Transformations are used to filter the input data coming from the source or coming from any other transformations.In short we can say that, router transformation acts like group of filters as it allows many conditions. In Informatica, Filter Transformation is just like where condition in SQL. Though both are almost used for the same purposes. There are some slight differences between them while using them in Informatica Mapping.

Filter Transformation in Informatica:

1. We can apply only one condition in filter to filter the data.
2. Filter Transformation is used only when we want to load the data into single target

3. It can not capture the rejected records.

Router Transformation in Informatica:

1. We can specify any number of conditions in Router Transformation.

2. This is used when we want to load the data into multiple targets.

3. It can capture the rejected records but filter can not.

Here Rejected Record means the record for which none of the given conditions are satisfied.
All the rejected records will be in the default group of the Router Transformation. We can use these rejected records to other transformations or directly to the target systems.

Saturday, 5 November 2016

Components of Informatica PowerCenter - Architecture

Informatica PowerCenter Architecture:

When we install Informatica PowerCenter, the following 2 types of components gets installed.
  • Client Components
  • Server Components

Client Components are:
  • Informatica PowerCenter Designer
  • Informatica PowerCenter workflow manager
  • Informatica PowerCenter monitor and
  • Informatica PowerCenter Repository manager

Server Components are:
  • Repository Services
  • Integration Services

Informatica PowerCenter Designer:

It is a GUI based client component which is used to import source definition, target definition, design mappings, to design mapplets and to create reusable transformations etc.,

It contains 5 tools.

1. Source Analyzer:
It is used to import source meta data.

2. Target Designer:
It is used to import target meta data and to design the target tables at Informatica level.

3. Mapplet Designer:
It is used to design mapplets(Reusable Objects created with set of transformations)

4. Transformation Developer:
It is used to create reusable transformations created with single transformation.

5.Mapping Designer:
It is used to create or design mappings in Informatica.

Designer Client connects to the source and target databases using ODBC connection to import the meta data.


Workflow Manager:

It is a GUI based client component which is used to create relational connections, to create different types of tasks like session, email, command, event wait, event raise.
It is also used to create worklets(reusable objects with set of tasks) and to design workflow.
Workflow Manager contains 3 tools as follows:
1. Task Developer:
It is mainly used to create reusable tasks.
The following 3 tasks can be developed using task developer.

  • Session Task
  • Email Task
  • command Task
2. Worklet Designer:
It is used to create worklets.

3. Workflow Designer:
It is used create workflows.


Relational Connections:

It is an connection object which contains Database information. Informatica Server connects to source and target databases with the help of Relational Connection at the time of execution.

Session Task:
It is an object which provides a set of instructions  or required information like mapping information, connection info, load type info, parameter file path etc.,
A session task is required to execute a mapping. One session can take only 1 mapping for execution.

It is a top level object in Informatica PowerCenter which is used to execute different types of tasks. One workflow can have any number of tasks. By default, every workflow is created with start button.
Use Link task to connect multiple tasks while creating the work flow.

Workflow Monitor:

It is a GUI based client component which is used to monitor the workflows at the time of execution to know the status whether it is succeeded or failed or running.
It is also used to display the run properties like source success rows, source fail rows, target success rows and failed rows etc.,
Workflow monitor allows the user to open session logs and workflow logs to give the events occurred at the time of execution.
Workflow monitor can send a request to informatica server for execution.

Repository Manager:

Repository Manager is used to perform admin roles like creating folders in the repository, deleting the folders, editing the folders, exporting the objects, importing the objects etc.,

Server Components:
  1. Repository Service
  2. Integration Service
Repository Service:
There is a two way communication between all the client components and repository database through repository services.

Integration Service:
It is the main services which executes the ETL objects developed by Informatica clients. Integration Service executes the workflow as soon as it receives the request from client. Integration Service performs extraction, transformation and loading according to the plan available in the mapping.

Friday, 4 November 2016

What is a Mapping in Informatica ?

Informatica PowerCenter is an integrated tool set used to design, to run, to monitor, to administrate the data acquisition apps or ETL applications known as 'Mappings'.

Definition of Mapping in Informatica:

It is a graphical representation or pictorial representation of data flow from the source to target.
It is a path of data flow from the source to target.

Pre-requisits to design a mapping:

The following are needed to design a mapping in Informatica PowerCenter.
  • Source meta or source definition or source structure
  • target meta data and
  • business rules

Pass through mapping:

A mapping which created without any business rules is called 'Pass through mapping'.
It is also called as 'Simple Pass Mapping' or 'Direct Mapping'.
Minimum objects that are required to develop design a mapping in  is source definition and target defintions.