# Informatica Cloud (IICS) Scenario based Interview Questions

Spread the love

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 1st, 3rd, 5th … records should be loaded in Target1 and 2nd, 4th, 6th … 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 1st record into 1st target, 2nd record into 2nd target, 3rd record into 3rd target. When the 4th record comes it should begin loading again from 1st target and the sequence continues.

At the end, the 1st target should contain data of row 1, 4, 7, 10 ….
At the end, the 2nd target should contain data of row 2, 5, 8, 11 ….
At the end, the 3rd 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 150th record to the target table

Description: For example, if the source have 300 records, only the 150th and 300th 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.

### 9 thoughts on “Informatica Cloud (IICS) Scenario based Interview Questions”

1. Nice Explanation !!!

Kindly add more relevant questions related to data integration.

Thanks,
Jyoti

Reply
• Thanks Jyothi.. More articles coming soon!!

Reply
2. Mind blowing. Please post more and more such scenarios. Your approach is very interesting and easy to understand.Very much appreciated!
It is very hard to find material on IICS on web, finding this site is blessing.
Please make YouTube channel too we would love to subscribe it!

Reply
• Thank you very much!!😃😊
Please support us by following on LinkedIn, Instagram and Facebook.

Reply
3. Done.. Following everywhere now

Reply
4. keep it up.. Nice content and explaination

Reply
5. Very nicely done. I have never seen such helpful tutorials, scenarios, quizzes for IICS available anywhere. Not even informatica itself. Thank you!

Reply
• Thanks Purval. Appreciate your feedback!!

Reply