Informatica Interview Question Part -7

Q. List of Active and Passive Transformations in Informatica?

Active Transformation - An active transformation changes the number of rows that pass through the mapping.

• Source Qualifier Transformation
• Sorter Transformations
• Aggregator Transformations
• Filter Transformation
• Union Transformation
• Joiner Transformation
• Normalizer Transformation
• Rank Transformation
• Router Transformation
• Update Strategy Transformation
• Advanced External Procedure Transformation

Passive Transformation - Passive transformations do not change the number of rows that pass through the mapping.

• Expression Transformation
• Sequence Generator Transformation
• Lookup Transformation
• Stored Procedure Transformation
• XML Source Qualifier Transformation
• External Procedure Transformation

Q. Eliminating of duplicate records without using dynamic lookups?

Hi U can eliminate duplicate records by an simple one line SQL Query.

Select id, count (*) from seq1 group by id having count (*)>1;

Below are the ways to eliminate the duplicate records:

1. By enabling the option in Source Qualifier transformation as select distinct. 

2. By enabling the option in sorter transformation as select distinct. 

3.  By enabling all the values as group by in Aggregator transformation.

Q. Can anyone give idea on how do we perform test load in informatica? What do we test as part of test load in informatica?

With a test load, the Informatica Server reads and transforms data without writing to targets. The Informatica Server does everything, as if running the full session. The Informatica Server writes data to relational targets, but rolls back the data when the session completes. So, you can enable collect performance details property and analyze the how efficient your mapping is. If the session is running for a long time, you may like to find out the bottlenecks that are existing. It may be bottleneck of type target, source, mapping etc.

The basic idea behind test load is to see the behavior of Informatica Server with your session.

Q. What is ODS (Operational Data Store)?

A collection of operation or bases data that is extracted from operation databases and standardized, cleansed, consolidated, transformed, and loaded into enterprise data architecture.

 An ODS is used to support data mining of operational data, or as the store for base data that is summarized for a data warehouse.

The ODS may also be used to audit the data warehouse to assure summarized and derived data is calculated properly. The ODS may further become the enterprise shared operational database, allowing operational systems that are being reengineered to use the ODS as there operation databases.

Q. How many tasks are there in informatica?

·       

            Session Task

·         Email Task

·         Command Task

·         Assignment Task

·         Control Task

·         Decision Task

·         Event-Raise

·         Event- Wait

·         Timer Task

·         Link Task

Q. What are business components in Informatica?

·         Domains

·         Nodes

·         Services

Q. What is versioning?

It’s used to keep history of changes done on the mappings and workflows

1.   Check in: You check in when you are done with your changes so that everyone can see those changes.

2.   Check out: You check out from the main stream when you want to make any change to the mapping/workflow.

3.   Version history: It will show you all the changes made and who made it.

Q. Diff between $$$sessstarttime and sessstarttime?

$$$SessStartTime - Returns session start time as a string value (String datatype)
SESSSTARTTIME - Returns the date along with date timestamp (Date datatype)

Q. Difference between $,$$,$$$ in Informatica?

1. $ Refers
These are the system variables/Session Parameters like $Bad file,$input
file, $output file, $DB connection,$source,$target etc..
2. $$ Refers
User defined variables/Mapping Parameters like $$State,$$Time, $$Entity, $$Business_Date, $$SRC,etc. 

3. $$$ Refers
System Parameters like $$$SessStartTime

$$$SessStartTime returns the session start time as a string value. The format of the string depends on the database you are using.

$$$SessStartTime returns the session start time as a string value --> The format of the string depends on the database you are using.

Q. Finding Duplicate Rows based on Multiple Columns?

SELECT firstname, COUNT(firstname), surname, COUNT(surname), email, 

COUNT(email) FROM employee

GROUP BY firstname, surname, email

HAVING (COUNT(firstname) > 1) AND (COUNT(surname) > 1) AND (COUNT(email) > 1);

Q. Finding Nth Highest Salary in Oracle?

Pick out the Nth highest salary, say the 4th highest salary.

Select * from

(select ename,sal,dense_rank() over (order by sal desc) emp_rank from emp)

where emp_rank=4;

Q. Find out the third highest salary?

SELECT MIN(sal) FROM emp WHERE

sal IN (SELECT  distinct TOP 3  sal FROM emp ORDER BY sal DESC);

Q. How do you handle error logic in Informatica? What are the transformations that you used while handling errors? How did you reload those error records in target?

Row indicator: It generally happens when working with update strategy transformation. The writer/target rejects the rows going to the target

Column indicator:

D -Valid

o - Overflow

n - Null

t - Truncate

When the data is with nulls, or overflow it will be rejected to write the data to the target

The reject data is stored on reject files. You can check the data and reload the data in to the target using reject reload utility.

Q. Difference between STOP and ABORT?

Stop - If the Integration Service is executing a Session task when you issue the stop command, the Integration Service stops reading data. It continues processing and writing data and committing data to targets. If the Integration Service cannot finish processing and committing data, you can issue the abort command.

Abort - The Integration Service handles the abort command for the Session task like the stop command, except it has a timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.

Q. What is inline view?

An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub query

Ex : Select Tab1.col1,Tab1.col.2,Inview.col1,Inview.Col2
From Tab1, (Select statement) Inview
Where Tab1.col1=Inview.col1

SELECT DNAME, ENAME, SAL FROM EMP ,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO

In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.

Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary,

Disadvantage of using this is

1. Separate view need to be created which is an overhead
2. Extra time taken in parsing of view

This problem is solved by inline view by using select statement in sub query and using that as table.

Advantage of using inline views:

1. Better query performance
2. Better visibility of code

Practical use of Inline views:

1. Joining Grouped data with non grouped data
2. Getting data to use in another query