Informatica Interview Question and Answers

5. What is mean by Slowly Changing Dimensions?
  Dimensions don't change in predicable ways. Individual customers and products evolve slowly and episodically. Some of the changes are true physical changes. Customers change their addresses because they move. A product is manufactured with different packaging. Other changes are actually corrections of mistakes in the data. And finally, some changes are changes in how we label a product or customer and are more a matter of opinion than physical reality. We call these variations Slowly Changing Dimension (SCD).
The 3 fundamental choices for handling the slowly changing dimension are:
  • Overwrite the changed attribute, thereby destroying previous history.e.g. Useful when correcting an error.
  • Issue a new record for the customer, keeping the customer natural key, but creating a new surrogate primary key.
  • Create an additional field in the existing customer record, and store the old value of the attribute in the additional field. Overwrite the original attribute field.
  • Type 1 SCD is an overwrite of a dimensional attribute. History is definitely lost. We overwrite when we are correcting an error in the data or when we truly don't want to save history.
  • A Type 2 SCD creates a new dimension record and requires a generalized or surrogate key for the dimension. We create surrogate keys when a true physical change occurs in a dimension entity at a specific point in time, such as the customer address change or the product packing change. We often add a timestamp and a reason code in the dimension record to precisely describe the change. The Type 2 SCD records changes of values of dimensional entity attributes over time. The technique requires adding a new row to the dimension each time there's a change in the value of an attribute (or group of attributes) and assigning a unique surrogate key to the new row.
  • A Type 3 SCD adds a new field in the dimension record but does not create a new record. We might change the designation of the customer's sales territory because we redraw the sales territory map, or we arbitrarily change the category of the product from confectionary to candy. In both cases, we augment the original dimension attribute with an "old" attribute so we can switch between these alternate realities.
Your Name Your Email-ID
Your Answer
6. How can you define a transformation?
  A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data. Below are the various transformations available in Informatica:
  • Aggregator
  • Application Source Qualifier
  • Custom
  • Expression
  • External Procedure
  • Filter
  • Input
  • Joiner
  • Lookup
  • Normalizer
  • Output
  • Rank
  • Router
  • Sequence Generator
  • Sorter
  • Source Qualifier
  • Stored Procedure
  • Transaction Control
  • Union
  • Update Strategy
  • XML Generator
  • XML Parser
  • XML Source Qualifier
Your Name Your Email-ID
Your Answer
7. What is a mapplet?
  A mapplet is a reusable object that is created using mapplet designer. The mapplet contains set of transformations and it allows us to reuse that transformation logic in multiple mappings.
Your Name Your Email-ID
Your Answer
8. What is the difference between OLTP and ODS?
  • OLTP is online transaction processing systems and ODS - Operational Database System.
  • In OLTP we can save the current data, it depends on the day to day transactions and it stores the day to day data.
  • In ODS we can store data for a month also and it is not restricted to a specific day or transaction.
Your Name Your Email-ID
Your Answer

131415 Page 2 of 15