What is Informatica and its Basic Interview Questions

Introduction to Informatica

Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Now a days Informatica is also being used as an Integration tool.
Informatica is an easy to use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for Data extraction transformation and load. These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background Informatica server takes care of fetching data from source, transforming it, & loading it to the target systems/databases.
Important Components of Informatica are:
Power Exchange
Power Centre
Power Centre Connect
Power Channel
Metadata Exchange
Power Analyser
Super Glue
Components of Informatica Client
Repository Manager : Manage the Informatica repository from the client machine
Designer: The Designer helps you create source definitions, target definitions, and
transformations to build a mapping
Workflow Manager : Allows user to execute a mapping created in the designer
Workflow Monitor : Tool that allows you to monitor workflows and tasks

                                                  Basic Interview Questions On Informatica:

What is Data Integration?
Data Integration is the process of combining data residing at different sources and providing the user with a unified view of these data.
Explain PowerCenter Repository?
Repository consist of database tables that store metadata. Metadata describes different types of objects , such as mappings or transformations , that you can create using PowerCenter Client tools. The interation service uses repository objects to extract , transform and load data. The repository also stores administrative information such as user names, passwords , permissions and previleges. When any task is performed through PowerCenter Client application such as creating users, analyzing sources , developing mapping or mapplets or creating workflows , Metadata is added to repository tables.
What is a Mapping?
A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.
What is a mapplet?
A mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.
While importing the relational source definition from database, what are the metadata of source you import?
Source name
Database location
Column names
Data types
Key constraints
How many ways you can update a relational source definition and what are they?
Two ways
1. Edit the definition
2. Re import the definition
Where should U place the flat file to import the flat file definition to the designer?
Place it in local folder
To provide support for Mainframes source data, which files r used as a source definitions?
COBOL files
Which transformation should u need while using the COBOL sources as source definitions?
Normalizer transformation is used to normalize the data. Since COBOL sources r often consists of Demoralized data.

Flat file header row, footer row and detail rows to multiple tables

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.
First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don’t check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).
Now pass the data from expression and aggregator to joiner transformation. In the joiner select the ports from aggregator as master and the ports from expression as details. Give the join condition on the count ports and select the join type as master outer join. Pass the joiner output to a router transformation and create two groups in the router. For the first group give the condtion as exp_count=1, which gives header row. For the second group give the condition as exp_count=agg_count, which gives the footer row. The default group will give the detail rows.

Normalizer Transformation Error – Informatica

Normalizer transformation is used to convert the data in multiple columns into different rows. Basically the normalizer transformation converts the denormalized data in a table in to a normalized table.
Normalizer Transformation Error
Getting the following Error for the Normalizer transformation in mapping when pivoting the columns in to Rows

TT_11054 Normalizer Transformation: Initialization Error: [Cannot match OFOSid with IFOTid.]

How to fix the Normalizer Transformation Error?
Solution:
Follow the below steps to avoid this error.

1. There should be no unconnected input ports to the Normalizer transformation.
2. If the Normalizer has an OCCURS in it, make sure number of input ports matches the number of OCCURS.

Creating a Non Reusable Object from Reusable Object

Q) How to create a non-reusable transformation or session or task from a reusable transformation or session or task?
I still remember my first project in which i created so many reusable transformations and developed a mapping. My project lead reviewed the code and told me that you created unnecessary reusable transformation change them to non reusable transformations. I created non reusable transformations and re-implemented the entire logic. It took almost one day for me to implement the code. Still so many new informatica developers will do the same mistake and re implement the entire logic.
I found an easy way to create a non-reusable transformation from a reusable transformation. Follow the below steps to create a non-reusable transformation or session or task from a reusable transformation or session or task in informatica is

1. Go to the Navigator which is on the left side.
2. Select the reusable transformation or session or task which you want to convert to non resuable with the mouse.
3. Drag the object (transformation/session/task) to the work-space and just before leaving the object on the work-space hold the ctrl key and then release the object.

Now you are done with creating a non-reusable transformation or session or task.

Informatica Performance Improvement Tips

Following standards/guidelines can improve the overall performance:

• Use Source Qualifier if the Source tables reside in the same schema.
• Make use of Source Qualifer  “Filter” Properties if the Source type is Relational.
• If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.
• Use flags as integer, as the integer comparison is faster than the string comparison.
• Use tables with lesser number of records as master table for joins.
• While reading from Flat files, define the appropriate data type instead of reading as String and converting.
• Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports.
• Suppress ORDER BY using the ‘–’ at the end of the query in Lookup Transformations.
• Minimize the number of Update strategies.
• Group by simple columns in transformations like Aggregate, Source Qualifier.
• Use Router transformation in place of multiple Filter transformations.
• Turn off the Verbose Logging while moving the workflows to Production environment.
• For large volume of data drop index before loading and recreate indexes after load.
• For large of volume of records Use Bulk load Increase the commit interval to a higher value large volume of data.
• Set ‘Commit on Target’ in the sessions.

Case Converter Transformation in informatica

The Case converter transformation is a passive transformation used to format the data to similar character formats. Case converter transformation is used to maintain data quality.
The predefined case conversion types are uppercase, lowercase, toggle case, title case and sentence case.
Reference tables can also be used to control the case conversion. Use the “Valid” column in the reference table to change the case of input strings. Use reference tables only when the case conversion type is “title case or sentence case”.
Case Strategy Properties:
You can create multiple case conversion strategies. Each strategy uses a single conversion type. Configure the following properties on the strategies view in the case converter transformation:
Reference Tables: used to apply the capitalization format specified by a reference table. Reference tables work only if the case conversion option is title case or sentence case. If a reference table match occurs at the start of a string, the next character in that string changes to uppercase. For example, if the input string is vieditor and the reference table has an entry for Vi, the output string is ViEditor.
Conversion Types: The conversion types are uppercase, lowercase, toggle case, title case and sentence case. The default conversion type is uppercase.
Leave uppercase words unchanged: Overrides the chosen capitalization for uppercase strings.
Delimiters: Specifies how capitalization functions work for title case conversion. For example, choose a colon as a delimiter to transform “james:bond” to “James:Bond”. The default delimiter is the space character.

SQL Transformation in Informatica

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.
The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.
The following SQL statements can be used in the SQL transformation.

• Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
• DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
• DATA Retrieval Statement (SELECT)
• DATA Control Language Statements (GRANT, REVOKE)
• Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following options can be used to configure an SQL transformation

• Mode: SQL transformation runs either in script mode or query mode.
• Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.
• Database Type: The type of database that the SQL transformation connects to.
• Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.
You can create the following types of SQL queries
Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.
Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

SQL Transformation Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products
PRODUCT 
-------
SAMSUNG
LG
IPhone

Table Name: Sales
PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
LG      3        80
IPhone  5        200
SAMSUNG 5        500

Create a mapping to join the products and sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
IPhone  5        200
LG      3        80

Solution:
Just follow the below steps for creating the SQL transformation to solve the example

• Create a new mapping, drag the products source definition to the mapping.
• Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.
• Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

• Edit the sql transformation, go to the “SQL Ports” tab and add the input and output ports as shown in the below image. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

• In the same “SQL Ports” Tab, go to the SQL query and enter the below sql in the SQL editor.

select product, quantity, price from sales where product = ?product?

• Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

• Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG'
select product, quantity, price from sales where product ='LG'
select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.
SQL Transformation Example Using Full Dynamic query
Q2) I have the below source table which contains the below data.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product = 'LG'
Delete FROM products WHERE Product = 'LG'

Solution:
Just follow the same steps for creating the sql transformation in the example 1.

• Now go to the “SQL Ports” tab of SQL transformation and create the input port as “Query_Port”. Connect this input port to the Source Qualifier Transformation.
• In the “SQL Ports” tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.
• As we don’t need any output, just connect the SQLError port to the target.
• Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query
Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab
Tab_Names
----------
sales
products

Solution:
Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

Informatica Scenario Based Interview Questions with Answers

Informatica Scenarios

I have listed the following informatica scenarios which are frequently asked in the informatica interviews. These informatica scenario interview questions helps you a lot in gaining confidence in interviews.
1. How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count
2. Design a mapping to load the first 3 rows from a flat file into a target?
Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3
3. Design a mapping to load the last 3 rows from a flat file into a target?
Solution:
Consider the source has the following data.
col
a
b
c
d
e
Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.
In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count
The output of aggregator transformation will be
O_total_records, O_dummy
5, 1
Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)
The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5
Step4: Now pass the ouput of joiner transformation to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
In the filter transformation, the filter condition will be
O_total_records – O_count <=2
The output of filter transformation will be
col o_count, o_total_records
c, 3, 5
d, 4, 5
e, 5, 5
4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?
Solution:
This is similar to the above problem; the first 3 steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation create two output groups.
In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.
5. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B
Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D
The second target should contain the following output
B
B
B
C
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1
Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.
Q2. Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_curr_product, and assign product port to it. Then create a V_count port and in the expression editor write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable port V_prev_port and assign product port to it. Now create an output port O_count port and assign V_count port to it.
In the expression transformation, the ports are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count
The output of expression transformation will be
Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1
Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table