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.
- To get a related value, updating slowly changing dimensions and calculating expressions.
- 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 :
- 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.
- 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 sources(sql 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?
- PowerMart Designer.
- Server .
- Server Manager.
- Repository.
- 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.