informatica cert qa set1

7/29/2019 Informatica Cert QA Set1

1/25

I have tried to answers the questions . Please check and let me know the correct answers in case of

wrong answers.

1=which of the following type of sources are supported for sorter transformation?A=relational database & flat files

B= relational database & COBOL files

C= relational database & COBOL files & FLAT FILES

D= relational database ONLY

ANS:A

2=with which of the following the master gateway is associated?

A=Node if you do not have high availability and domain if you do

B=node

C=domainD=Repository

ANS :C

3=which of the following is related to character sets during the repository configuration?

A=Codepage

B=none of the listed options

C=Char type

D=Code char

ANS:A

4=In which of the following tab of Domain in Administration console you can view or modify the user

permission?

A=Permission

B=user activity monitoring

C=manage user

D=resources

ANS:A

5=the integration services holds two different values for a workflow variable what are they?

A=start value and end value

B=current value and end value

C=start value and current value

D=none

ANS:C

6=what are the available windows in workflow manager?

A= navigator, workspace, overviewB=workspace, output


2/25

C=navigator, workspace, output, overview

D=navigator, workspace, output

ANS:C

7=which of the following components integration service does not use to move data from source to

target?

A=data transformation manager(DTM)process

B=service manager

C=load balancer

D= integration service process

ANS:C

8=we can run session task .?A=concurrently

B=both concurrently & sequentially

C=sequentially

D=one sessin at a time

ANS:B

9=which of the below statement is not true about reusable transformation?

A=when you add a reusable transformation to a mapping you add an actual transformation

B=you can create reusable transformation in transformation developer or mapping developer

C=reusable transformation can be used in multiple mappings

D=the transformation marked as reusable in mapping designer cannot be reversed

ANS:A

10=how can you identify source bottleneck in case of relational source?

A=use a filter transformation

B=all of the listed options

C=use a read test mapping

D=use a database query

ANS:B

11=where can you create workflow task?

A=in task developer

B=in task developer or workflow designer

C= in task developer or workflow designer, or the worklet designerD= in task developer or workflow designer, or the worklet designer or session wizard


3/25

ANS:B

12=what power center does while validating an expression which contain an user defined function?

A=a power center does not validate the user-defined function it just validate expression

B=a power center validates fails with validation error as it contain UDF

C=none

D=power center validates both the user-defined function and expression

ANS:A (NOT SURE)

13=you can configure expression syntax for a user-defined function by using the argument, as well as

custom function TRUE/FALSE

14=parameter file contain which of the following parameter and variables?

1=service processvariable

2=workflow variable3=worklet variable

4=workflow parameter

A=1,2 &4

B=1,2,3 & 4

C=1,2 & 3

D=1,2 &4

ANS:C

15=which of the following objects are required to create grid?

A=nodes, workflow & session

B=domains

C=nodes and domain

D=nodes

ANS:C

16=integration services rolls back transaction under which circumstances?

A=invalid transaction

B=open transaction

C=roll back or session failure

D= closed transaction

ANS:C

The Integrat ion Service rol ls back transact ions in the fo l lowing circums tances:

Rol lback evaluat ion. The transact ion co ntro l expression returns a rol lback v alue.

Open transact ion. You c hoose to rol l back at the end of f i le.


4/25

Rol l back on error . You choose to rol l back comm it transact ions i f the Integration Service

encou nters a non-fatal error.

Rol l back on fai led comm it . I f any target connect ion grou p in a transact ion contro l uni t fa i ls to

comm it , the Integration Service rol ls back al l uncomm it ted data to the last successfu l commit

point .

17=what can be done to improve integration services performation?

A=cache power center metadata for the repository services

B=use native drivers instead of ODBC drivers for the integration services

C=all of the listed options

D=run integration service with high availability

ANS:C

18=which of the following target definition does power center 8 support?

A=relational. Flat file

B= relational. Flat file, XML files

C=relational only

D= relational. Flat file, XML files, COBOL files

ANS:B

19=which of the following setting you can configure while configuring the load balancing for a node?

A=resource

B=service level

C=dispatch mode

D=you cannot configure load balancing for node

ANS:C

20=which of the following transformation can be placed b/w the sort origin and the joiner

transformation?

A=rank

B=filter

C=union

D=normalize

ANS:D

21=folder and session name are case sensitive ,mapping parameter and variable name are not case

sensitive in parameter file TRUE/FALSE?

Session parameter names are not case sensit ive.

Mapping p arameter and v ariable names are not cas e sensit ive TRUE.


5/25

22=What are the different states of debugger?

A=initializing running and paused

B=running, paused & aborted

C=starting, running & stopped

D=running, aborted & stopped

23=what happen when you use $target in a stored procedure transformation and the pipeline contain

multiple relational target?

A=none of the listed option

B=the DB server uses the database connections you specify for the target

C=the informatica server uses the database connection you specify the target

D= the session fails

ANS:D

24=how many output group can you create in a java transformation?

A=equal to no of output ports

B=not limited

C=equal to no of input ports

D=1

ANS:D

25=what type of cache he give performance benefit in Lookup?

A=dynamic

B=shared & persistence

C=cache dosent give performance benefit

D=static

ANS:B

26=which of the following is true about enabling a high precision data handling?

1=use the decimal datatype with a precision of 16 to 28 in the mapping

2=select enable high precission in the session properties

3=power center will automatically process the high precession data no explicit setting are required

A =1 & 2

B=1

C=2

D=3

ANS:A

27=which of the following statement accurately describes the repository service?

A=each repository service can manage multiple repositories

B=a repository service may be located on two or more domains


6/25

C=each repository service can manage only one repository

D=each node must be assigned at least one repository service

ANS:C

28=in which of the following tabs of administration console-you can manage users, monitor user

activity & domain activity?

A=domain tab

B=you cannot manage user and monitor user activity in administration console

C=logs tab

D=administration tab

ANS:D NOT SURE

29=workflow schedule can be ?

A=both reusable & non-reusable

B=nonreusable

C=global

D=reusable

ANS:A

30=under which of the following condition you can edit the ports of a target definition in the mapping

designer?

A=you cannot change the ports of a target definition in mapping designer under any condition

B=always

C=if target definition is reusable

D=if mapping is saved in the repository

ANS:C

31=in joiner transformation to get performation benefits unsorted master source should be ?

1=large

2=small

3=dont depend on source size

4=master source should not be cached

A=1

B=2

C=3

D=4

ANS:B

32=workflow manager concist of how many tools?


7/25

A=4

B=3

C=5

D=2

ANS:3

33=a user sets the treat rows as session property as update what is the effect of making this

selection?

A=all records processed by the session will be treated as update statement on the target table

provided that the primary key constraint exists on the corresponding target table definition

B=all records processed by the session will be treated as update statement on the target table

C=the session allowa us the sue of the update strategy transformation provide that one or more

update strategy transformation are present in the mapping run by the session

D= the session allowa us the sue of the update strategy transformation provide that one or moreupdate strategy transformation are present in the mapping run by the session & provided that the

primary key constraint exists on the corresponding target table definition

ANS:A

34=when you import the COBOL source in the source analyser how does the designer interpret each

occurs clause?

A=it creates the different column for each occurs statement in the COLOB file

B=each occurs produce multiple output column for each column in the COBOL data set

C=each occurs produce multiple unique identifier in the mapping pipeline, which may correspond to

multiple rows or columns in the COBOL data set

D=it has no effect

ANS:A

35=what you can do to improve session performance?

A=reduce the number of log events generated by the integration service when it run the session

B=set tracing level to verbos data

C=do not set session tracing level to terse

D=all of the listed options

ANS:A

36=the sql always returns one row for each input row in which of the following modes?

A=script

B=query

C=output

D=input

ANS:A

37=where can you create a reusable task?


8/25

A=in task developer or workflow designer

B=in task developer

C= in task developer or workflow designer or the worklet designer

D= in task developer or workflow designer or the worklet designer or session wizard

ANS:A

38=which of the following statement about DTM is incorrect?

A=retrieve and validate session information from the repository

B=reads the parameter file and expandsworkflow variable

C= verifies connection object permission

D=runs post-sessions stored procedure SQL and shell commands

ANS:A

39=can an active java transformation be converted into passive?A=need to remove active transformation logic from code first

B=no

C=yes

D=yes, but reverse is not true

ANS:B

40=what are the threads that will be created when you start a session for execution?

A=reader thread and writer thread

B=writer thread and transformation thread

C= reader thread and transformation thread

D=reader, writer and transformation thread

ANS:D

41=what happen in a router transformation when the filter condition for more than one output groups

evalutes to true?

A=the router will pass the output data from all the output group where the expression is true

B=the roiuter will generate a transformation error

C=the router will pass the data from first output group where the expression is true

D= the router will pass the data from first output group where the expression is true & where the

output port is mapped to another transformation

ANS:A

42=which statement is not true about bulk loading?

A=buk loading improves the performance of a session that insert a target amount of data to target

database

B= integration service invokes a database bulk utility and bypass the database log

C=we can enable recovery by bulk loading

D= we can enable recovery by bulk loading when you load to DB2, Sybase, oracle or Microsoft SQLServer


9/25

ANS:C & D

43=what system variable can be used within a workflow?

A=workflow start time

B=session start time & workflow start time

C=sys date & workflow start time

D=session start time & sys date

ANS:C

44=what happen when you use $source in a stored procedure transformation and the pipeline contain

multiple relational sources joined by a joiner transformation?

A=none

B=the session fails

C=integration service uses different database connectionsD=DB server uses different database connection

ANS:B

45=which of the following is an optional JAVA transformation property?

A=is active

B= transformation scope

C=language

D=generate transactions

ANS:A

46=what are the different dispatch mode of load balancing?

A=adaptive

B=all listed options

C=round robin

D=metric based

ANS:B

47=what happen when transaction control expression evaluates to a value other then commit.

Rollback or continue?

A=the integration service fails the session

B= the integration service rollback the transaction

C= the integration service does not perform any transaction changes

D= the integration service commit the transaction

ANS:A

48=what happen if you edit reusable task instance in workflow designer?


10/25

A=you cannot edit the task instance of a reusable task in workflow designer

B=task cannot be reusable, you can only create it inside workflow

C= the original task definition changes

D=the task definition remain unchanged in task developer

ANS:D

49=when 3 session are running in a parallel in a workflow and suspension is enabled what will be the

status of the workflow when one of the task fails and other two are still running?

A=suspending

B=running

C=failed

D=suspend

ANS:A

50=under what circumstances is it desirable to alter the datatype in the source qualifier?

A=to alter the way the source definition binds data when it is importing into the mapping

B=when the precession required by the mapping is less then the precesion of the data stored in the

table of flat file being read by the source qualifier

C=when the precision required by the mapping is greater than the

D=never. The data type in the source qualifier and the corresponding sources must match

ANS:D

51=aggregator function that cannot be used in incremental aggregator?

1=percentage

2=standard deviation

3=variance

4=count

A=2 4 & 1

B=1 2 & 3

C=2 4 & 3

D=1 2 & 4

NOT CLEAR

52=by which of the following you access the data in informatica using the ODBC data source?

A=none

B=excel

C=excel & Sybase

D=flat files

ANS:C

53=you might use stored procedures to complete which of the following task?


11/25

A=determine if enough space exist in database

B=drop and recreate indexes

C=all of the listed options

D=check the status of the target database

ANS:C

54=which of the following types of objects are visible in repository manager navigation window?

A=mapping, repository user,source & target definition

B=repository, source, target, mapping

C=repository service, integration service, domain, folder

D=source & target definition, integration service, nodes

ANS:B

55=what value of new lookup row specifies that integration service updates the row in the cache?

A=0

B=1

C=2

D=3

ANS:2

56=which of the following are true about recovering the workflow?

A=when you recover the workflow, integration service recover the entire workflow and continue

running the workflow path

B= when you recover the workflow, integration service recover the failed session and restart the

workflow path

C= when you recover the workflow, integration service recover the failed session and continue

running the rest of the task in the workflow path

D= you cannot recover a workflow if it fails you need to abort it and restart

ANS:B

57=in which of the following operating modes an integration service can run?

A=normal & safe

B=normal & exclusive

C=enabled & disabled

D=enabled, disabled & exclusive

ANS:A

58=what will be the relationship b/w the code page and the informatica server, source & target?

A=source & target code pages must be subsets of informatica server

B=informatica server and source code page must be subsets of targets

C=informatica server & target code pages must be subsets of sourceD=none


12/25

ANS:A

59=metadata is stored in.?

A=DB

B=flat file

C=repository

D=ODBC

ANS:A

60=following filter condition used in filter

IIF(ISNULL(FIRST_NAME), TRUE,FALSE)

All the rows having NULL as FIRST_NAME..

A=this will give error at run time and the record will be logged in the session log file

B= will bw discarded

C=the condition is invalid

D=will be passed to the next transformation

ANS:D

Informatica ETL Interview QuestionsAnswers

Posted UnderInformatica-Interview-Questions

Tags:Informatica Questions Answers.Questions asked in Infosys, TCS, CTS, WIPRO,

Accenture, ORACLE, CSC, EDS, IBM, L&T

When to use Abort, Decode functions?

Ans: Abort can be used to Abort / stop the session on an error condition. If the primary key column

contains NULL, and you need to stop the session from continuing then you may use ABORT function

in the default value for the port. It can be used with IIF and DECODE function to Abort the session.

What is constraint based loading ?

Ans: Constraint based loading. the data was loaded into the target table based on the Constraints.i.eif we want to load the EMP&DEPT data, first it loads the data of DEPT then EMP because DEPT is
http://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questions


13/25

PARENT table EMP is CHILD table. In simple terms, it loads PARENT table first then CHILD table.

In a joiner transformation, you should specify the source with fewer rows as the master

source. Why?

Ans: Joiner transformation compares each row of the master source against the detail source. The

fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the

join process.

What is Incremental Aggregation?

Ans: Whenever a session is created for a mapping Aggregate Transformation, the session option for

Incremental Aggregation can be enabled. When PowerCenter performs incremental aggregation, it

passes new source data through the mapping and uses historical cache data to perform new

aggregation calculations incrementally.

What is the default join that source qualifier provides?

Ans: Inner equi join.

What are the options in the target session of update strategy transformations?

Ans:

Insert

Delete

Update

Update as update

Update as insert

Update else insert

Truncate table

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

Ans: Normalizer Transformation.

What is the use of tracing levels in transformation?

Ans: Tracing levels store information about mapping and transformations.

What are the basic needs to join two sources in a source qualifier?

Ans: Two sources should have primary and foreign key relationships. Two sources should havematching data types.


14/25

What is update strategy transformation?

Ans: This transformation is used to maintain the history data or just most recent changes in to target

table.

Describe two levels in which update strategy transformation sets?

Ans: Within a session. When you configure a session, you can instruct the Informatica Server to

either treat all records in the same way (for example, treat all records as inserts), or use instructions

coded into the session mapping to flag records for different database operations. Within a mapping.

Within a mapping, you use the Update Strategy transformation to flag records for insert, delete,

update, or reject.

What are the various types of transformation?

Ans: Various types of transformation are: Aggregator Transformation, Expression Transformation,

Filter Transformation, Joiner Transformation, Lookup

Transformation, Normalizer Transformation, Rank Transformation, Router Transformation, Sequence

Generator Transformation, Stored Procedure Transformation,

Sorter Transformation, Update Strategy Transformation, XML Source Qualifier Transformation,

Advanced External Procedure Transformation, External Transformation.

What is the difference between active transformation and passive transformation?

Ans: An active transformation can change the number of rows that pass through it, but a passive

transformation can not change the number of rows that pass through it.

What is the use of control break statements?

Ans: They execute a set of codes within the loop and endloop

- See more at:http://www.aired.in/2010/03/informatica-etl-interview-

questions.html#sthash.GqI79nJn.dpuf

Informatica Interview QuestionsAnswers Page 4

Posted UnderInformatica-Interview-Questions
http://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpufhttp://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpufhttp://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpufhttp://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpufhttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/search/label/Informatica-Interview-Questionshttp://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpufhttp://www.aired.in/2010/03/informatica-etl-interview-questions.html#sthash.GqI79nJn.dpuf


15/25

Tags:Informatica Jos Interview Questions Answers,Datawarehousing Informatica Interview

Questions and Solutions,Informatica Interview Questions gathered from MNCs

Which tool you use to create and manage sessions and batches and to monitor and stop the

Informatica server?

Ans: Informatica server manager.

Why we use partitioning the session in Informatica?

Ans: Partitioning achieves the session performance by reducing the time period of reading the source

and loading the data into target.

To achieve the session partition what are the necessary tasks you have to do?

Ans:

Configure the session to partition source data.

Install the Informatica server on a machine with multiple CPUs.

What is the default join that source qualifier provides?

Ans: Inner EquiJoin

What is Datadriven?

Ans.The Informatica server follows instructions coded into update strategy transformations within the

session mapping which determine how to flag records for insert, update, delete or reject. If we do not

choose data driven option setting, the Informatica server ignores all update strategy transformations

in the mapping.

What is polling

Ans: It displays the updated information about the session in the monitor window. The monitor

window displays the status of each session when you poll the Informatica server.

What are the types of mapping wizards that are provided in Informatica?

Ans: The designer provide two mapping wizard.

Getting Started Wizard creates mapping to load static facts and dimension tables as well as slowly

growing dimension tables.

Slowly Changing Dimensions Wizard, creates mappings to load slowly changing dimension tables

based on the amount of historical dimension data we want to keep and the method we choose tohandle historical dimension data.


16/25

Explain reference cursor? (PL/SQL)

Ans: Cursor doesn't have a return type but A Reference Cursor have a return type and it as 2 type

one is Strongly Typed Cursor and Weakly Typed Cursor

What is a source qualifier? What do you mean by Query Override?

Ans: Source Qualifier represents the rows that the PowerCenter Server reads from a relational or flat

file source when it runs a session. When the definition of the relational or flat file is added to mapping

then it is connected to Source Qualifier transformation. The default query is SELECT statement

containing all the source columns. Source Qualifier has capability to override this default query by

changing the default settings of the transformation properties.

Can you generate reports in Informatica?

Ans: Yes. By using Metadata reporter we can generate reports in Informatica.

What is metadata reporter?

Ans: It is a web based application that enables you to run reports against repository metadata. With a

Meta data reporter you can access information about your repository with out having knowledge of

sql, transformation language or underlying tables in the repository.

Define mapping and sessions?

Ans:Mapping: It is a set of source and target definitions linked by transformation objects that define

the rules for transformation.

Session: It is a set of instructions that describe how and when to move data from source to targets

- See more at:http://www.aired.in/2010/03/informatica-interview-questions-

answers_29.html#sthash.5kEnlWSc.dpuf

What are the types of loading in Informatica?

Ans: There are two types of loading, normal loading and bulk loading. In normal loading, it loads

record by record and writes log for that. It takes comparatively a longer time to load data to the target

in normal loading. But in bulk loading, it loads number of records at a time to target database. It takes

less time to load data to target.

What is aggregate cache in aggregator transformation?

Ans: The aggregator stores data in the aggregate cache until it completes aggregate calculations.

When you run a session that uses an aggregator transformation,the Informatica server creates index

and data caches in memory to process the transformation. If the Informatica server requires more

space, it stores overflow values in cache files.
http://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers_29.html#sthash.5kEnlWSc.dpuf


17/25

Define Informatica repository?

Ans: Infromatica Repository:The Informatica repository is at the center of the Informatica suite. You

create a set of metadata tables within the repository database that the Informatica application and

tools access. The Informatica client and server access the repository to save and retrieve metadata.

What are the tasks that source qualifier performs?

Ans: Join data originating from same source data base. Filter records when the informatica server

reads source data. Specify an outer join rather than the default inner join specify sorted records.

Select only distinct values from the source. Creating custom query to issue a special SELECT

statement for the informatica server to read source data.

What is the target load order?

Ans: Specify the target load order based on source qualifiers in a mapping. If you have the multiple

source qualifiers connected to the multiple targets you can designate the order in which informatica

server loads data into the targets.

How Union Transformation is used?

Ans: The union transformation is a multiple input group transformation that can be used to merge

data from various sources (or pipelines). This transformation works just like UNION ALL statement in

SQL, that is used to combine result set of two SELECT statements.

Can two flat files be joined with Joiner Transformation?

Ans: Yes, joiner transformation can be used to join data from two flat file sources.

What is a look up transformation?

Ans: This transformation is used to lookup data in a flat file or a relational table, view or synonym. It

compares lookup transformation ports (input ports) to the source column values based on the lookup

condition. Later returned values can be passed to other transformations.

Can a lookup be done on Flat Files?

Ans: Yes.

Which transformation should you need while using the cobol sources as source defintions?

Ans: Normalizer transformaiton which is used to normalize the data.Since cobol sources are oftenly

consists of Denormailzed data.

What is the difference between a connected look up and unconnected look up?

Ans: Connected lookup takes input values directly from other transformations in the pipleline.

Unconnected lookup doesnt take inputs directly from any other transformation, but it can be used inany transformation (like expression) and can be invoked as a function using :LKP expression. So, an

unconnected lookup can be called multiple times in a mapping.

What are the types of data that passes between informatica server and stored procedure?

Ans: 3 types of data

Input/Out put parameters

Return Values

Status code.

What is the status code?

Ans: Status code provides error handling for the informatica server during the session. The storedprocedure issues a status code that notifies whether or not stored procedure completed


18/25

sucessfully.This value can not seen by the user. It only used by the informatica server to determine

whether to continue running the session or stop.

What is source qualifier transformation?

Ans: When you add a relational or a flat file source definition to a mapping, you need to connect it to

a source qualifier transformation. The source qualifier transformation represents the records that theinformatica server reads when it runs a session. - See more at:

http://www.aired.in/2010/04/informatica-interview-questions-and.html#sthash.qfQ8O1Pb.dpuf

What is difference between $ and $$ parameters/variables

Ans: $ are System defined and $$ are User defined variables

How to use data from SAP/Oracle Apps /Mainframe

Ans: Through power exchange.

What are the types of lookup?Ans: Connected and unconnected.

What is meant by lookup caches?

Ans: The informatica server builds a cache in memory when it processes the first row of a data in a

cached look up transformation. It allocates memory for the cache based on the amount you configure

in the transformation or session properties. The informatica server stores condition values in the index

cache and output values in the data cache.

How to find all invalid mappings in a folder

Ans: we can find the invalid mappings information from the rep_all_mappings view with a query

Select mapping_name from rep_all_mappings Wheresubject_area='Folder_Name' And parent_mapping_is_valid 1

What are different kinds of aggregation?

Ans: Some examples of aggregation are SUM, AVG, COUNT, MAX, MIN, LAST, MEDIAN,

PERCENTILE etc.

What is difference between maplet and reusable transformation?

Ans: Maplet: one or more transformations

Reusable transformation: only one transformation

Define maping and sessions?

Ans:Maping: It is a set of source and target definitions linked by transformation objects that define

the rules for transformation.

Session : It is a set of instructions that describe how and when to move data from source to targets.

What is a code page?

Ans.A code page contains encoding to specify characters in a set of one or more languages and is

selected based on source of the data. The set code page refers to a specific set of data that describes

the characters the application recognizes. This influences the way that application stores, receives,

and sends character data.

What is Target Update override?

Ans: It overrides the default update statement in the target properties.
http://www.aired.in/2010/04/informatica-interview-questions-and.html#sthash.qfQ8O1Pb.dpufhttp://www.aired.in/2010/04/informatica-interview-questions-and.html#sthash.qfQ8O1Pb.dpufhttp://www.aired.in/2010/04/informatica-interview-questions-and.html#sthash.qfQ8O1Pb.dpuf


19/25

How are Informatica object files stored?

Ans: They are stored as XML in the Informatica Repository.

How do we tune the performance of Informatica transformations?

Ans:Use Filter transformation before transforming the unwanted data. Go for dynamic lookups

as far as possible. Use Union transformations in place of using multiple transformations withbinded SQLs.

In what conditions we cannot use Joiner transformation?

Ans: Both pipelines begin with the same original data source. Both input pipelines originate from the

same Source Qualifier transformation. Both input pipelines originate from the same Normalizer

transformation. Both input pipelines originate from the same Joiner transformation. Any of the input

pipelines contains an Update Strategy transformation. Any of the input pipelines contains a connected

or unconnected Sequence Generator transformation.

If there is no PK or FK in the target table, how do we update or insert value into the table?

Ans: We take a dynamic lookup on the target and do a comparison with source in an expression and

flag it.

What are the settings that you use to configure the joiner transformation?

Ans: Master and detail source Type of join. Condition of the join.

What are the join types in joiner transformation?

Ans: 1. Normal (Default)

2. Master outer

3. Detail outer

4. Full outer - See more at:http://www.aired.in/2010/03/informatica-tool-interview-

questions.html#sthash.bDDBSKkH.dpuf

What are Data driven Sessions?

Ans: The Informatica server follows instructions coded into update strategy transformations with in

the session mapping to determine how to flag records for insert,update,delete or reject.

What is Data cleansing?

Ans: The process of finding and removing or correcting data that is incorrect, out-of-date, redundant,

incomplete, or formatted incorrectly.

What is the differences between connected and unconnected lookup?

Ans: Connected lookupUnconnected lookup Receives input values diectly from the pipe

line.Receives input values from the result of a lkp expression in a another transformation.

What is the look up transformation?

Ans:Use lookup transformation in youare mapping to lookup data in a relational table,view,synonym.

Informatica server queries the look up table based on the lookup ports in the transformation.

What are the basic needs to join two sources in a source qualifier?

Ans: Two sources should have primary and Foreign key relation ships. Two sources should have

matching data types.

What are the reusable transforamtions?

Ans: Reusable transformations can be used in multiple mappings.When you need to incorporate this

transformation into mapping ,You add an instance of it to mapping
http://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpufhttp://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpufhttp://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpufhttp://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpufhttp://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpufhttp://www.aired.in/2010/03/informatica-tool-interview-questions.html#sthash.bDDBSKkH.dpuf


20/25

What are the types of mapping wizards that are to be provided in Informatica?

Ans: The Designer provides two mapping wizards to help you create mappings quickly and easily.

Both wizards are designed to create mappings for loading and maintaining star schemas, a series of

dimensions

Why we use stored procedure transformation?Ans: For populating and maintaining data bases.

What is tracing level and what are the types of tracing level?

Ans: Tracing level represents the amount of information that informatcia server writes in a log file.

Types of tracing level Normal Verbose Verbose init Verbose data

What are Target Types on the Server?

Ans: Target Types are File, Relational and ERP.

What are Target Options on the Servers?

Ans: Target Options for File Target type are FTP File, Loader and MQ.There are no target options for

ERP target typeTarget Options for Relational are Insert, Update (as Update), Update (as Insert),

Update

Can you start a batches with in a batch?

Ans: You can not. If you want to start batch that resides in a batch,create a new independent batch

and copy the necessary sessions into the new batch.

What are the mappings that we use for slowly changing dimension table?

Ans: Type1: Rows containing changes to existing dimensions are updated in the target by overwriting

the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data. -

See more at:http://www.aired.in/2010/03/informatica-interview-questions-

and.html#sthash.zdCcyEGe.dpuf

What is the mapplet?

Ans: Mapplet is a set of transformations that you build in the mapplet designer and You can use in

multiple mappings.

What is update strategy transformation?

Ans: This transformation is used to maintain the history data or just most recent changes in to target

table.

How can You create or import flat file definition in to the warehouse designer?

Ans: You can not create or import flat file defintion in to warehouse designer directly.Instead You

must analyze the file in source analyzer,then drag it into the warehouse designer.

What are the different options used to configure the sequential batches?

Ans: Two options Run the session only if previous session completes sucessfully. Always runs the

session.

How can You improve session performance in aggregator transformation?

Ans: Use sorted input.

What is Router transformation?Ans: Router transformation allows you to use a condition to test data. It is similar to filter
http://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and.html#sthash.zdCcyEGe.dpuf


21/25

transformation. It allows the testing to be done on one or more conditions.

How many ways you create ports?

Ans: Two ways 1.Drag the port from another transforamtion 2.Click the add buttion on the ports tab.

How can you stop a batch?Ans: By using server manager or pmcmd.

Can you use the mapping parameters or variables created in one mapping into any other

reusable transformation?

Ans: Yes.Because reusable tranformation is not contained with any maplet or mapping .

How can you recover the session in sequential batches?

Ans: If you configure a session in a sequential batch to stop on failure, you can run recovery starting

with the failed session.

How to recover sessions in concurrent batches?

Ans: If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the

batch again. However, if a session in a concurrent batch fails and the rest of the sessions complete

Can you start a session inside a batch individually?

Ans: We can start our required session only in case of sequential batch.

When the Informatica server marks that a batch is failed?

Ans: If one of session is configured to "run if previous completes" and that previous session fails.

To achieve the session partition what are the necessary tasks you have to do?

Ans: Configure the session to partition source data. Install the Informatica server on a machine with

multiple CPUs.

What are various types of Aggregation?

Ans: Various types of aggregation are SUM, AVG, COUNT, MAX, MIN, FIRST, LAST, MEDIAN,

PERCENTILE, STDDEV, and VARIANCE.

Which transformation should you need while using the cobol sources as source defintions?

Ans: Normalizer transformaiton which is used to normalize the data.Since cobol sources are oftenly

consists of Denormailzed data. - See more at:http://www.aired.in/2010/03/informatica-interview-

questions-and_16.html#sthash.OzDmkPDp.dpuf

What is a transformation?

Ans: It is a repository object that generates, modifies or passes data.

What are the designer tools for creating transformations?

Ans:

1. Mapping designer

2. Transformation developer

3. Mapplet designer

What is active and passive transformation?

Ans: An active transformation can change the number of rows that pass through it. Passive

transformation does not change the number of rows that pass through it.
http://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_16.html#sthash.OzDmkPDp.dpuf


22/25

What are the different options used to configure the sequential batches?

Ans: Run the session only if previous session completes successfully. Always runs the session.

What is data driven?

Ans: If you will select the data driven option then this is an ultimatum to the Informatica server and it

will soon ignore all the strategic transformation which has taken place in the mapping.

What is aggregate cache in aggregator transformation?

Ans.The aggregator stores data in the aggregate cache until it completes aggregate calculations.

When we run a session that uses an aggregator transformation, the Informatica server creates index

and data caches in memory to process the transformation. If the Informatica server requires more

space, it stores overflow values in cache files.

In which circumstances that Informatica server creates Reject files?

Ans:

When it encounters the DD_Reject in update strategy transformation.

Violates database constraint

Filed in the rows was truncated or overflowed.

What is polling?

Ans: It displays the updated information about the session in the monitor window. The monitor

window displays the status of each session when U poll the Informatica server.

Can you copy the session to a different folder or repository?

Ans:

Yes. By using copy session wizard you can copy a session in a different folder or repository. But that

target folder or repository should consists of mapping of that session. If target folder or repository is

not having the mapping of copying session, you should have to copy that mapping first before you

copy the session

What is batch and describe about types of batches?

Ans:

Grouping of session is known as batch.

Batches are two types

Sequential: Runs sessions one after the other

Concurrent: Runs session at same time. If you have sessions with source-target dependencies you

have to go for sequential batch to start the sessions one after another. If you have several

independent sessions you can use concurrent batches. Which runs all the sessions at the same time?

What are two types of processes that runs the session?Ans: The two types of processes that runs the session are Load Manager and DTM process.

Load manager process starts the session, creates DTM process, and sends post session email when

the session completes. DTM process creates threads to initialize the session, read, write and

transform data and handle pre-session and post-session operations. - See more at:

http://www.aired.in/2010/03/informatica-interview-questions-answers.html#sthash.mmR1VPQ7.dpuf

What are the settiings that you use to cofigure the joiner transformation?

Ans: Master and detail source Type of join Condition of the join.

What are the types of groups in Router transformation?Ans: Input group Output group The designer copies property information from the input ports of the
http://www.aired.in/2010/03/informatica-interview-questions-answers.html#sthash.mmR1VPQ7.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers.html#sthash.mmR1VPQ7.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-answers.html#sthash.mmR1VPQ7.dpuf


23/25

input group to create a set of output ports for each output group. Two types of output groups User

defined.

How many ways you can update a relational source defintion and what are they?

Ans: Two ways 1. Edit the definition 2. Reimport the defintion

What are the options in the target session of update strategy transsformatioin?

Ans: Insert Delete Update Update as update Update as insert Update esle insert Truncate table

What is the default source option for update stratgey transformation?

Ans: Data driven.

Which tool would you use to create and manage sessions and batches and to monitor and

stop the Informatica server?

Ans: Informatica server manager.

What are partition points?

Ans: Partition points mark the thread boundaries in a source pipeline and divide

the pipeline into stages

How can you recognize whether or not the newly added rows in the source are get inserted in

the target?

Ans: In the Type2 mapping we have three options to recognize the newly added rows 1. Version

number

2. Flag value

3. Effective date Range

What are two types of processes that Informatica runs the session?

Ans: Load manager Process: Starts the session, creates the DTM process, and sends post-session

email when the session completes. The DTM process. Creates threads to initialize the session, read,write, and transform data, and handle pre and post-session operations.

What is parameter file?

Ans: Parameter file is to define the values for parameters and variables used in a session.A

parameter file is a file created by text editor such as word pad or notepad.

You can define the following values in parameter file:-

Maping parameters

Maping variables

session parameters.

Differences between Normalizer and Normalizer transformation.Ans: Normalizer: It is a transformation mainly using for Cobol sources,

it's change the rows into columns and columns into rows

Normalization: To remove the redundancy and inconsistency

What is the default source option for update strategy transformation?

Ans: Data driven.

What is Data driven?

Ans: The Informatica server follows instructions coded into update strategy transformations with in

the session mapping determine how to flag records for insert,update,,delete or reject .If you do not

choose data driven option setting, the Informatica server ignores all update strategy transformations


24/25

in the mapping. - See more at:http://www.aired.in/2010/03/informatica-interview-questions-

and_21.html#sthash.azbwDyP4.dpuf

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

mapping?Ans: No

What is difference between direct and indirect loading options in sessions?

Ans: Use file type direct when we are loading single file into Target. Use Indirect when we want to

load multiple files through single session in the mapping

When we create a target as flat file and source as oracle. How can we have first rows as

column names in flat files.

Ans :We can add a union all clause in the Source Qualifier of the Relational source to add the header

values in SELECT clause, so as to populate them as header.

How to capture the user information (Username) of the person, who is executing a workflow or

session in workflow manager.

Ans : SELECT USER_NAME,WORKFLOW_NAME FROM REP_WFLOW_RUN;

Which ETL tool is more preferable Informatica or Data Stage and why?

Ans: preference of an ETL tool depends on affordability and functionality. It is mostly a tradeoff

between the price and feature. While Informatica has been a market leader since the past many

years, DataStage is beginning to pick up momentum.

What is worklet?

Ans: Worklet is an object that represents a set of tasks.

What is workflow?

Ans: A workflow is a set of instructions that tells the Informatica server how to execute the tasks.

What is session?

Ans: A session is a set of instructions to move data from sources to targets.

Why do we need SQL overrides in Lookup transformations?

Ans: In order to lookup more than one value from one table, we go for SQL overrides in Lookups.

What is the difference between static cache and dynamic cache.

Ans:Static cache: you can not insert or update the cache.

The informatica server returns a value from condition when the condition is false. This indicates that is

true. When the condition is not true, informatica server returns the default value

Dynamic cache: You can insert rows into the cache as you pass to the target.

The informatica server inserts rows into cache when the condition is false. This indicates the row is

not in the cache or target.

You can pass these rows to the target table. For connected transformations and null for unconnected

transformations.

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

Ans: Normalizer Transformation.

When you drag the COBOL source in to the mapping Designer workspace, the normalizertransformation automatically appears, creating input and output ports for every column in the source.
http://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpufhttp://www.aired.in/2010/03/informatica-interview-questions-and_21.html#sthash.azbwDyP4.dpuf


25/25

How to join two tables, which reside in two different databases in the Source qualifier.

Ans: By using DB links in SQ ,but throughput will be very slow. Joiner will be better approach in this

case

What is a code page?Ans: A code page contains encoding to specify characters in a set of one or more languages. The

code page is selected based on source of the dat.For example if source contains Japanese text then

the code page should be selected to support Japanese text.

When a code page is chosen, the program or application for which the code page is set, refers to a

specific set of data that describes the characters the application recognizes. This influences the way

that application stores, receives, and sends character data. - See more at:

http://www.aired.in/2010/03/informatica-technical-interview.html#sthash.pboZ40W7.dpuf

informatica cert qa set1

Documents