Informatica Interview Questions and Answers : Top Informatica Interview Questions 2018, Informatica Interview Questions on Transformations, Data Warehouse Interview Questions, Informatica ETL Interview Questions with Answers that are asked in various companies available for free at tutorialkart.com.

Informatica Interview Questions and Answers

Q1 Why we use lookup Transformation

Lookup Transformation can access data from relational tables that are not sources in mapping. Using Lookup Transformation, we can perform the following tasks:

To query the largest amounts of data to improve overall performance. By doing that we can reduce the number of lookups on the same table.

  1. To get a related value, updating slowly changing dimensions and calculating expressions.
  2. Lookup Transformation helps in terms of performance tuning of the mapping.

Q2 What are the meta data of source you import while importing the relational source definition from database

  • Source name.
  • Database location.
  • Column names.
  • Datatypes.
  • Key Constraints.

Q3 How many ways you can update a relational source definition

Depending upon the changes, we can update a relational source definition in 2 ways :

  1. Minor changes to the source definition :  Edit the source definition (in source analyzer) then the Designer propagates the changes to all mappings using that source.
  2. Major changes to the source changes definition : Re-import the source and giving replace option while re-importing will reflect the changes in all the mappings where the source is used.

Q4 Where should you place the flat file to import the flat file definition to the designer

We can place source file any where in network, but it will consume more time to fetch data from source file, but if the source file is present on server srcfile then it will fetch data from source upto 25 times faster than previous.

Q5 Which source definition files are needed to provide support for Mainframes

Using COBOL files.

Q6 Which transformation you need while using the COBOL sources as source definitions

Normalizer Transformation.

Q7 How can we create or import flat file definition in Informatica PowerCenter designer

We can not create or import flat file definition in to Informatica PowerCenter designer directly. Follow the steps given below

  • Analyse the file in source analyzer, then drag it into the warehouse designer.
  • Now designer creates a relational target definition not a file definition. If u want to load to a file, configure the session to write to a flat file.
  • Now Informatica server runs the session, it creates and loads the flat file.

Q8 What is the Mapplet

Mapplet is set of Transformation where the logic can be reusable.

Q9 What is Transformation

Transformation is a repository object that generated, modifies and passed data.

Q10 Different designer tools used for creating Transformation in Informatica

  • Mapping designer.
  • Transformation developer.
  • Mapplet designer.

Q11 What are the active and Passive Transformations

Active Transformation : It changes the number of rows that passes through it.Example : Filter transformation removes rows that do not meet the filter condition.

Passive Transformation : It does not changes the number of rows that passes through it.Example : Expression transformation performs calculations on data and passes all rows through the transformation.

Q12 What are connected and unconnected transformations

Connected Transformation : It participates in mapping data flow and can receive multiple inputs and provides multiple outputs.

Unconnected Transformation : This is not connected with other transformations in the mapping, It receives multiple inputs and provides single output.

Q13 How many ways you can create Ports in Informatica

  • By dragging the ports from another transformation .
  • By clicking the add button on the ports tab.

Q14 What are the reusable Transformations

A Transformations that can be use in multiple transformations is known as reusable transformation. To create, use transformation developer (or) create normal transformation and change it to reusable transformation by changing properties.

Q15 What are the unsupported repository objects for a mapplet

  • Normalizer Transformation.
  • Cobol sources.
  • XML source Qualifier transformation.
  • XML sources.
  • Target definitions.
  • Pre- and Post-session stored procedures.
  • Other Mapplets.
  • PowerMart 3.5 style Lookup functions.
  • Non reusable sequence generator.

Q16 What are the mapping parameters and mapping variables

Mapping parameters : It represents a value that can be changed during the mapping run and mapping reusability can be done using mapping parameters.

Mapping variables : It represents a value that can be changed during the mapping run and used in incremental loading process.

Q17 Can you use mapping parameters or variables created in one mapping into another mapping

We can use mapping parameters or variables in any transformations of the same mapping or mapplet in which we you have created mapping parameters or variables.

Q18 Can you use mapping parameters or variables created in one mapping into any other reusable transformation

Yes, reusable transformation does not contain any mapplet or mapping.

Q19 How can we improve session performance in aggregator transformation

  • Using sorted inputs to decrease the use of aggregator caches.
  • Limit connected input/output or output ports.
  • Filter before aggregating.

Q20 What is aggregator cache in aggregator transformation

Aggregator cache contains data values while aggregator calculations are performed. Aggregate cache is made up of index cache and data cache. Index cache contains group values and data cache consists of row values.

Q21 What is ODS

Operating data systems

Q22 Describe two levels in which update strategy transformation sets

Within a session: When you configure a session, you can instruct the Informatica Server to either treat all records in the same way , or use instructions coded into the session mapping to flag records for different database operations. With in a mapping: Within a mapping, you use the Update Strategy transformation to flag records for insert, delete, update or reject.

Q23 What are the different Lookup methods used in Informatica

 Connected transformation :It is connected to other transformations or directly to target table in the mapping.

Unconnected transformation : It is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.

Connected lookup : It receives input values directly from mapping pipeline and returns multiple columns from the same row.

UnConnected lookup : It receives values from: Lookup expression from another transformation, it has only one return port and returns one column from each row.

Q24 What is multi dimensional analysis

It is a data analysis process that groups data into two or more categories like data dimensions and measurements.

Q25 Performance wise which is better look up joiner Transformation

Lookup Transformation give better performance than Joiner Transformation.

Q26 How to enter same record twice in target table give me syntax

To insert a same record in to the target table twice, use the fill method of data adapter twice and update the dataset. Add the following lines of code

sqlconnection cn = new sqlconnection("ur connection string");
Sqldataadapter da= new sqldataadapter("select * from sometable",cn);
sqlcommandbuilder cmb = new sqlcommandbuilder(da);
dataset ds= new dataset();
da.fill(ds,"sometable");
ds.tables[0].constraints.add("usr_pk",ds.tables[0].columns[0],true);
datarow row;
row=ds.tables[0].newrow();
for(int i=o;i<2;i++)
{i++;
row[0]=value;
ds.tables[0].rows.add(row);
da.update(ds.tables[0]);

Q27 How to get particular record from the table in informatica

select * from <tablename> where <coloumn-name1>='<value>'and 
<coloumn-name2>='<value>'.........
(OR)
select name from tablename where name like 'A%';

Q28 how to get the records starting with particular letter like A in informatica

select * from emp where ename like 'A%'

Q29 How can you improve the performance of Aggregate transformation

  • Use sorted input option to decrease the use of aggregator cache.
  • Use filter transformation before aggregator transformation to reduce unnecessary aggregation.
  • Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.

Q30 why did u use update strategy in your application

To update the tables and generate the correct data

<!-- When to check for updates -->
<subscription>
<update>
<expiration maximumAge="6" unit="hours" />
</update>
</subscription>

Q31 In update strategy target table or flat file which gives more performance why

Flat file give better performance because retrieval of data from flat file is fast than relation database

Q32 How to load time dimension

We can use SCD Type 1/2/3 to load any Dimensions based on the requirement.

Q33 After dragging the ports of three sourcessql server, oracle, informix to a single source qualifier, can u map these three ports directly to target

No,For this you need to use 2 joiner transformations and the resulted columns can be connected to the target.

Q33 What are the modules in Power Mart

  1. PowerMart Designer.
  2. Server .
  3. Server Manager.
  4. Repository.
  5. Repository Manager

Q33 What is SCD1 , SCD2 , SCD3

SCD1: It never maintains history in the target table. It keeps the most recent updated record only in the data base. SCD2: It maintains full history in the target. It maintains history by inserting the new record and updating for each change. SCD3: It keeps the both current and previous values only in the target.

Q34 What are additive, semi-additive and non-additive facts

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.

Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table but not the others.

 Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Q35 What are slowly changing dimensions

If the data in the dimension table happen to change very rarely then it is called as slowly changing dimension.

Q36 Techniques of Error Handling

Ignore, Reject bad records to a flat file, loading the records and reviewing them (default values) Rejection of records either at the database due to constraint key violation or the informatica server when writing data into target table.

Q37 What are the various methods of getting incremental records or delta records from the source systems

Method #1: If there is a column in the source which identifies the record inserted date. Then it will be easy to put a filter condition in the source qualifier.

Method #2: If there is no record in the source to identify the record inserted date. Then we need to do a target lookup based on the primary key and determine the new record and then insert.

Q38 Latest version of PowerCenter Power Mart

Informatica PowerCenter 10.0.1

Q39 What is the difference between PowerCenter Power Mart

PowerCenter supports ERP (enterprise resource planning) where Power mart does not support ERP.