Informatica Scenario Based Interview Questions with Answers for experienced Part 2

Informatica Scenario Based Interview Problems with Answers - Part 2

Q 8. Let us assume the following Sales Data

    

ID	PERSONAL_SALES
10	40000
20	80000
30	40000
40	60000
50	NULL
60	50000

Q 8.1. Design a Informatica mapping to calculate the cumulative sum of PERSONAL_SALES and load that into target table?

The Expected Output in target should be

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	40000
20	80000	120000
30	40000	160000
40	60000	220000
50	NULL	220000
60	50000	270000

Solution:

Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression CUME(PERSONAL_SALES), again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.

In the expression transformation,the ports are

ID                                           PERSONAL_SALES                               V_RETURN_VALUE = CUME(PERSONAL_SALES) O_RETURN_VALUE =  V_RETURN_VALUE

// Input/Output Port // Input/Output Port // Variable Port // Output Port

Connect the output port from the expression transformation to the target and see the results.

Q 8.2. Design a Informatica mapping to calculate the Moving sum of PERSONAL_SALES for two rows and load that into target table?

The Expected Output in target should be

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	NULL
20	80000	120000
30	40000	120000
40	60000	100000
50	NULL	60000
60	50000	50000

Solution:

Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression MOVINGSUM(PERSONAL_SALES,2) again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.

In the expression transformation,the ports are

ID                                           PERSONAL_SALES                               V_RETURN_VALUE = MOVINGSUM(PERSONAL_SALES,2) O_RETURN_VALUE =  V_RETURN_VALUE

// Input/Output Port // Input/Output Port // Variable Port // Output Port

Connect the output port to the target and see the results

Q 8.3. Design a Informatica mapping to get the previous row value for the current row. If there is no previous row then load NULL as in case of first row there is no previous row

The Expected Output in target should be

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	NULL
20	80000	40000
30	40000	80000
40	60000	40000
50	NULL	60000
60	50000	NULL

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_COUNT and start incrementing it by one for each row entering the expression transformation. Also create V_SALES variable port and assign the expression IIF(V_COUNT=1,NULL,V_PREV_SALES) to it . Then Create one more variable port V_PREV_SALES and assign PERSONAL_SALES to it. Now create output port O_RETURN_VALUE and assign V_SALES to it. Connect the expression transformation to the target ports and run the mapping after creation of precedding session and workflow.

In the expression transformation, the ports will be

ID                                      PERSONAL_SALES                          V_COUNT=V_COUNT+1                       V_SALES=IIF(V_COUNT=1,NULL,V_PREV_SALES) V_PREV_SALES=PERSONAL_SALES             O_RETURN_VALUE=V_SALES

// Input/Output Port // Input/Output Port // Variable Port // Variable Port // Variable Port // Output Port

Q 8.4. Design a mapping to get the next row Sales 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

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	80000
20	80000	40000
30	40000	60000
40	60000	NULL
50	NULL	50000

Solution:

1. Create a source qualifier transformation and connect it to two separate expression transformation. In each of the expression transformation create a variable port V_COUNT and increment it by 1 using expression V_COUNT+1. Then create an output port O_COUNT in each expression transformation. Assign the value V_COUNT in 1st expression and V_COUNT-1 in the 2nd expression as shown below.

In the 1st expression transformation, the ports will be

ID                                           PERSONAL_SALES                               V_COUNT=V_COUNT+1 O_COUNT=V_COUNT

// Input/Output Port // Input/Output Port // Variable Port // Output Port

1st Expression Data will look like

ID	PERSONAL_SALES	O_COUNT
10	40000	1
20	80000	2
30	40000	3
40	60000	4
50	NULL	5

In the 2nd expression transformation, the ports will be

ID                                           PERSONAL_SALES                               V_COUNT=V_COUNT+1  O_COUNT=V_COUNT-1

// Input/Output Port // Input/Output Port // Variable Port // Output Port

2nd Expression Data will look like

ID	PERSONAL_SALES	O_COUNT
10	40000	0
20	80000	1
30	40000	2
40	60000	3
50	NULL	4

2. Connect both the expression transformations ports to joiner transformation and join them on the port O_count. Consider the 1st expression transformation as Master and 2nd one as Detail Source. Specify the join type in Joiner transformation as Detail Outer Join. In the joiner transformation check the property sorted input this will allow you to connect both expression transformations to joiner transformation.

3. Pass the output of joiner transformation to a target table. In the Joiner transformation, connect the ID and PERSONAL_SALES from the 1st expression transformation to the ID and PERSONAL_SALES in target table. Connect the PERSONAL_SALES which is coming from the 2nd expression transformation to the next row sales port in the target table.

Q 8.5. Design a Informatica Mapping to find the sum of all sales of an employee id and this sum should come in another column for all rows.

The output should look like as

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	270000
20	80000	270000
30	40000	270000
40	60000	270000
50	NULL	270000
60	50000	270000

Solution:

Create a source qualifier transformation and connect it to the Aggregator transformation.In aggregator transformation create a Output port O_RETURN_VALUE and assign expression SUM(PERSONAL_SALES). Don not specify and group by for any filed and then connect all the other output ports to target and runt the mapping.

In the Aggregator transformation, the ports will be

ID                                           PERSONAL_SALES                               O_RETURN_VALUE=SUM(PERSONAL_SALES)

// Input/Output Port // Input/Output Port // Output Port