Informatica interview questions

What is meant by persistent cache?

Can we use Agg and Exp transformations parallelly in the mapping coming from SQ? If so Why?

How many return ports max I can use in unconnected lookup transformation?

If I want to use more than 1 return port in unconnected lookup what is the best way to follow?

Data cache and index cache using in joiner and lookup transformations?

If I change the data type in SQ of any mapping what kind of error I may get?

Have you used parameter file concept? If so where the parameter file will be saved and how it will be accessed?

What is the syntax of pmcmd?

Normalize transformation behavior?

Can we use alias names in where condition in SQL query? If I use what will happen?

What is the difference between parameters and variables?

What is target load plan and target load order in informatica?

Without any condition if I used Agg transformation what is the output from the transformation and how many rows it will through?

Using Decode and Case in SQL

The Decode Function is analogous to the "IF THEN ELSE" conditional Statement.

Syntax

Decode(Expression, Search, Result[,Search,result]...[,default])

SQL> select ename,

  2  decode(deptno, 10, 'operations',

  3  'others') result

4  from emp;

ENAME      RESULT

---------- ----------

SMITH      others

ALLEN      others

WARD       others

Evaluates a list of conditions and returns one of multiple possible result expressions.

Simple CASE expression: 

CASE input_expression 

     WHEN when_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END

SQL> select empno,ename,

  2  case deptno

  3  when 10 then 'reasearch'

  4  when 20 then 'operations'

  5  else 'other'

  6  end as dept_name

  7  from emp;

     EMPNO ENAME      DEPT_NAME

---------- ---------- ----------

      7369 SMITH      operations

      7499 ALLEN      other

      7521 WARD       other

      7566 JONES      operations

      7654 MARTIN     other

Using Informatica Converting 24 hour format to 12 hours(AM,PM) format

1. How to split one column into different with new filed. I have one column with 24 hour time format i want it to convert to AM, PM.

a. I have the source data in three columns like Col1, Col2 and Col3.




b. Take SQ and connect it to ROUTER transformation.

c. Take one group and write the shown condition for suppurate 0-11 as 12 am to 11 am.

d. Remaining will be pm in default group.

e. Connect two groups to two different EXPRESSION transformations.

f. In first EXPRESSION TRANSFORMATION for AM, take the new port as variable port and give the condition shown as below in the picture.

g. In second EXPRESSION TRANSFORMATION for PM, take the new port as variable port and give the condition shown as below in the picture.




h.  Finally connect the all ports to the target and its instance respectively.

OUT PUT 

Using parameter file concept

Follow the shown steps to implement Parameter file concept in informatica.

Screen 1:

mapping view 

 Screen 2:

You can see the option in mappings tab called parameters and variables 

Screen 3:

create the parameter with $$parametername

Screen 4:

five the condition like this in filter transformation expression window we have the variable tab there you created parameter will be presented,

using that parameter we can write the expression here.

Screen 5:

the parameter should create in .prm format in specific path

like this

Screen 6:

copy that path and specify in work flow level

Screen 7:

From parameter connection is establishing that's why we are creating the connection variable for source.

Screen 8:

For Target also.

Screen 9:

Start task.

Screen 10:

Here

Specification of .prm like this

[folder_name.WF:Work_flow_name.ST:Session_Task_name]

database_connection_specified_in_workflw=Database_Schema_name_for_source

database_connection_specified_in_workflw=Database_Schema_name_for_target

Parameter_name=parameter_value

Working with Stored Procedure Transformation in informatica

We have connected and unconnected in Stored procedure transformations in informatica

For connected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_agg (in_deptno in number, max_sal out number,

min_sal out number, avg_sal out number, sum_sal out number)

As

Begin

select max(Sal),min(sal),avg(sal),sum(sal) into max_sal,min_sal,avg_sal,sum_sal

from emp where deptno=in_deptno group by deptno;

End;

/

Execute the above script in TOAD or Database

import stored procedure in mapping area directly connect to target.

For unconnected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_unconn_1_value(in_deptno in number, max_sal out number)

As

Begin

Select max(Sal) into max_sal from EMP where deptno=in_deptno;

End;

/

Execute the above script in TOAD or Database

call stored procedure same as like unconnected lookup

use expression transformation for calling Unconnected stored procedure,

in functions tab in expression window

Easy steps to do SCD Type 1

SCD type 1 explanation:

Slowly Changing dimension Type 1 doesn't carry the history simply value will be updated with new value. 

Source Table: Customer source table

Target Table: Customer target Stage table

Mapping Design 

Step 1: 

Step 2:

Lookup condition 

Step 3:

Step 4:

Expression Transformation new ports

Step 5:

Expression For changed Flog 

Step 6:

Expression For New Flog

Step 7:

Map to filters from Expression

Step 8:

1st filter condition 

Step 9:

2nd Filter Condition 

Step 10:

connect two filters to two different Update Strategies 

For Inserts Condition : DD_INSERT

For Updates Condition : DD_Update

Step 11:

Connect to target from Update Strategies

Use Target Instance for it

use sequence generator to generate the sequences 

Finally using those Sequence numbers LKP will find the new and changes records in Type 1 Dimension.

Informatica Architecture in brief