Informatica Interview Questions on Transformations

The transformations which used mostly are listed in the below table

Aggregator	Active/Connected
Expression	Passive/Connected
Filter	Active/Connected
Joiner	Active/Connected
Lookup	Passive/Connected or Unconnected
Normalizer	Active/Connected
Rank	Active/Connected
Router	Active/Connected
Sequence Generator	Passive/Connected
Sorter	Active/Connected
Source Qualifier	Active/Connected
SQL	Active or Passive/Connected
Stored Procedure	Passive/Connected or Unconnected
Transaction Control	Active/Connected
Union	Active/Connected
Update Strategy	Active/Connected

1. What is a transformation?
A transformation is a repository object that generates, modifies, or passes data.

2. What is an active transformation?

An active transformation is the one which changes the number of rows that pass through it.
Example: Filter transformation

3. What is a passive transformation?

A passive transformation is the one which does not change the number of rows that pass through it.
Example: Expression transformation

4. What is a connected transformation?

A connected transformation is connected to the data flow or connected to the other transformations in the mapping pipeline.
Example: sorter transformation

5. What is an unconnected transformation?

An unconnected transformation is not connected to other transformations in the mapping. An unconnected transformation is called within another transformation and returns a value to that transformation.
Example: Unconnected lookup transformation, unconnected stored procedure transformation

6. What are multi-group transformations?

Transformations having multiple input and output groups are called multi-group transformations.
Examples: Custom, HTTP, Joiner, Router, Union, Unstructured Data, XML source qualifier, XML Target definition, XML parser, XML generator

7. List out all the transformations which use cache?

Aggregator, Joiner, Lookup, Rank, Sorter

8. What is blocking transformation?

Transformation which blocks the input rows are called blocking transformation.
Example: Custom transformation, unsorted joiner

9. What is a reusable transformation?

A reusable transformation is the one which can be used in multiple mappings. Reusable transformation is created in transformation developer.

10. How do you promote a non-reusable transformation to reusable transformation?

Edit the transformation and check the Make Reusable option

11. How to create a non-reusable instance of reusable transformations?

In the navigator, select an existing transformation and drag the transformation into the mapping workspace. Hold down the Ctrl key before you release the transformation.

12. Which transformation can be created only as reusable transformation but not as non-reusable transformation?

External procedure transformation.

13. What is the difference between Reusable transformation and Mapplet?

Ans. Any Informatica Transformation created in the in the Transformation Developer or a non-reusable promoted to reusable transformation from the mapping designer which can be used in multiple mappings is known as Reusable Transformation. When we add a reusable transformation to a mapping, we actually add an instance of the transformation. Since the instance of a reusable transformation is a pointer to that transformation, when we change the transformation in the Transformation Developer, its instances reflect these changes.

A Mapplet is a reusable object created in the Mapplet Designer which contains a set of transformations and lets us reuse the transformation logic in multiple mappings. A Mapplet can contain as many transformations as we need. Like a reusable transformation when we use a mapplet in a mapping, we use an instance of the mapplet and any change made to the mapplet is inherited by all instances of the mapplet.

14. What are the transformations that are not supported in Mapplet?

Ans. Normalizer, Cobol sources, XML sources, XML Source Qualifier transformations, Target definitions, Pre- and post- session Stored Procedures, Other Mapplets.

16. What is the difference between STOP and ABORT?

Ans. When we issue the STOP command on the executing session task, the Integration Service stops reading data from source. It continues processing, writing and committing the data to targets. If the Integration Service cannot finish processing and committing data, we can issue the abort command.

In contrast ABORT command 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.

17. Can we copy a session to new folder or new repository?

Ans. Yes we can copy session to new folder or repository provided the corresponding Mapping is already in there.

18. What type of join does Lookup support?

Ans. Lookup is just similar like SQL LEFT OUTER JOIN.

19.How can we update a record in target table without using Update strategy?

A target table can be updated without using 'Update Strategy'. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as "Update as Update" and check the "Update" check-box.

Let's assume we have a target table "Customer" with fields as "Customer ID", "Customer Name" and "Customer Address".Suppose we want to update "Customer Address" without an Update Strategy. Then we have to define "Customer ID" as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

     

21 Which transformation uses cache?

1.   Lookup transformation

2.   Aggregator transformation

3.   Rank transformation

4.   Sorter transformation

5.   Joiner transformation

21.       Can every transformation reusable? How?

Except source qualifier transformation, all transformations support reusable property. Reusable transformation developed in two ways.

1. In mapping which transformation do you want to reuse, select the transformation and double click on it, there you got option like make it as reusable transformation option. There you need to check the option for converting non reusable to reusable transformation. but except for source qualifier trans.

2. By using transformation developer

Rows to Columns : Part II

Source: Std Table (Oracle)

 Target: Std_Stg (Oracle)

Final Mapping:

SQ_STD: Add four output ports for each subject

Finally, connect output ports from source qualifier to target output ports

Video: Rows to Columns

Rows to column conversion : Part I

Source Name: Rows (Flat file)

Target Name: Rows_Tgt (Flat file)

Now connect O_Address port to target address port.

SCD1

SCD2 using Effective Date Approach

Implementation of SCD3

Moving file from FTP server to informatica directory and processing

Select Half records from a Flat file

Separate unique and duplicate records in informatica

Load Top five salary from Source to target without using Rank Transformation

Load every fourth record to target

Rows to Columns

Columns to Rows using Normalizer Tranformation

Columns to Rows using Java Transformation

HOW TO USE EXPRESSION TRANSFORMATION IN INFORMATICA

Senario for inserting records n times using java transformation

SQL Complex Queries

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
   select * from emp where rownum <= &n;
6. Select LAST n records from a table
   select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.
   select * from dept where deptno not in (select deptno from emp);
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?
   select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?
   select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.
    select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?
    delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.
    select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
    
15. Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
    
16. Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
    
17. Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
    
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
    
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
    
20. Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
    
21. Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
    
22. In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
    
23. Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
    
24. Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
    
25. Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
    
26. How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
    
27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
    
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
    
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
    
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
    
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
    
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2

 

Sequence & Index

Sequence :

• To generate sequences numbers to a column sequence is used.
• Can be used for any column of table.
• Column data type should be number.

 Example ;

SQL> Create or replace sequence no_seq start with 1 increment by 1 ;

To use this sequence in insert statement :
Insert into emp values (no_seq.next,'john',100);

Index :

• To speed up sql statement execution on a table . 
• it points directly to the location of the rows containing the values.

Types :

• Unique
• Non-Unique
• B-Tree
• Composite
• Function based 
• Cluster

Unique :

• It guarantee that no two rows of a table have duplicate values in the columns that define the index.
• Automatically created when primary key or unique key constraint is created.

Example :
SQL> create unique index stude_idx on student(sno);

Non-Unique :

• it doesn't  impose the above restrictions on the column values.

Example :
SQL> create index stude_idx on student(sno);

B-Tree :

• Default type of index in oracle.
• Created when column having more distinct or unique values . eg. student no , employee no etc.

SQL> create index std_idx on student(sno);

Bitmap Index : 

• Created when the columns have low cardinality values or distinct values.
• gender ,country etc.

SQL> create bitmap index stude_idx on student(gender);

Composite Index :

• also called concatenated index.
• Created on multiple columns

Example :
create bitmap index student_idx on student(sno,sname);

Function based Index :

• When we use any functions within the where clause we define function based index.

Views

View :  To restrict the columns access within a schema  we use view.

Types of view :

1. Simple view
2. Complex view
3. Materialized view

Simple view :

• View created based on Single table  is called simple view.
• We can perform DML operations on view.
• Changes made to view data will be reflected on original table.
• view doesn't store data.

Example :

SQL>Create view employee_v as select name,no,dno from employee;

Note :

• create view in one schema .
• grant privileges on that view to another schema user.

SQL>Grant select on employee_v to <schema user name>

Complex view : 

• When multiple tables participates in creation of view it is called complex view .
• Joins are used.
• DML operations can't be performed.

Example:
SQL> create view employee_v as (select a.name,a.no,a.dno,b.dname from employee a,department b where a.dno=b.dno);
Note : view is an object which has to be access from one schema to another . So again grant privileges are required.

Materialized view :

• Is a database object
• Advantages over normal and complex views.
• Always used in real time for reporting.

Example :

SQL>materialized view employee_mv complete refresh based on primary key start with sysdate next sysdate +1 as select name,no from emp;
Difference between view and materialized view:
View :

• Has logical existence.
• Changes reflected to original table.
• Cant perform DML operations on complex view
• As view always called then it calls the sub query which takes response time.

Materialized view.

• Has physical existence.
• Changes not reflected to original table.
• Can perform DML operations on it.
• Can perform auto refreshing on materialized view. on  schedule basis.
• performance is better.

 

SUB QUERY & Inline View

Sub query: 
A Query within the where clause or A select query within another select query is called sub query.

Example : Select * from employee where dno=(select dno from department);
Types :

1. Inner or Normal sub query.
2. Correlated  sub query.

Inner Query : 

•  If sub query executed only once and returns single values. It is called Inner query.
• Uses operator =,<,>,<> etc.

Example : select * from employee where dno=(select max(dno) from department);

Correlated Query : 

• If sub query returns multiple values then it is correlated sub query.
• Uses opeartors In,Not In, All, Any etc.

Example :  Department wise max salary with employee details.
 Select e.name,e.no,e.salary from employee e where salary=(select max(salary) from employee b where e.dno=b.dno group by b.dno):

Inline View :

Select query within the from clause instead of table or object name.
Example : Select * from (Select name,no,salary from employee);