#informatica basics

Informatica Real Time Scenarios:

Q1) Alternate Target Loading

My source is a flat file which contains N number of records. I want to load the source data into two targets such that first five records should loaded into the first target, next five records into the second target table. Again the next source five records into the first target table and so on. How to implement a Informatica mapping logic for this?

Solution:

Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports:

v_cnt (variable port) = v_cnt+1 o_cnt (output port) = v_cnt

Connect the expression transformation to the router transformation. Create two output groups in the router transformation and specify the following filter conditions:

--Filter condition for first output group DECODE(substr(o_cnt,-1,1),1,TRUE,2,TRUE,3,TRUE,4,TRUE,5,TRUE,FALSE) --Filter condition for second output group DECODE(substr(o_cnt,-1,1),6,TRUE,7,TRUE,8,TRUE,9,TRUE,0,TRUE,FALSE)

Connect the router transformation output groups to the appropriate targets.

Q2) Load source data in multiple session run.

I have flat file as a source which contains N number of records. My requirement is to load half of the source data into the target table in the first session run and the remaining half of the records in the second session run. Create Informatica mapping to implement this logic? Assume that the source data does not change between session runs.

Solution:

Create a mapping to find out the number of records in the source and write the count to a parameter file. Let call this parameter as $$SOURCE_COUNT.

Create another mapping. Go to the mapping parameters and variables, create a mapping variable ($$VAR_SESSION_RUNS) with integer data type.

Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports.

v_Count (variable port) = v_Count+1 O_Run_flag (output port) = IIF($$vAR_SESSION_RUNS=0, setvariable($$vAR_SESSION_RUNS,1), IIF( !ISNULL($$vAR_SESSION_RUNS) and v_Count=1, 2, $$vAR_SESSION_RUNS) ) O_count (output port) = V_Count

Connect the expression transformation to the filter transformation and specify the following filter condition:

IIF (O_Run_Flag =1, v_count<= $$SOURCE_COUNT/2, IIF (O_Run_Flag =2, v_count > $$SOURCE_COUNT/2))

Connect the filter transformation to the target.

1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica.

Consider the below sales table as an example?

Table name: Sales

product, prod_quantity, price , Year A         , 10                 , 100  , 2010 B         , 15                 , 150  , 2010 A         , 8                   , 80    , 2011 B         , 26                 , 260  , 2011

Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year.

Here in this example, Product A sold less in 2011 when compared with the sales in 2010.

This problem can be easily implemented with the help of SQL query as shown below

SELECT  cy.* FROM    SALES cy, SALES py WHERE   cy.product = py.product AND        cy.year=2011 AND        py.year=2010 AND       cy.prod_quantity < py.prod_quantity;

In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica.

Solution:

STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy  (cy means current year) and the other as sq_py  (py means previous year).

STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010

STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail.

STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port.

STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition.

2. How to implement the not exists operator in informatica which is available in database?

Solution:

Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table.

1. Consider the following employees data as source

employee_id, salary 10, 1000 20, 2000 30, 3000 40, 5000

Q1. Design a mapping to load the cumulative sum of salaries of employees into target table? The target table data should look like as

employee_id, salary, cumulative_sum 10, 1000, 1000 20, 2000, 3000 30, 3000, 6000 40, 5000, 11000

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_cum_sal and in the expression editor write V_cum_sal+salary. Create an output port O_cum_sal and assign V_cum_sal to it.

Q2. Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null. The output should look like as

employee_id, salary, pre_row_salary 10, 1000, Null 20, 2000, 1000 30, 3000, 2000 40, 5000, 3000

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_count and increment it by one for each row entering the expression transformation. Also create V_salary variable port and assign the expression IIF(V_count=1,NULL,V_prev_salary) to it . Then create one more variable port V_prev_salary and assign Salary to it. Now create output port O_prev_salary and assign V_salary to it. Connect the expression transformation to the target ports.

In the expression transformation, the ports will be

employee_id salary V_count=V_count+1 V_salary=IIF(V_count=1,NULL,V_prev_salary) V_prev_salary=salary O_prev_salary=V_salary

Q3. Design a mapping to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null. The output should look like as

employee_id, salary, next_row_salary 10, 1000, 2000 20, 2000, 3000 30, 3000, 5000 40, 5000, Null

Solution:

Step1: Connect the source qualifier to two expression transformation. In each expression transformation, create a variable port V_count and in the expression editor write V_count+1. Now create an output port O_count in each expression transformation. In the first expression transformation, assign V_count to O_count. In the second expression transformation assign V_count-1 to O_count.

In the first expression transformation, the ports will be

employee_id salary V_count=V_count+1 O_count=V_count

In the second expression transformation, the ports will be

employee_id salary V_count=V_count+1 O_count=V_count-1

Step2: Connect both the expression transformations to joiner transformation and join them on the port O_count. Consider the first expression transformation as Master and second one as detail. In the joiner specify the join type as Detail Outer Join. In the joiner transformation check the property sorted input, then only you can connect both expression transformations to joiner transformation.

Step3: Pass the output of joiner transformation to a target table. From the joiner, connect the employee_id, salary which are obtained from the first expression transformation to the employee_id, salary ports in target table. Then from the joiner, connect the salary which is obtained from the second expression transformaiton to the next_row_salary port in the target table.

Q4. Design a mapping to find the sum of salaries of all employees and this sum should repeat for all the rows. The output should look like as

employee_id, salary, salary_sum 10, 1000, 11000 20, 2000, 11000 30, 3000, 11000 40, 5000, 11000

Solution:

Step1: Connect the source qualifier to the expression transformation. In the expression transformation, create a dummy port and assign value 1 to it.

In the expression transformation, the ports will be

employee_id salary O_dummy=1

Step2: Pass the output of expression transformation to aggregator. Create a new port O_sum_salary and in the expression editor write SUM(salary). Do not specify group by on any port.

In the aggregator transformation, the ports will be

salary O_dummy O_sum_salary=SUM(salary)

Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

Step4: Pass the output of joiner to the target table.

2. Consider the following employees table as source

department_no, employee_name 20, R 10, A 10, D 20, P 10, B 10, C 20, Q 20, S

Q1. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list 10, A 10, A,B 10, A,B,C 10, A,B,C,D 20, A,B,C,D,P 20, A,B,C,D,P,Q 20, A,B,C,D,P,Q,R 20, A,B,C,D,P,Q,R,S

Solution:

Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be

department_no employee_name V_employee_list = IIF(ISNULL(V_employee_list),employee_name,V_employee_list||’,’||employee_name) O_employee_list = V_employee_list

Step2: Now connect the expression transformation to a target table.

Q2. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list 10, A 10, A,B 10, A,B,C 10, A,B,C,D 20, P 20, P,Q 20, P,Q,R 20, P,Q,R,S

Solution:

Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be

department_no employee_name V_curr_deptno=department_no V_employee_list = IIF(V_curr_deptno! = V_prev_deptno,employee_name,V_employee_list||’,’||employee_name) V_prev_deptno=department_no O_employee_list = V_employee_list

Step2: Now connect the expression transformation to a target table.

Q3. Design a mapping to load a target table with the following values from the above source?

department_no, employee_names 10, A,B,C,D 20, P,Q,R,S

Solution: