Informatica Interview Questions Part - 5
What is an Expression Transformation in Informatica?
An expression transformation in Informatica is a common Powercenter mapping transformation. It is used to transform data passed through it one record at a time. The expression transformation is passive and connected. Within an expression, data can be manipulated, variables created, and output ports generated. We can write conditional statements within output ports or variables to help transform data according to our business requirements.
What is a Sorter Transformation in Informatica?
The sorter transformation in Informatica helps us sort collections of data by port or ports. This functionality very much like an ORDER BY SQL statement where we specify certain field(s) we want to ORDER BY. The sorter transformation is active and connected. The sorter transformation contains some additional functionality that is very useful. For example we can check the “Distinct Output Rows” property to pass only distinct rows through a pipeline. We can also check the “Case Sensitive” property to sort data with case sensitivity in mind.
What is a Decode in Informatica?
A decode in Informatica is a function used within an Expression Transformation. It functions very much like a CASE statement in SQL. The decode allows us to search for specific values in a record, and then set a corresponding port value based on search results.
What is a Master Outer Join in Informatica?
A master outer join in Informatica is a specific join type setting within a joiner transformation. There are other join types, but let’s focus on the master outer join. The joiner transformation allows us to join two separate pipelines of data by specifying key port(s) from each pipeline. We define each pipeline as either a master or detail. This master outer join is very much like a LEFT OUTER JOIN in SQL, considering the detail pipeline as the LEFT side. So the master outer join returns all rows from the detail pipeline, and the matched rows from the master pipeline.
What is a Mapplet in Informatica?
The Mapplet in Informatica is essential to creating reusable mappings in Informatica. That is essentially what is a mapplet is for. Reuse is essential to building efficient business intelligence systems quickly. Within InformaticaPowercenter is a mapplet designer where we can copy existing mapping logic or create new logic we want to reuse in multiple mappings. We can create input and output transformations within a mapplet that define input and output ports for our mapplet within a mapping.
What is an Update Strategy Transformation in Informatica
The Update Strategy Transformation in Informatica helps us tag each record passed through it for insert, update, delete, or reject. This information lets the Integration Service know how to treat each record passed to a target. We can set this with any type of conditional logic within the update strategy expression property. This functionality is essential to control how data is stored within our business intelligence databases.
What is a Router Transformation in Informatica
The Router Transformation in Informatica allows us to split a single pipeline of data into multiple. We do this by using the transformation’s group tab, group filter condition. We can create as many groups as we want and therefore as many new pipelines. The router checks one record at a time with the group filter condition and routes it down the appropriate path or paths. Keep in mind a single record may be copied to many records if it matches multiple group filter conditions. Many times we use the router to check specific primary key conditions to determine if we want to insert, update, or delete data from a target table.
What is a Rank Transformation in Informatica?
The Rank Transformation in Informatica lets us sort and rank the top or bottom set of records based on a specific port. You can only have one rank port. Once we have selected which port to rank on, we must set a Top/Bottom attribute value and Number of Ranks attribute value. Top/Bottom set a descending (Top) or ascending (Bottom) rank order. The Number of Ranks attribute determines how many records to return. If we set it to 10, we will return 10 records.
What is a Filter Transformation in Informatica?
A Filter Transformation in Informatica is active and connected. Records passed through it, are allowed through based on evaluating TRUE for the developer defined filter condition. To help mapping performance, always place filter condition as early in the mapping data flow as possible. This will reduce processing needed by downstream mapping transformations.
What is a Sequence Generator Transformation in Informatica?
The Sequence Generator Transformation in Informatica is both passive and connected. Since its primary purpose is to generate integer values from both its NEXTVAL and CURRVAL default output ports, it is very helpful in creating new surrogate key values. We would generally leverage the NEXTVAL port to accomplish this.
What is a Joiner Transformation in Informatica?
The Joiner Transformation in Informatica is used to join two data pipelines in a mapping. We specify one or more ports from each pipeline as a join condition to relate each pipeline. This functionality is essential to relating data from multiple sources in a mapping including flat files, databases, XML files, and more. Within the joiner transformation we can specify different join types (Normal, Master Outer, Detail Outer, Full Outer) similar to our join options in SQL (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER).
What are Active and Passive Transformations in Informatica?
Most Informatica transformations are either passive or active. A passive transformation is one where records pass through without ever dropping or adding records. In contrast, an active transformation reduces or adds records to our target record pipeline count. Some transformations such as the lookup transformation in Informatica are both active and passive.
What is a Aggregator Transformation in Informatica?
A Aggregator Transformation in Informatica is very useful for aggregating data in a Informatica Powercenter mapping. The aggregator behaves similarly to a GROUP BY statement in SQL. With the aggregator transformation we can select a specific port or ports to aggregate by and apply aggregator type functions (SUM, MAX, MIN, etc…) to all non group by ports. If we do not apply an aggregate function to a non group by port, the last records value will be set for this port. Aggregation is an important part of integrating data so make sure you understand the aggregator transformation in Informatica.
What is a Union Transformation in Informatica?
A Union Transformation in Informatica functions just like a UNION ALL statement in SQL. It allows us to incorporate two pipelines of data with the same port names and data types, into a single pipeline of data. Keep in mind that the union transformation does not collapse records with the same port values into a single distinct record. In SQL we could write a UNION statement for this instead of UNION ALL, but this functionality does not currently exist through the union transformation in Informatica.
What is a Informatica Worklet?
Informatica Worklets allow us to apply re-usability at the Informatica workflow level. This concept is very similar to the mapplet. However mapplets apply re-usability at the mapping level.
So with a worklet we can add tasks (session, command, etc…) and logic just as we would in a workflow. The difference with the worklet is, we can add a worklet to any number of workflows we want. Again, this reuse is very helpful, saving us time by developing once and keeping logic in one place instead of many.
Walk through a simple Informatica mapping on how to load a Type 2 slowly changing dimension (SCD)?
A typical mapping of this nature is going to start with a source table or set of tables. These tables may reside in an ODS or set of historical table in a data warehouse, depending on a company’s architecture.
Before populating a dimension table with similar attributes, we need to determine if our new historical or ODS records contain data that differs from our target dimension. In our source qualifier we should use some custom SQL to limit the records that have been updated since our last run. The general idea is to then place lookup transformation(s) pointing to our target dimension table to retrieve attributes comparable to our source attributes. After getting values for all our attributes from our dimension table, we can use data concatenation to merge our lookup data with our original source data in an expression transformation. A great function to use at this point is MD5. This will allow us to compare our recently updated record attributes to our existing dimension record attributes. After doing this compare, we can filter out records that have not changed and insert newly changed records.
What does it mean to transform data?
Within PowerCenter we have many transformations that can modify record counts and change/add attribute values. Altering data in this fashion is what it means to transform data.
What is an Informatica mapping?
A mapping exists in Informatica PowerCenter Mapping Designer. A mapping consists of at least one source and one target. Many times a variety of transformation are used in a mapping between sources and targets to modify/enhance data according to business needs. So we might say a mapping logically defines the ETL process.
What SQL statement is comparable to a Union Transformation, UNION or UNION ALL?
UNION ALL
What is an Informatica PowerCenter Task?
An Informatica PowerCenter task allows us to build workflows and worklets. There are a variety of tasks that allow a developer to call code to be executed in a specific order. A series of session tasks for example can be used to initiate mappings to extract, transform, and load data.
Describe an Informatica PowerCenter Workflow?
A workflow is developed in the workflow manager. It is constructed through connecting tasks in a logical fashion to execute specific sets of code (mappings, scripts, etc…). A final workflow can therefore be started which will begin to run tasks within it, in the order defined.
List each PowerCenter client application and their basic purpose?
- Repository Manager : Manages repository connections, folders, objects, users, and groups
- Administration Console: Performs domain and repository service tasks such as create/configure, updgrade/delete, start/stop, and backup/restore nodes and repository services
- Designer: Creates ETL Mappings
- Workflow Manager : Creates and starts workflows/tasks
- Workflow Monitor : Monitors and controls workflows. Access to runtime metrics and session logs are also available.
Describe the purpose of a variable port within an expression transformation?
A variable port is designated by checking the V checkbox next to the port designated as a variable. A variable is set and persist for the entire data set passed through the expression transformation. The useful feature can be used in conjunction with conditional logic to assist with applying business logic of some kind. A major way I have personally used variable ports is to generate a new surrogate key as a new insert record is found.
What is the purpose of the INITCAP function?
The INITCAP function capitalizes the first letter in each word of a string and converts all other letters to lowercase.
EX: INITCAP(IN_DATA)
| IN_DATA | RETURN VALUE |
| informatica interview questions | Informatica Interview Questions |
When thinking performance, should active transformation be placed at the beginning or end of a mapping?
The beginning of the mapping, since it can reduce the number records passed to downstream transformations, reducing mapping overhead.
List the different join types within a Joiner transformation and describe each one?
- Normal Join – keeps only matching rows based on the condition
- Master Outer Join – keeps all rows from the detail and matching rows from master
- Detail Outer Join – keeps all rows from the master and matching rows from detail
- Full Outer Join – keeps all rows from both master and detail
What is a PowerCenter Shortcut and what are its benefits?
A shortcut is a dynamic link to an original Informatica PowerCenter object. If the original object is edited, all shortcuts inherit the changes.
What does the Revert to Saved feature do?
If unwanted changes are made to a source, target, transformation, mapplet, or mapping, these objects will be reverted to their previously saved version.
What does Auto-link by Name in Designer do?
It adds links between input and output ports across transformations with the same port names (case sensitive).
What does the Scale-to-Fit option do in Designer?
This option zooms in or out the designer workspace to allow for every object in a mapping to fit within the viewable workspace.
Can you copy Informatica objects between mappings in different folders?
No
When would you use a Joiner transformation rather than joining in a Source Qualifier?When you need to relate data from different databases or perhaps a source flat file.
Can you copy a reusable transformation as a non-reusable transformation into a mapping?
Yes, by depressing the Ctrl key while dragging.
What is the recommended order for Optimizing Informatica PowerCenter performance tuning bottlenecks?
TargetSource
Mapping
Transformation
Session
Grid Deployments
PowerCenter Components
System
Should we strive to create more or less re-usable transformations?
We should strive to create more re-usable transformations. Re-usability is a Velocity best practice. Benefits include decreased development time and mapping consistency.
Describe Data Concatenation?
We can bring together different pieces of the same record with data concatenation. This is only possible if combining branches of the same source pipeline where neither branch contains an active transformation.
What are the rules for a self-join in an Informatica mapping?
- We must place at least 1 transformation between the source qualifier and the joiner in at least 1 branch.
- Before joining, data must be pre-sorted by the join key.
- We must configure the joiner to accept sorted input.
What performs better, a single router transformation or multiple filter transformations? Why?
A single router transformation because a record is read into the transformation once instead of multiple reads of the same row data with a filter transformation.
What is an Informatica Task?
An Informatica task allows us to build PowerCenter workflows and worklets. There are a variety of tasks that allow a developer to call code to be executed in a specific order. A series of session tasks for example can be used to initiate mappings to extract, transform, and load data.
Describe an Informatica Workflow?
A workflow is developed in the workflow manager. It is constructed through connecting tasks in a logical fashion to execute specific sets of code (mappings, scripts, etc…). A final workflow can therefore be started which will begin to run tasks within it, in the order defined.
List the different join types within a Joiner transformation and describe each one?
- Normal Join – keeps only matching rows based on the condition
- Master Outer Join – keeps all rows from the detail and matching rows from master
- Detail Outer Join – keeps all rows from the master and matching rows from detail
- Full Outer Join – keeps all rows from both master and detail
Can you copy objects between mappings in different PowerCenter folders?
No
Can we make a reusable transformation non-reusable?
No, this process is non-reversible.
What does the debugger option Next Instance do?
Runs until it reaches the next transformation or satisfies a breakpoint.
What does the debugger option Step to Instance do?
Runs until it reaches a breakpoint or reaches a selected transformation.
Can the debugger help you determine why a mapping is invalid?
No, the designer output window will show you why a mapping is invalid, not the debugger.
What is the Velocity best practice prefix naming standard for a shortcut object and reusable transformation?
Reusable Transformation – re or RE
Shortcut Object – sc_ or SC_
What is persistent lookup cache and its advantages?
Persistent lookup cache is stored on the server hard drive available in the next session. Since the data is stored on the Informatica server, performance is increased the next time the lookup is called since a database query does not need to occur.
What happens to data overflow when not enough memory is specified in the index and data cache properties of lookup transformation?
It is written to hard disk.
What is the rule of thumb when deciding whether or not to cache a lookup or not?
Cache if the number of mapping records passing through the lookup is greater relative to the lookup table’s record number (and size).
What does the Find in Workspace feature allow us to do in Mapping Designer?
Perform string searches for the names of objects, tables, columns, or ports in the currently open mapping.
What does the View Object Dependencies feature in Designer allow us to do?
Developers can identify objects that may be affected by making changes to a mapping, mapplet, and its sources/targets.
What is the difference between PowerCenter variables and parameters?
Parameters in Informatica are constant values (datatypes strings, numbers, etc…). Variables on the other hand can be constant or change values within a single session run.
Informatica Interview Questions Part -1
Informatica Interview Questions Part -2
Informatica Interview Questions Part -3
Informatica Interview Questions Part -4
Informatica Interview Questions Part -5
Informatica Interview Questions Part -6
Informatica Interview Questions Part -7
Informatica Interview Questions Part -8
Informatica Interview Questions Part -9
Informatica Interview Questions Part -10
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Informatica Interview Questions Part - 5 "