New Informatica Interview Questions

1.       Name some Informatica Application Services

·         Repository Service

·         Integration Service

·         Data Integration Service

·         Metadata repository service.

·         Analyst Service.

2.       What is a Service Manager?

·         The service manager is the main service of Informatica which is responsible for running of other application services. When we start the Informatica services on the node, we are actually starting the Service manager. Default port 6005.

3.       From where can we call SQL functions in Informatica.

·         Pre/Post session level SQL

·         Source Qualifier override.

4.       What is the minimum number of ports required to Install Informatica.

·         6000 to 6113.

5.       Name the environment variables required to be set at Informatica level.

·         TNS_ADMIN

·         ORACLE_HOME

·         ORACLE_BASE

·         LD_LIBRARY_PATH

·         NLS_LANG

6.       Can you implement High Availability Architecture without a grid?

·         Yes, by adding nodes as primary and backup nodes under the same domain.License should have HA property.

7.       How do you configure a WSDL connection at session level?

·         As an application connectionà WSDL consumer-àAuthentication type Basic.

8.       Should the client and server for Informatica , be of the same version?

·         Yes

9.       What is Target Load Plan?

·         Please study from the guide and understand that the load can be prioritized even if the pipes are originating from the same source.

10.   How to switch off/start Informatica services?

·         Informatica/9.5.1/tomcat/bin infaservice.sh startup/shutdown.

11.   Give an example of usage of shell scripts in pre-post session.

·         Used to FTP the flat file on a particular folder in the unix box from where informatica could read it.

·         Do a validation check, pre-post count check of the feed being processed.

Update Strategy with WSDL target

While importing the Web Service (consumer) as a target we need to specify that the WSDL will be for :

1. insert
2. delete- select delete multiple not used delete record
3. Update

and then use Update strategy with different targets for different kind of DML operations.

Amazon EC2 (Elastic Compute Cloud)

We create virtual machines using the AWS EC2 component. We can create the instances in the following manner:

1.       Create a new instance (remote machine)

2.       Use the image of an existing image and customize it as per the need.

Creating a new EC2 instance

·         Click on instances.

·         Navigate to the page below

We can see two already created instances. For creating a new instance click on Launch instance.

Follow as per the snapshots below:

Amazon Simple Storage Service (S3)

Core Idea: Using the above link we will create a folder and upload the following software in this folder

·         Informatica Server – for windows 64 bit or 32 bit

·         Informatica Client  – for windows 64 bit or 32 bit

We shall be creating and shall be installing the Informatica server and clients on different virtual machines using the AWS. Once a VM is created in would use Internet Download manager to download the Informatica server or client from the folder created in S3, which would act as a central/global folder for such installations.

As a best practice it is advised to upload all the important s/w in the folder created using S3

AWS User guide.

Algorithm

Step 0 – Start

Step 1 – Access the link of the AWS

Step 2 – Creating a folder with re-usable components-S3

Step 3 - Creating an independent Virtual Machine

Step 3.1 - Creating a Virtual Machine from an image (AMI)

Step 4 – Creating a Virtual Private Cloud (VPC).                 

Step 4.1   – Creating instances in a VPC

Step 5 – Creating/Associating/Dis-associating Elastic IPs

Step 6 - Precautions before shutting down work at AWS.

Step 7 – End

Below is the snapshot of all the services under the AWS environment that we see on first time login.

The system navigates to the above screen. i.e. The AWS Home Page. The three links to be explored here are (marked with green arrows):

11   S3 – (Simple Storage Service)

12   EC2 – (Elastic Compute Cloud)

13   VPC – (Virtual Private Cloud)

How to make an Oracle Relation Connection in Informatica

This was one of the most challenging parts.

Problem – You need to make an Oracle connection at Informatica level so that you may use the same for read/write.

Solution

1.       For Linux and Windows , you need to Install Oracle client (admin version) freely downloadable from the OTN.

a.       Be careful about the bit compatibility, the oracle client and Informatica server bit should be same i.e. 64 bit or 32 bit

2.       Once you have installed the oracle client in the Windows/Linux box along with the Informatica server, you need to make the following environment variable entries:

1. PATH=$PATH/home/oracle/app/oracle/product/11.2.0/client_1/bin
2. ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
3. ORACLE_BASE=/home/oracle/app/oracle
4. LD_LIBRARY_PATH=/home/oracle/app/oracle/product/11.2.0/client_1/lib
5. TNS_ADMIN=$ORACLE_HOME/network/admin

Preferably you may make these entries at Informatica Administrator>Repository Services>Process tab>Env variables level.

3.       Now to connect to oracle database from Informatica, we can either use ODBC connection or Oracle connection.

a.       Connecting using Oracle drivers

                                                               i.      Make the TNS entry in the tnsnames.ora file,assuming the connection string name=Conn_string

                                                             ii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make Oracle connection for TNS entry

2.       Type in the credentials and make the connection string as  Conn_string same as TNS entry.

b.      Connecting using ODBC drivers (Approach not applicable for Linux only for Windows)

                                                               i.      You need to go to> Start>Administrative Tools>ODBC Data Sources>System DSN

                                                             ii.      Make a new System DSN give the DSN Name as Conn1

                                                            iii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make ODBC Connection

2.       Name the Connection String as Conn1

                

PMWS_33016--Informatica Authorization error

I have often faced difficulty in configuring a WSDL connection in the workflow manager.Hence listing down the steps:

1. Import the WSDL source/target as Import>WSDL Consumer.-Designer
2. navigate through the options of the import.-Designer
3. Make an application connection in workflow manager as a Web Service consumer connection.
4.Make sure of setting the connection authentication mode as Basic and not PEM. 
5.Enter username and password, Domain need not be entered.

Talend vs Pentaho (ETL) review

Informatica happens to be the current leader in the ETL stack, but as a result of rapid business change, the demaqnd of light weight tools for the Small and medium size industry has increased exponentially.This sector is very particular on cost and would need a tool to address its data migration activities across different environments.High end tools like Informatica have thus released their thinner version i.e. Informatica Powercenter express to address such clients, which generally have data migration requirements. It is one time, across heterogeneous sources and very tight on cost.
We shall be reviewing Talend and Pentaho ETL tools which belong to the same space.

Talend

I installed the latest version for talend 5.5.1 and tried exploring the same. My reviews are as follows:

Pros
1. 30 day free trial version available.
2. Server - 2 GB
3. Free and comprehensive documentation.
4. Exceptional tool for people coming from Java coding background. The ETL concept is partially GUI based (unlike Informatica) and uses a lot of Java code to implement data transformations. Good tool for coders who want to learn ETL.
5. No database required for repository metadata.
6. Integration with a wide variety of storage systems including-Amazon Web Services' RDS, Big Data,WSDL etc.

Cons
1. Bit complicated to adopt for people who have worked on complete GUI based ETL tools like Informatica
2. The help center is available for 4 countries only, so your server should reside in one of these countries for you to access the same. India is not one of those countries :D

Pentaho

After my tryst with Talend, I decided to explore the data Integration suite of Pentaho.Reviews are as follows:

Pros
1. Very easy to install and use
2. Comprehensive documentation (better than talend)
3. Less coding more of GUI.
4. No database required for repository metadata.
5. Custom output that can be used by the Reporting tool of Pentaho.

Cons
1. No direct integration with WSDL.

 

The Snow cap on the peak of frustration

The situation is similar to the second session of the third day of the test match.A batsmen well settled has to face the vicious spinner , trying to find a way through him. The fielders surround the batsman in the scorching sun, trying to mentally disintegrate him through sledging.

An IT job has a very similar situation when you are about to complete your second year. It is the time when you are on the verge of expanding your comfort zone within the organisation. You have successfully survived the viciousness and discouragement of your insecure manager and colleagues and ended up with decent appraisals. Impossible tasks are thrown upon you now and you are left with a feeling of whether to continue with the company.

1. A good decision would be have an offer in hand.
2. If you dare to  handle different kind of tasks thrown at you and are able to survive the same in your 3rd year, this could be your road to fame and growth within the organisation,which you had been eagerly waiting to walk all these years.
3.I had missed a similar opportunity in my first company at the end of  years when I had put down papers there.
4. Always remember - Never give in. (Cornwallis house motto), hold the fort and thou shall walk the path to glory.

How to work and not work at the same time.

Working in an internal project, where the project manager treats you like his pet slave?You need to stay in the job for another couple of months so that you could crack a company and put down papers in the present one?This post talks about the do's and dont's if you are in a similar situation.

1. Before you end up writing mails in which you would clearly deny the job assigned to you.Try out the following:
2. Never ever show any attitude at work or enter into altercations. It might worsen your situation.
3. Do your bit. People should not feel that you haven't even tried.
4. Try delivering a small working prototype so that you have something to show.
5.  Try not to react back to subtle or direct humiliations related to work by senior managers, understand that people around you should not matter,they are equally traumatized by the same people.
6.  Try to be chilled up.At times we think too much and things automatically come on track.
7. Remember the famous quote of Rocky Balboa - " No matter how tough you are,life will make you bend to your knees,and keep you permanently there. It is all about how much you can take and keep moving forward.Cowards end up blaming people around them".
 

Tips for a 0-2 year data professional

So you are another fresher who dived into the IT industry and is determined to learn the technology after getting into a development project.Every 2nd fresher's feels himself cheated after landing in a Production-Support profile.I was no exception.I was really dissapointed to have been trained in .NET/SQL Server and then landing in a technology called Informatica that too a production support profile. Worse, we were not working on a datawarehouse (Which I realized when I had started appearing for interviews)

Do not be afraid as there is always something to cheer about. I started off with simple monitoring, understanding the jobs at workflow/monitor level. Then trying to decipher which code at session level maps to what. We had extensive usage of shell scripts too in our projects. I took time and analyzed them.

The book: UNIX Shell programming by Yashwant Kanetkar was very handy in helping me understand and decipher the power of shell scripting in Informatica.It will help you overcome your fear of the black linux screen.

I believe developing is not the most important thing.For a 2year professional the follwing profile would be great

1. Exposure to all Informatica tools
2. Unix Shell scripting
3. SQL/PL-SQL
4. Try getting some hands on the reporting tool in your project along with the ETL tool.

Test cricket - Playing a long innings at work.

I'd started this blog to share the experiences I had at job.This includes both the technical and non-technical side of things. So I started to come up with this new tab,under which we shall discuss office psychology.I shall be sharing the difficulties that I had faced at office and how I had coped up with the same.

1. Never be an emotional fool at work, there are no friends or girl friends at work.
2. Make sure of having Simple,measurable,achievable,relevant and time bound (SMART) goals at work.
3. Working in an Indian IT company or an MNC having indians is like playing a five day test match.Each day of the test match can be mapped to an year at the company. Try avoiding people who make your blood boil during the 1st year, which happens to be the toughest. Once you have settled down and you know the pitch i.e. the organisational culture start by taking singles and graduate to boundaries. Remember Rahul Dravid who played the balls according to it's merit.
4. Do not be afraid of saying no to work in an environment you are not comfortable working in.
5. Being Nice to people is not licking and is an important art.
6. You are bound to be pulled down by known and unknown ways by all kind of people.Just assume them to be Jason Gillispes,Brett Lees and Glen mcgraths of your arena who are trying to sledge you for your wicket, Do not quit hang on duck the bouncers,ignore the sledges, close your eyes and ask for strength.

Restoring Informatica Code from existing repository database.

Assumptions

1.       An existing informatica setup with server existing on Linux Box A (internal i.p: a.b.c.d) and repository metadata existing on Relational Database Service (RDS- A)

2.       Following componenets exist on this environment

a.       Domain-Domain_abc

b.      Node-node_abc

c.       Repository Service-RS-A.

d.      Integration Service-IS-A.

3.       Linux Box A gets corrupted. The OS team provides a fresh Linux Box B with the same internal IP (a.b.c.d)

4.       This document will explain how to recover the Informatica code that was made using repository service-RS-A

Steps to be followed

1.       On Linux Box –B – Re-install informatica Server

·         Keep the repository database same

·         Make new Domain- Domain_def

·         Make new node- node_def

2.       Access the administrator console and login using admin credentials.

3.       Configure the Repository RS_def

   We get the following error

 We now try creating the Repository Service with the same name i.e. RS_abc

 Since the content already exists in the database we select the Content exist option as shown below

Repository Service RS_abc is created and is in disabled mode. 

Enable the repository using the enable button. RS_abc is up and running now.

You may now configure the domain and the repository. When you log into the repository you can see the old code.

1

Informatica Opportunities in Delhi NCR

Healthcare
1. Stryker-Gurgaon -Dev and Prod support projects
2. United Health Group-Gurgaon/Noida-Dev and Prod support projects

Capital Markets
1. Headstrong-Morgan Stanley clients-Noida-Prod support projects
2. American Express-Gurgaon-Dev and Prod support projects
3. Black Rock-Gurgaon-Prod support projects
4. Fidelity

Miscellaneous
1. CentureyLink-Noida (Telecomm)
2. Deloitte
3. Expedia
4. ZS Associates

IT Services
1. HCL
2.Accenture
3.TCS
4.Sapient
5.IBM

User-Group Creation and Management in Informatica

Please refer the below matrix while making Informatica users/groups. The Matrix also has folder level permission details.

Development –

Repository Service			Admin	Developer	Monitor	Deployer	Operator	User
	Folder
		Create
		Copy
		Manage Versions	X			X
	Runtime Objects
		Create, Edit, and Delete	X	X		X
		Manage Versions	X	X		X
		Monitor	X	X		X	X	X
		Execute	X	X		X	X	X
		Manage Execution	X	X		X	X	X
	Global Objects
		Create Connections	X			X
		Manage Deployment Groups	X			X
		Execute Deployment Groups	X			X
		Create Labels	X			X
		Create Queries	X			X
	Tools
		Access Designer	X	X	X	X	X
		Access Repository Manager	X	X	X	X	X
		Access Workflow Manager	X	X	X	X	X
		Access Workflow Monitor	X	X	X	X	X
	Design Objects
		Create, Edit, and Delete	X	X
		Manage Versions	X	X
	Sources and Targets
		Create, Edit, and Delete	X	X
		Manage Versions	X	X
	Folder Access Development		RWX	RWX	R--	RWX	R-X
	Folder Access Validation		RWX	---	R--	RWX	R-X
	Folder Access Production		RWX	---	R--	RWX	R-X

Validation and Production –

Repository Service			Admin	Developer	Monitor	Deployer	Operator	User
	Folder
		Create
		Copy
		Manage Versions	X
	Runtime Objects
		Create, Edit, and Delete
		Manage Versions
		Monitor	X	X	X	X	X	X
		Execute	X	X	X	X	X	X
		Manage Execution	X	X	X	X	X	X
	Global Objects
		Create Connections	X			X
		Manage Deployment Groups	X			X
		Execute Deployment Groups	X			X
		Create Labels	X			X
		Create Queries	X			X
	Tools
		Access Designer	X	X	X	X	X
		Access Repository Manager	X	X	X	X	X
		Access Workflow Manager	X	X	X	X	X
		Access Workflow Monitor	X	X	X	X	X
	Design Objects
		Create, Edit, and Delete
		Manage Versions
	Sources and Targets
		Create, Edit, and Delete
		Manage Versions