Informatica Scenario Based Interview Questions

21. How can we store previous session logs?
Answer: If you run the session in the time stamp mode then automatically session log out will not overwrite the current session log.

• Go to Session Properties –> Config Object –> Log Options
• Select the properties as follows:
• Save session log by –> SessionRuns
• Save session log for these runs –> Change the number that you want to save the number of log files (Default is 0)
• If you want to save all of the log files created by every run, and then select the option Save session log for these runs –> Session TimeStamp
• You can find these properties in the session/workflow Properties.

22. Suppose we configure Sorter transformations in the master and detail pipelines with the following sorted ports in order: ITEM_NO, ITEM_NAME, PRICE. When we configure the join condition, what are the guidelines we need to follow to maintain the sort order?
Answer: If we have sorted both the master and detail pipelines in order of the ports say ITEM_NO, ITEM_NAME and PRICE we must ensure that:

• Use ITEM_NO in the First Join Condition.
• If we add a Second Join Condition, we must use ITEM_NAME.
• If we want to use PRICE as a Join Condition apart from ITEM_NO, we must also use ITEM_NAME in the Second Join Condition.
• If we skip ITEM_NAME and join on ITEM_NO and PRICE, we will lose the input sort order and the Integration Service fails the session.

23. How an Expression Transformation differs from Aggregator Transformation?
Answer: An Expression Transformation performs a calculation on a row-by-row basis. An Aggregator Transformation performs calculations on groups.

24. What is Incremental Aggregation?
Answer: We can enable the session option, Incremental Aggregation for a session that includes an Aggregator Transformation. When the Integration Service performs incremental aggregation, it actually passes changed source data through the mapping and uses the historical cache data to perform aggregate calculations incrementally.
For reference check Implementing Informatica Incremental Aggregation.

25. What are the performance considerations when working with Aggregator Transformation?
Answer:

1. Filter the unnecessary data before aggregating it. Place a Filter transformation in the mapping before the Aggregator transformation to reduce unnecessary aggregation.
2. Improve performance by connecting only the necessary input/output ports to subsequent transformations, thereby reducing the size of the data cache.
3. Use Sorted input which reduces the amount of data cached and improves session performance.

26. What will happen if the SELECT list COLUMNS in the Custom override SQL Query and the OUTPUT PORTS order in SQ transformation do not match?
Answer: Mismatch or Changing the order of the list of selected columns to that of the connected transformation output ports may result in session failure.

27. What are the different ways to implement parallel processing in Informatica?
Answer: We can implement parallel processing using various types of partition algorithms:

Database partitioning: The Integration Service queries the database system for table partition information. It reads partitioned data from the corresponding nodes in the database.

Round-Robin Partitioning: Using this partitioning algorithm, the Integration service distributes data evenly among all partitions. It makes sense to use round-robin partitioning when you need to distribute rows evenly and do not need to group data among partitions.

Hash Auto-Keys Partitioning: The Powercenter Server uses a hash function to group rows of data among partitions. When the hash auto-key partition is used, the Integration Service uses all grouped or sorted ports as a compound partition key. You can use hash auto-keys partitioning at or before Rank, Sorter, and unsorted Aggregator transformations to ensure that rows are grouped properly before they enter these transformations.

Hash User-Keys Partitioning: Here, the Integration Service uses a hash function to group rows of data among partitions based on a user-defined partition key. You can individually choose the ports that define the partition key.

Key Range Partitioning: With this type of partitioning, you can specify one or more ports to form a compound partition key for a source or target. The Integration Service then passes data to each partition depending on the ranges you specify for each port.

Pass-through Partitioning: In this type of partitioning, the Integration Service passes all rows from one partition point to the next partition point without redistributing them.

28. Mention a few design and development best practices for Informatica?
Answer: 

• Mapping design tips: Standards – sticking to consistent standards is beneficial in the long run. This includes naming conventions, descriptions, environment settings, parameter files, documentation, among others.
• Reusability – in order to react quickly to potential changes, use Informatica components like applets, worklets, and reusable transformations.
• Scalability – when designing and developing mappings, it is a good practice to keep volumes in mind. This is caching, queries, partitioning, initial vs incremental loads.
• Simplicity – it is recommended to create multiple mappings instead of a few complex ones. Use Staging Area and try to keep the processing logic as clear and simple as possible.
• Modularity – use the modular design technique (common error handling, reprocessing).
• Mapping development best practices
• Source Qualifier – use shortcuts, extract only the necessary data, limit the reading of columns and rows on the source. Try to use the default query options (User Defined Join, Filter) instead of using SQL Query override which may impact database resources and make unable to use partitioning and push-down.
• Expressions – use local variables to limit the number of redundant calculations, avoid datatype conversions, reduce invoking external scripts (coding outside of Informatica), provide comments, use operators (||, +, /) instead of functions. Keep in mind that numeric operations are generally faster than string operations.
• Filter – use the Filter transformation as close to the source as possible. If multiple filters need to be applied, usually it’s more efficient to replace them with Router.
• Aggregator – use sorted input, also use as early (close to the source) as possible and filter the data before aggregating.
• Joiner – try to join the data in Source Qualifier wherever possible, and avoid outer joins. It is good practice to use a source with fewer rows, such as a Master source.
• Lookup – relational lookup should only return ports that meet the condition. Call Unconnected Lookup in expression (IIF). Replace large lookup tables with joins whenever possible. Review the database objects and add indexes to database columns when possible. Use Cache Calculator in session to eliminate paging in lookup cache.

29. What is Enterprise Data Warehousing?
Answer: When a large amount of data is assembled at a single access point then it is called as Enterprise Data Warehousing. This data can be reused and analyzed at regular intervals or as per the need of the time requirement.
Considered as the central database or say a single point of access, Enterprise data warehousing provides a complete global view and thus helps in decision support.

It can be more understood from the following points which define its features:

• All important business information stored in this unified database can be accessed from anywhere across the organization.
• Although the time required is more, periodic analysis on this single source always produces better results.
• Security and integrity of data are never compromised while making it accessible across the organization.

30. How can we use Batches?
Answer: Batches are the collection of sessions which is used to migrate the data from the source to target on a server. Batches can have the largest number of sessions in it but they cause more network traffic whereas less number of sessions in a batch can be moved rapidly.