#informatica architecture

Informatica PowerCenter Integration Service Architecture

Informatica Integration Service . While discussing Informatica Architecture, we also went through various informatica services involved .Out of them most important one is Informatica Integration Service . Informatica Integration Service  is created while installing the Power Centre on the server.After creation of Integration service we use Administration console to mange the Integration Services.…

Q) How to generate or load values in to the target table based on a column value using informatica etl tool.

I have the products table as the source and the data of the products table is shown below.

Table Name: Products Product Quantity ----------------- Samsung NULL Iphone 3 LG 0 Nokia 4

Now i want to duplicate or repeat each product in the source table as many times as the value in the quantity column. The output is

product Quantity ---------------- Iphone 3 Iphone 3 Iphone 3 Nokia 4 Nokia 4 Nokia 4 Nokia 4

The Samsung and LG products should not be loaded as their quantity is NULL, 0 respectively.

Now create informatica workflow to load the data in to the target table?

Solution:

Follow the below steps

Create a new mapping in the mapping designer

Drag the source definition in to the mapping

Create the java transformation in active mode

Drag the ports of source qualifier transformation in to the java transformation.

Now edit the java transformation by double clicking on the title bar of the java transformation and go to the “Java Code” tab.

Enter the below java code in the “Java Code” tab.

if (!isNull("quantity")) { double cnt = quantity; for (int i = 1; i <= quantity; i++) { product = product; quantity = quantity; generateRow(); } }

Now compile the java code. The compile button is shown in red circle in the image.

Connect the ports of the java transformation to the target.

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.

First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don’t check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).

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: