Pages

Monday, March 18, 2013

Informatica Interview Questions and answers || Informatica Real time interview questions and answers


1. Can you copy the session to a different folder or repository?
Yes. By using copy session wizard, you can copy a session in a different folder or repository.

2. What is a command that used to run a batch?
pmcmd is used to start a batch.

3. What are the types of mapping in Getting Started Wizard?
  • Simple Pass through mapping
  • Slowly growing target mapping

4. What are the different types of Type2 slowly changing dimensions?

There are three types of slowly changing dimensions
  • SCD with versioning
  • SCD with flags
  • SCD with Date

5. What are the different threads in DTM process?
  • Master thread
  • Mapping thread
  • Reader thread
  • Writer thread
  • Pre and post session threads

6. What are the active and passive transformations?

An active transformation can change the number of rows that pass through it.
A passive transformation does not change the number of rows that pass through it.

7. What are the scheduling options to run a session?
Different options of scheduling are
  • Run only on demand: Informatica server runs the session only when user starts session explicitly
  • Run once: Informatica server runs the session only once at a specified date and time.
  • Run every: Informatica server runs the session at regular intervals as you configured.
  • Customized repeat: Informatica server runs the session at the date and time specified in the repeat dialog box.

8. How can we store previous session logs?

Just run the session in time stamp mode then automatically session log will not overwrite current session log.

9. What is the difference between Mapping and Mapplet?
  • Mapping is the collection of Source Definition, Target Definition, Transformation(s) and/or Mapplet.
  • Mapplet is a set of reusable transformations for applying same business logic.

10. What is the use of Repository Manager?

Repository Manager is used to manage folders, view Source/Target dependencies, Mapping Dependencies, adding and removing repositories, Versioning etc.

11. What are the various Tasks in Informatica Workflow Manager?

The various Tasks in Informatica are
  • Assignment Task
  • Command Task
  • Control Task
  • Decision Task
  • E-mail Task
  • Event Raise Task
  • Event Wait Task
  • Session Task
  • Timer Task
  • Link Task


12. What is the use of Source Analyzer?

Source Analyzer is used to create source definition.

13. What is the use of Target Designer?

Target Designer is used to create Target Definition.

14. What is Target Designer called prior to Informatica 8.6?

Warehouse Designer

15. Can we use Reusable Sequence Generator transformation in Mapplet?

No

16. Why Cache? Explain with an example?

If there are x lot number of records with y number of columns from source data and we need to extract z number of columns only (very less) then the cache stores those columns for respective records in the $PMCACHEDIR of Informatica Server so that we don’t need to extract each record from database and load into Informatica. Doing this increases the performance of the system. 

17. What is the use of Shared Object?

It is easier to explain this using an example.
Suppose there is an object which is used by many users in the company.

If the object undergoes a change, this change has to be updated to each and every user. Instead, if the object is made as shared, then the update has to be done to the object and all other users get the update. 

18. What is Dual table?

Dual table is a table that is created by oracle along with data dictionary. It gives output of exactly one column name dummy and one record ‘x’.

Select * from Dual;
-----------
DUMMY
X

19. Can we install Power Center Informatica 8.X version on Windows 7?

No we can’t install Informatica on Power Center. We can Install Informatica 9.x on Windows 7.

20. What are the types of variables in Informatica?

There are three types of variables in Informatica
  • Predefined variable represented by $
  • User defined variable represented with $$
  • System variable denoted by $$$

21. Difference between Informatica normal join and Oracle Equi join?

Equi join in Oracle is performed on oracle sources (relational sources) while Informatica Equi joins can be performed on non-relational sources too (oracle and flat file etc). 

22. What is degenerate dimension?

Dimension which has no dimension table of its own and is derived from the fact table. 

23. What is requirements gathering?

It is carried out by Business Analyst. It is nothing but interacting with end users and getting to know what his requirements are. Based on his requirements, the rest of the phases Analysis, Design, Implementation and Testing and finally Maintenance are carried on.

24. What is Junk dimension?

The dimension that is formed by lumping of smaller dimensions is called Junk dimension. 

25. What is Staging Area?

Staging Area is indeed a database where data from different source systems are brought together and this database acts as an input to Data Cleansing.

26. What are the types of joins in Informatica and in Oracle?

There are four types of joins in oracle
  • equi join
  • non equi join
  • self join
  • outer join

27. Joins in informatica
  • master join (right outer join)
  • detailed join (left outer join)
  • normal join

28. What is the file extension or format of files for the Informatica Objects like sessions, mappings etc. in Repository?

The format of files for Informatica Objects in Repository is XML

Where can we find Versioning in Informatica? What happens if Versioning is turned off?
In Informatica, we can find Versioning in Repository Manager. If Versioning is turned off, we will not be able to track the changes for the respective Sessions/Mappings/Workflows.

29. What is tracing level? What are the types of tracing level?

Tracing level is the amount of information that Informatica server writes into a log file.
Types of tracing level
  • Normal
  • Terse
  • Verbose init
  • Verbose data
30. In joiner transformation, we take the table with lesser number of rows as master while the more number of rows as detailed. Why?

In joiner, each and every row of the master is compared with every row of the detailed and so, the less number of rows in master, the less is the number of iterations and so better is the performance of the system.

31. What are all the databases the Informatica server on windows can connect to?

Informatica server on windows can connect to
  • SQL server database
  • Oracle
  • Sybase
  • Teradata
  • MS Access
  • MS Excel
  • Informix
  • DB2

32. What are the databases the Informatica server on UNIX can connect to?

Informatica on UNIX can connect to
  • Sybase
  • Teradata
  • Informix
  • DB2
  • Oracle

33. What is an overview window?

It is a window where you can see all the transformations in a mapping.

34. In how many ways we can update a source definition?

Two ways
  • We can reimport the source definition
  • We can edit the source definition

35. What is mapping?

Mapping is nothing but data flow between source and target.

36. What is a session?

Session is a set of instructions that tells the Informatica server when to and how to move the data from source to targets.

37. What are the different things we can do using PMCMD command?

We can START, STOP and ABORT session using PMCMD command.

38. What is the use of bitmap indexes?

Bitmap indexes are used to join large fact tables to smaller dimension tables.

39. How can we delete duplicate rows from flat files?

We can make use of sorter transformation and select distinct option.

40. If a session fails after loading 10000 records into the target how can we start loading into the target from the 10001th record?

We can run the session with the recovery strategy mode.

41. What is the limit of joiner transformations?

We cannot use sequence generator or update strategy transformations before or after joiner transformations.

42. How does server recognize the source and target databases?

By using ODBC if they are relational, FTP if they are flat files.

43. What is rank index in a group?

Power Center Designer automatically creates a RANK INDEX port while using Rank transformation. The purpose of this RANK INDEX port is to store the ranking for the column(s) we are interested in. 

44. What are the constants or flags for each database operation and their numeric equivalent in Update Strategy?
  • Insert DD_INSERT 0
  • Update DD_UPDATE 1
  • Delete DD_DELETE 2
  • Reject DD_REJECT 3

45. Can you generate reports using Informatica?

No, we cannot generate reports using Informatica, it is just an ETL tool but we can generate metadata reports.
(SCD-2 by RamaSurReddy) Yes, Using Informatica Data Analyzer Tool we can generate reports.

46. Can you start batches within a batch?
No, you can’t

47. What are the types of groups in Router Transformation?
There are three types of groups in Router Transformation namely
  • Input group
  • Otput group
  • Default group

48. What are batches? What are the types of batches?

Batches provide a way to group sessions for either sequential or parallel execution by Informatica server.
  • Concurrent batches – which run at the same time
  • Sequential batches – which run one after the other

49. What is Power Center Repository?

Power Center Repository allows you to share metadata among different repositories and to create a datamart domain.

50. What r the types of metadata that stores in repository?

  • Source definition
  • Target definition
  • Mappings
  • Mapplet
  • Transformations

51. Differences between dynamic cache and static cache

In case of dynamic cache, if we want to insert a new row, then it will first looks in the lookup cache and if the row is not present in the cache, it inserts the row into the cache as well as target but in case of static cache, its stores only into target table and not in cache.

52. What is the use of source qualifier?
Source qualifier is used to convert different data types to Informatica compatible data types.

53. What is page code compatibility?
It is nothing but compatibility of code for maintaining data accuracy. It comes into picture when data is in different languages.

54. What are Synonyms?
Synonyms are alternative names for database objects such as tables, views, stored procedures etc.
Syntax
Create [replace] synonym for [schema.]object_name

55. Types of Lookup Cache?
Static cache
Dynamic cache
Persistent cache
Recache from database
Shared cache

56. What are various stages of SDLC?
Requirements Gathering, Analysis, Design, Implementation and Testing and Maintenance.

57.What is Star Schema?
Star Schema is a simplest form of schema which has one fact table and at least one dimension table. The dimensions here are denormalized.

58.What is Fact table?
It is a centralized table in Star Schema. Fact table has two types of columns. First type is measures and second type is the foreign keys for the dimension tables.

59.What is SnowFlake Schema?
In SnowFlake Schema, the dimensions are further divided into sub dimensions. The dimensions here are normalized.

60. What is Dimension table? 
A dimension table is one that describes the business entities of an enterprise.

61. What is operational data store (ODS)?
Operational data store is defined to be structure that is
  • Subject-oriented
  • Integrated
  • Volatile
  • and current data that is a day or perhaps a month old.

62.What is Logical Data Modeling?
Logical Data Modeling is a type of data modeling which represents business requirements of an organization.

63. What is Physical Data Modeling?
Physical Data Modeling is a type of data modeling which includes all required tables, columns, and relationships for the physical implementation of a database.

64. Difference between mapplet and reusable transformation?
Using mapplet we can make set of transformations reusable whereas in reusable transformation, we can make only one transformation as reusable.

65. what is Data cleansing?
It is the process of converting data from different format of files or databases to single required format.

66. What are the types of files created by Informatica server during the session running?
Types of files created are
  • Cache file
  • Session log file
  • Informatica server log file
  • Output file
  • Reject file

67. What are the types of repositories created by Informatica repository manager?
Four types of repositories are created using repository manager
  • Standalone repository
  • Global repository
  • Local repository
  • Versioned repository

68. What are the two types of processes that run the session?
The two types of processes that run the session are
  • Load Manager
  • DTM processes (Data Transformation Manager)

69. What is the difference between Union and Lookup transformation?
Union transformation is Active while Lookup transformation is Passive.
For Union transformation, the source tables or data should have similar structure while its not the case with the Lookup transformation.
Lookup transformation can run on source or target tables while Union tables work only on source tables.

70. What is throughput in Informatica?
Throughput is nothing but the rate at which Informatica server reads the data from sources and writes them successfully to the target.

71. Where can we find the throughput option in Informatica?
We can view this in workflow monitor
In workflow monitor, right click on session, then click on Get Run Properties and under Source/Target statistics we can find throughput option

72. What is code page?
Code page consists of encoding to specify characters in set of one or more languages and are selected based on source language.

73. What are types of loading in Informatica?
The two types of loading available in Informatica are
  • Bulk Loading
  • Normal Loading

74. What is the difference between local index and global index?
Global index is nothing but a single index covering all partitions whereas local index has separate index for each partition.

75.What is Complex Mapping?
Complex Mapping will have the following features 
  • Dificult requirements
  • Many number of transformations
  • Complex business logic

76. How many ways you can add ports?
Two ways
  • From other transformation
  • Click on add port button

77. How many number of sessions can you can you group in batches?
Any number of sessions but the lesser the number of sessions in a batch, the easier the migration.

78. What is the difference between Aggregator transformation and Expression Transformation?
Aggregator transformation use aggregator functions and performs calculation on entire group whereas in Expression transformation performs calculation on row by row basis.

79. What is the difference between Filter transformation and Router transformation?
Filter transformation drops the data that do not meet the condition whereas Router transformation captures the data even though the condition is not met and saves it in Default output group.

80. Filter transformation works on single condition only while Router transformation works on multiple conditions as well.

Filter transformation gives only one output. Router transformation can give more than one output.

81. What is the similarity between Router and Filter transformations?
Router transformation and Filter transformations are used to filter the data based on condition.
  • Both Filter and Router transformation are Active transformations.
  • Both Filter and Router transformation are connected.

82. What is the difference between Source Qualifier transformation and Joiner transformation?
  • Source Qualifier transformation is used to join the data from homogeneous sources while Joiner transformation is used to join data from heterogeneous sources as well as homogenous sources from different schemas.
  • We need matching keys to join two relational sources in Source Qualifier transformation and is not the case with joiner transformation.

83. What is PARAM file?
Param file is an ordinary text file where we can define the value for the parameter which is defined in session.
These parameter files are defined in session properties.

84. Which transformation should we use to normalize the COBOL and relational sources?

We need to make use of Normalizer Transformation

85. What are the various transformations which we cannot use in Mapplet?
Transformations which we cannot use in Mapplet are
  • Normalizer Transformation
  • XML Source Qualifier Transformation
  • Target Definition
  • Cobol Sources
  • Pre and Post Session Stored Procedures

86. What is the dfference between Joiner transformation and Lookup transformation?
  • Joiner is active transformation while Lookup is passive transformation.
  • Joiner works on source data only while Lookup works on source as well as target data.
  • Joiner transformation supports equi joins only while Lookup supports equi join as well as non equi joins.
  • Joiner transformation is connected while Lookup transformation can be either connected or unconnected.

87. What is the use of Lookup transformation?
Lookup transformation is used to check whether matching record exists in the target table and if the matching record doesn’t exist, it inserts accordingly.

88. How can we improve the session performance in Aggregator transformation?
We can increase the session performance by sending the sorted input to the aggregator transformation.

89. What is aggregated cache?
Aggregator cache is a temporary location which stores the input data values while the aggregation calculation is being carried out.

90. What is Data Driven?
Informatica Server follows the instructions coded into update strategy within session mapping which determine how to flag the records for Insert, Update, Delete and Reject.

91. Difference between connected Lookup and Unconnected Lookup?
  • Connected lookup receives input directly from mapping pipeline whereas unconnected lookup receives input from :LKP expression of another transformation.
  • Connected lookup returns more than one column in a row where as unconnected
  • lookup returns only one column in each row.
  • Connected lookup Supports user-defined values while unconnected doesn’t.
  • Connected lookup is not reusable whereas unconnected is.
  • Performance of connected lookup is lower compared to unconnected lookup.

92. What is a Mapplet?
Mapplet is an object which consists of set of reusable transformations which can be used in different mappings.

93. Can Lookup be done on flat files?
Yes

94. What is the transformation used in loading 4 flat files of similar structure to a single target?
We can make use of Union transformation

95. Difference between direct and indirect loading options in sessions?
Direct loading can be used on single transformations while indirect loading can be used on multiple transformations

96. In direct loading, we can perform recovery process while in indirect loading, we cannot perform recovery process.

97. What are the various techniques for implementing a dimensional model?
  • Star schema
  • Snowflake schema

98. What are the types of dimensions?
There are three types of dimensions
  • Slowly changing dimensions
  • Confirmed dimensions
  • Casual dimensions

99. What is SQL override?
It is nothing but overriding SQL in source qualifier or lookup for additional logic.

100. What are the default values for variables?

The default variables for
  • Number=0
  • Variable=NULL
  • Date=1/1/1753

101. What are the types of data movement in Informatica server?
There are two types of modes in Informatica
  • Ascii mode
  • Unicode mode

102. What happens to the discarded records in Filter transformation?
Discarded rows do not appear in session logs and reject files.

103. How to increase the performance of session in using Filter transformation?
To increase the performance of session, we need to use Filter transformation as close as possible to the sources in the mapping.

104. In case of flat files (which comes through FTP) haven’t arrived what happens?
The session is going to fail because of fatal error

105. Why do we need to reinitialise aggregate cache?
We need to reinitialise the aggregate cache only to remove the previous data present in the aggrgator cache and to be used by the new source.

106. What are the various mapping objects available in Informatica?
Mapping objects that are aviailable in Informatica are 
  • Source Definition
  • Target Definition
  • Link
  • Transformation
  • Mapplet

107. What is the default source option for Update Strategy?
Data Driven

108. What is the use of Update Strategy transformation?
Update Strategy is used to perform DML operations like Insert, Update, Delete and Reject on already populated targets.

109. What is the default join that the Joiner transformation provides?
Normal join

110. What are the types of Lookup Cache?
  • Static cache
  • Dynamic cache
  • Persistent cache
  • Recache from database
  • Shared cache

111. What are the differences between unique key and primary key?
Primary key cannot contain null value whereas unique key can contain one and only one null value

112. In case of sql server, with default options, primary key is created as a clustered index while unique key is created as a non clustered index

A unique key is similar to primary key but we can have more than one unique key per table

What are the types of joins in Informatica and in Oracle?
There are four types of joins in oracle
equi join
non equi join
self join
outer join
Joins in informatica
master join (right outer join)
detailed join (left outer join)
normal join

What is the difference between RowId and RowNum?
RowId is the physical address of a row. If we know the RowId, we can read entire row.
RowNum is the temporary number allocated during query execution

What are pseudo columns? What are the various types of pseudo columns?
Pseudo columns are columns which are not in the table but they can be used in sequel queries as if they are part of the table

RowNum
RowId
Sysdate
User
Currval
Nextval

What are the various types of Statements in Oracle?
The various Statements that are available in Oracle are
Data Manipulation Language (DML) Statements
Data Definition Language (DDL) Statements
Transaction Control (TC) Statements
Session Control (SC) Statements
System Control (SC) Statements

What are the various types of DML Statements?
Select
Update
Delete
Insert
Merge

What are various AGGREGATOR FUNCTIONS?
SUM
AVG
MIN
MAX
COUNT
STDDEV
VARIANCE
FIRST
LAST

What are the types of joins in oracle?
Equijoins or simple joins or inner joins
Non-equijoin
Outer join
Self join

Difference between char and varchar?
Char is a fixed length data type where as varchar is a variable length data type. So in case of char, though we entered less size for a column, it is going to allocate max size and in case of varchar, it is not going to allocate the maximum size.

Difference between varchar and varchar2
Varchar and varchar2 are variable length datatypes. Varchar datatype has a size of 2000 bytes whereas varchar2 has a size of 4000 bytes.
Varchar is in ascii whereas varchar2 is in unicode.

What is Normalisation? Define 1NF, 2NF and 3NF.
It is the process of reducing the complex data structure into a simpler one by removing the redundancy.
First normal form: First normal form state that each field is atomic.
Second normal form: Second normal form states that data redundancy can be reduced if all the non key attributes which are dependent on one of the primary keys of a composite primary key are put to a separate table along with depended primary key . This should also satisfy the 1nf. For example, if a table has attributes like partid, city, state and country and partid, country for a composite primary key and city, state depend on country then the table is sepeated as two different tables as attributes having partid, country and country, state, city.
Third normal form: Third normal form states that if a dependency exist between non key attributes, then these attributes are isolated to a different table. This should also satisfy the 1nf + 2nf.

2 comments:

  1. thank you for your wonderful information on informatica keep sharing thank you
    visit our site to learn

    learn Advanced informatica!

    ReplyDelete
  2. It was good collection. The Author did a great work. Keep sharing such a wonderful information. I am a Business Analyst. Suppose if your looking for more Informatica interview Q/A’s, I will share you a link related to Informatica : Informatica interview Q/A’s just have looks. I hope it will help who are looking for Informatica interview Q/A’s.

    ReplyDelete