Informatica IDMC/IICS interview questions and answers
81. What is a Router transformation in IICS?
A Router transformation in IICS is a transformation that can route data to different targets based on a set of routing conditions. The transformation evaluates the routing conditions for each record and sends the record to the appropriate target.
Example: Suppose a company wants to use IICS to process sales data, and they want to send high-value sales to one target and low-value sales to another target. They could use a Router transformation to route the sales data based on the ‘sales’ field.
82. What is a Union transformation in IICS?
A Union transformation in IICS is a transformation that can combine data from multiple sources into a single output. The transformation appends records from each source to the output.
Example: If a company wants to use IICS to combine sales data from multiple regions, they could use a Union transformation. The transformation would append the sales data from each region into a single output.
83. What is a Normalizer transformation in IICS?
A Normalizer transformation in IICS is a transformation that can normalize data. Normalizing data involves converting a single record with multiple occurrences of a group of fields into multiple records with single occurrences of the group of fields.
Example: Suppose a company has sales data where each record represents a sale, and each sale includes multiple items. They could use a Normalizer transformation to convert each sale into multiple records, with each record representing one item.
84. What is a Rank transformation in IICS?
A Rank transformation in IICS is a transformation that can rank data. The transformation sorts data based on a set of rank conditions, and it selects the top or bottom N records.
Example: If a company wants to use IICS to identify the top 10 customers by sales, they could use a Rank transformation. The transformation would sort the customers by the ‘sales’ field in descending order and select the top 10 records.
85.What is an Expression transformation in IICS?
An Expression transformation in IICS is a transformation that can calculate values. The transformation uses expressions to calculate values based on the input data.
Example: Suppose a company wants to use IICS to calculate the total price for each sale, where the total price is the product of the ‘quantity’ field and the ‘price’ field. They could use an Expression transformation to calculate the ‘total price’ field.
86. What is a Sequence Generator transformation in IICS?
A Sequence Generator transformation in IICS is a transformation that can generate a sequence of numbers. The transformation generates a new number for each input record, and the numbers are incremented by a specified value.
Example: If a company wants to use IICS to assign a unique ID to each sale, they could use a Sequence Generator transformation. The transformation would generate a unique ID for each sale, ensuring that each sale can be identified uniquely.
87. What is a Stored Procedure transformation in IICS?
A Stored Procedure transformation in IICS is a transformation that can be called a stored procedure in a database. The transformation sends a request to the database to execute the stored procedure, and it can capture the results of the stored procedure.
Example: Suppose a company has a stored procedure in a database that calculates the total sales for each region. They could use a Stored Procedure transformation in IICS to call the stored procedure and capture the results.
88. What is a Transaction Control transformation in IICS?
A Transaction Control transformation in IICS is a transformation that can control transaction boundaries. The transformation can commit or roll back transactions based on the input data.
Example: If a company wants to use IICS to load sales data into a database, and they want to commit the transaction after each batch of 100 sales, they could use a Transaction Control transformation. The transformation would commit the transaction after each batch of 100 sales, ensuring that the data is saved in the database.
89. What is an Update Strategy transformation in IICS?
An Update Strategy transformation in IICS is a transformation that can control how data is updated in a target. The transformation can specify whether each record should be inserted, updated, deleted, or rejected.
Example: Suppose a company wants to use IICS to update sales data in a database. They could use an Update Strategy transformation to specify whether each sale should be inserted (if it’s a new sale), updated (if it’s an existing sale with changed details), or deleted (if it’s an existing sale that has been canceled).
90. What are the different lookup caches?
Lookups can be cached or uncached (No cache). Cached lookup can be either static or dynamic. A static cache is one that does not modify the cache once it is built, and it remains the same during the session run. On the other hand, A dynamic cache is refreshed during the session run by inserting or updating the records in the cache based on the incoming source data.
A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after session run is complete or not respectively.
91. Why is Sorter an Active Transformation?
When the Sorter transformation is configured to treat output rows as distinct, it assigns all ports as part of the sort key. The Integration Service discards duplicate rows compared during the sort operation. The number of Input Rows will vary as compared with the Output rows and hence it is an Active transformation.
92. How to improve the performance of Aggregator Transformation in IICS?
Aggregator performance improves dramatically if records are sorted before passing to the aggregator and the “sorted input” option under aggregator properties is checked. The record set should be sorted on those columns that are used in the Group By operation.It is often a good idea to sort the record set in database level. e.g. inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.
93. What is Mapplet in IICS?
A mapplet is a reusable object that represents a set of transformations. It allows you to reuse transformation logic and can contain as many transformations as you need. You create mapplets in the Mapplet Designer. Its different than a reusable transformation as it may contain a set of transformations, while a reusable transformation is a single one.
94. What is a Fact and Dimension table?
Fact Table:
- It contains all the primary keys of the dimension and associated facts or measures like quantity sold, amount sold, and average sales
- Commonly used Fact tables are Transactions_fact, Sales_fact and Purchase_item_fact etc
Dimension Tables:
- Dimension tables provide descriptive information for all the measurements recorded in the fact table.
- Dimensions are relatively very small as comparison to the fact table.
- Commonly used dimensions are Customer, Product, Location, Account, User, and time etc.
95. What is SQL override in a source transformation?
The Source Qualifier provides the SQL Query option to override the default query. You can enter any SQL statement supported by your source database. You might enter your own SELECT statement, have the database perform aggregate calculations, or call a stored procedure or stored function to read the data and perform some tasks.
96. What is a Master Outer Join?
A master outer join is a specific join typesetting within a joiner transformation. In a master outer join, all records from the detail source are returned by the join and only matching rows from the master source are returned.
97. What is a surrogate key?
A surrogate key is a sequentially generated unique number attached with each record in a Dimension table. It is used in substitution for the natural primary key.
A Surrogate Key is any column or set of columns attached to every record in a Dimension table in a Data Warehouse. It is used as a substitute or replacement for the primary key when the update process becomes difficult for a future requirement.
98. How to delete duplicate rows from flat files?
We can use the sorter transformation to delete duplicate rows from flat files and select the distinct option.
99. Do we have re-usable transformation in iics?
No, we do not have re-usable transformation in IICS, instead we can use mapplet with single transformation.
100. How can we create worklet in ICIS?
We do not have worklet in IICS, instead we can call a sub taskflow inside a taskflow.
Enroll for Informatica IICS Cloud Data Integration(CDI) – Self-Paced courses
