Informatica Cloud (IICS) Scenario based Interview Questions

Instead of providing some scenario based Interview questions and solutions to them I would like to take a different approach here.

We shall take a “concept” and discuss what kind of scenarios based Interview questions that could be built around it.

Chapter-1: Adding Sequence numbers to the Source records
Chapter-2: Introducing a dummy field
Chapter-3: Comparing Current record with Previous record
Chapter-4: LEAD and LAG implementation in Informatica Cloud
Chapter-5: Denormalizing data in Informatica Cloud

Let us discuss in detail how to add sequence numbers to your source data and in what kind of scenarios do we need to do it.

Chapter-1: Adding Sequence numbers to the Source records

I. Introduction: How to add sequence numbers to your source data?

This can be implemented in two different ways

1. Using Expression transformation
2. Using Sequence Generator transformation

II. Generating sequence numbers using Expression transformation

In the expression transformation create a variable port and assign a value as incrementing the same variable by 1.
Next create an output port and assign the variable to the output port.
In expression transformation, the ports will be as below

V_count = V_count+1
O_count = V_count

III. Generating sequence numbers using Sequence Generator transformation

Sequence Generator transformation is a passive and connected transformation in Informatica Cloud. It is used to generate sequence of numeric values.

Use Sequence generator transformation to

• Generate sequence of unique numbers
• Generate a cyclic sequence of numbers

To generate the sequence numbers, we always use the NEXTVAL column from sequence generator transformation.

Q1. Design a mapping to load alternate records to different tables

Description: The question says alternate records i.e the 1^st, 3^rd, 5^th … records should be loaded in Target1 and 2^nd, 4^th, 6^th … records should be loaded in Target2.

It means the odd and even records should be loaded in different targets.

Solution:

1. Map the fields from source to an expression transformation.
2. Create a new variable field of type integer V_count and assign value as V_count+1
3. Create a new output field of type integer Flag and assign value as MOD(V_count,2).
4. The MOD functions returns the remainder of a division calculation. So for an odd record the output will be 1 and for an even record the output will be zero when divided by 2.
5. In expression the ports will be as below

V_count = V_count+1
Flag = MOD(V_count,2)

6. Map the fields from expression to a router transformation. In router create two new output groups.

7. Map the records from Odd output group to Target1 and Even output group to Target2.

NOTE: If you are using a sequence generator transformation, there is an additional step involved. You need to map fields from source to sequence generator and then to expression. In expression create only one output port ‘Flag’ and assign value as MOD(NEXTVAL,2). The rest of the procedure is same.

Q2. Design a mapping to load alternate group of three records into three different targets

Description: The requirement here is to load the 1^st record into 1^st target, 2^nd record into 2^nd target, 3^rd record into 3^rd target. When the 4^th record comes it should begin loading again from 1^st target and the sequence continues.

At the end, the 1^st target should contain data of row 1, 4, 7, 10 ….
At the end, the 2^nd target should contain data of row 2, 5, 8, 11 ….
At the end, the 3^rd target should contain data of row 3, 6, 9, 12 ….

Solution:

In the earlier case as there are only two targets we used the odd/even strategy.
In this case in order to achieve this we have to increment the count of records from 1 to 3 and for the next record when the count becomes >3, we need to reset the count back to 1.

Follow the below steps to implement the solution

1. Map the fields from source to an expression transformation.
2. Create a new variable field of type integer V_count and assign value as IIF(V_count=3, 1, V_count+1)
3. Create a new output field of type integrer Row_Number and assign value as V_count.
4. In expression the ports will be as below

V_count = IIF(V_count=3, 1, V_count+1)
Row_Number = V_count

5. Map the fields from expression to a router transformation. In router create three new output groups.

6. Map the records from each group to the respective target transformations.

NOTE: This can also be implemented using a sequence generator transformation. The only change required is to replace expression with sequence generator. In the sequence generator properties enable the Cycle option. Set the Cycle Start Value as 1 and End Value as 3. The rest of the procedure is same.

The ODD/EVEN example we discussed earlier can also be implemented using this approach by resetting the value to 1 whenever count becomes >2.

Q3. Design a mapping to load every 150^th record to the target table

Description: For example, if the source have 300 records, only the 150^th and 300^th record should be loaded.

Solution:

As discussed in above examples, we can implement the solution here in multiple methods.

Method1:

1. Map the fields from source to an expression transformation.
2. Create a new variable field of type integer V_count and assign value as V_count+1
3. Create a new output field of type integer Flag and assign value as MOD(V_count,150).
4. In expression the ports will be as below

V_count = V_count+1
Flag = MOD(V_count,150)

5. Map the fields from expression to a Filter transformation and provide filter condition as Flag=0
6. Map the records from filter to target transformation.

Method2:

This method is similar to the above discussed method except a change in expression transformation.
The ports in expression transformation should be as below

V_count = IIF(V_count=150,1,V_count+1)
Flag = V_count

The rest of the procedure is same.

IV. Conclusion

The above two methods can also be implemented using Sequence generator transformation. That makes it four different approaches to implement the solution.

As we are in the context of discussing the sequence numbers and resetting their values, this question might seem simple now. But in a real world interview scenario this question might confuse us.

Click Next button to navigate to the next chapter of Scenario based questions.