Largest Collection of Informatica Technical Interview Questions and Answers.

Questions asked in companies like Cap Gemini, Tech Mahindra, CTS, IBM, HP, TCS, Wipro, Infosys.Learn about the Informatica Questions in major companies.

Informatica Interview Question with Answers.

• Informatica Interview Questions and Answers
• Informatica Interview Questions Answers Page 4
• Informatica ETL Interview Questions Answers
• Informatica Tool Interview Questions Questions and Solutions
• Free Informatica Interview Questions Answers
• Informatica Interview Questions and Answers Part 3
• Informatica Interview Questions and Answers Part 2
• Informatica Interview Questions and Answers Part 1
• Informatica Technical Interview Preparation
• Informatica Interview Questions with Answers
• Informatica Interview Questions Answers Page 2
• Informatica Interview Questions Answers Page 1
• Informatica ETL Interview Questions Solutions
• Informatica Job Interview Questions Answers

Informatica Interview Question.

• Informatica ETL Interview Questions 2
• Free Informatica Interview Questions
• Informatica ETL Tool Interview Questions
• Interview Questions on ETL Tool Informatica
• Informatica FAQs Interview Questions
• Informatica Data Warehousing Interview Questions
• Informatica Frequently Asked Interview Questions
• Informatica Technical Interview Questions
• Informatica Interview Questions asked in MNCs
• Informatica FAQS Technical Interview Questions
• Informatica Job Interview Questions
• Informatica Tool Interview Questions
• Informatica Interview FAQs And Answers
• Informatica Interview Questions Collection 3
• Informatica Interview Questions Collection 2
• Informatica Interview Questions Collection 1
• Informatica ETL Interview Questions
• IBM Informatica Technical Questions
• Informatica Interview Questions Collection
• Informatica Technical Questions Collection

Read More »

Informatica ETL Tool Interview Questions ,Free Informatica Interview Questions.

1. What are the new features and enhancements in PowerCenter 5.1?
2. What are the various test procedures used to check whether the data is loaded in the backend, performance of the mapping, and quality of the data loaded in Informatica.
3. What is a repository?
4. What is difference between stored procedure transformation and external procedure transformation? 
5. What is polling?
6. What is the difference between active transformation and passive transformation?
7. What is the exact syntax of an update strategy?
8. What is update strategy and what are the options for update strategy?
9. Why do we use reusable sequence generator transformation only in mapplet?
10. Can we use lookup instead of join? Reason
11. Give me an scenario where flat files are used?
12. How do we do complex mapping by using flatfiles / relational database?
13. How does sorted input works in aggregator transformation
14. How to get 25 of 100 fields table is there any transformation available in Informatica I know router/filter or any other?
15. If I done any modifications for my table in back end does it reflect in Informatica warehouse or mapping designer or source analyzer?
16. To provide support for Mainframes source data,which files are used as a source definitions? COBOL files
17. What are the common errors while running a Informatica session?
18. What are the new features in Informatica 5.0? 
19. What are the various types of transformation?
20. What is a shared folder?
21. What is drill-down and drill-up?
22. What is power center repository? 
23. What is the difference between connected and unconnected stored procedures.
24. What is the file extension and format of the files for the Informatica objects like Mappings, sessions etc in the repository?
25. What is update strategy transformation? 
26. Why do you use a reusable sequence generator transformation in mapplets?
27. Can we use lookup transformation with out using source qualifier transformation?
28. Have you implemented Lookup in your mapping, If yes give some example?
29. How do we do unit testing in Informatica?How do we load data in Informatica ?
30. How does the Informatica know that the input is sorted? 
31. How to get the first 100 rows from the flat file into the target?
32. If index cache file capacity is 2MB and data cache is 1 MB. If you enter the data of capacity for index is 3 MB and data is 2 MB. What will happen?
33. Two relational tables are connected to SQ Trans,what are the possible errors it will be thrown?
34. What are the common problems developers face while ETL development
35. What are the new features of the server manager in the Informatica 5.0? 

Read More »

Overview:Informatica Interview Questions Answers,Informatica Job Interview Questions and Solutions,Informatica Tool Interview Questions,Informatica Interview Questions with Answers

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.

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 in any 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 stored procedure issues a status code that notifies whether or not stored procedure completed 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 the informatica server reads when it runs a session.

Read More »

Overview:Informatica ETL Interview Questions asked in top companies like Cap Gemini,IB ,HP,Accenture,Dell,Cisco ,CTS.Learn by sharing Informatica Questions asked in various companies

What are tracing levels in transformation?

What is a Shortcut and what is the difference between a Shortcut and a Reusable Transformation?

What is DTM process?

What is Powermart and Power Center?

What is the difference between connected lookup and unconnected lookup?

What is the functionality of Lookup Transformation (connected & un connected)

What is Update Strategy?

why sorter transformation is an active transformation?

Can you access a repository created in previous version of Informatica?

How a LOOKUP is passive?

How do we estimate the depth of the session scheduling queue? Where do we set the number of
maximum concurrent sessions that Informatica can run at a given time?

How does the server recognise the source and target databases?

How to get the records starting with particular letter like A in Informatica?

If source qualifier having employee details.the empid is in 5th placle and deptno is in 7th column then. you selected sort by 2. but how the Informatica server will come know that those are the fields to sort.

What are the type of loading procedures?

What are the connected or unconnected transformations?

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

What are Transformations?

What is a source qualifier?

What is DTM process?2. What are the output files that Informatica creates during the session running?3. limitations of joiner transformations?under which conditions we can't use joiner transformation?

What is pre-session and post-session?

What is the difference between connected stored procedure and unconnected stored procedure?

What is the gap analysis?

What is use of event waiter?

Why we use lookup transformations?

Can you copy the batches?

How can I get distinct values while mapping in Informatica in insertion?

How do we estimate the number of partitons that a mapping really requires? Is it dependent on
the machine configuration?

How is the Sequence Generator transformation different from other transformations?

How to get two targets T1 containing distinct values and T2 containing duplicate values from one source S1.

If the workflow has 5 session and running sequentially and 3rd session has failed how can we run again from only 3rd to 5th session?

Under what circumstance can a target definition are edited from the mapping designer. Within the mapping where that target definition is being used?

What are the Data Cleansing Tools used in the DWH?What are the Data Profiling Tools used for DWh?

What are the out put files that the informatica server creates during the session running?

What are two modes of data movement in Informatica Server?

Read More »

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 CPU’s.

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 to handle historical dimension data.

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.

Read More »

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.e if we want to load the EMP&DEPT data, first it loads the data of DEPT then EMP because DEPT is 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 have matching data types.

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.

Read More »

Tags:Informatica ETL Interview Questions Answers,Informatica Interview Questions gathered from MNCs like Delloite,Tech Mahindra,Cognizant,IBM ,HP

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 Where
subject_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.

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 with binded 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

Read More »

Tags:Informatica Interview Questions ,Informatica Interview Questions Preparations, Free Informatica Interview Questions Answers ,Free Informatica Technical Interview Questions Solutions

What are the difference between joiner transformation and source qualifier transformation?
Ans.In joiner transformation heterogeneous data sources can be joined but this cannot be achieved incase of source qualifier transformation.
We need matching keys to join two relational sources in source qualifier transformation whereas this is not needed incase of joiner. Two relational sources should come from same data source in source qualifier. We can join relational sources which are coming from different sources also.

What is the default join that source qualifier provides?
Ans: The default join that source qualifier uses is inner equi join.

What is Normalizer transformation?
Ans: This is one of the transformations in Informatica. This is applicable only for cobol sources. If we want to change rows into column or column into rows, we have to go for normalizer.

What are the default sources supported by Informatica Powermart?
Ans: Informatica Powermart supports Relational tables, fixed width and delimited files and Cobol sources.

What is sql override in Informatica?
Ans: We use sql override in Informatica to improve performance of lookup transformation. We write the query which is already executed by Informatica. Here we use Join queries.

What is Power Analyzer in Informatica?
Ans: Informatica Power Analyzer is the only BI tool in Informatica. This is the tool in Informatica which gives advantages for data delivering, adhoc reporting and dashboard.

What are the Hierarchy of DWH?
Ans: Data modeling
ETL process
BI process

How do you do error handling in Informatica ?
Ans: Debugger

What are the various ETL tools in the Market?
Ans: Informatica, AB initio, Oracle Warehouse Builder

Is it possible to generate reports from Informatica itself..?
Ans: Yes, by using Metada Reporter, a web based application

While importing the relational source definition from database, what are the meta data of source you import?
Ans:
• Source name
• Database location
• Column names
• Data types
• Key constraints

How many ways you can update a relational source definition and what are those way?
Ans:
Two ways
1. Edit the definition
2. Reimport the definition

What is meant by lookup cache?
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.

What is the target load order?
Ans: You 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.

Read More »

Tags:Informatica Interview Questions and Answers,Data Warehouse Informatica Interview,Informatica PowerCenter Interview Questions,Informatica Selected Interview Questions

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 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 in the mapping.

Read More »

Tags:Informatica Interview Questions,Informatica Frequently Asked Questions and Answers,Informatica Technical Interview Preparation ,Solutions, Answers,Explanations

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 normalizer transformation automatically appears, creating input and output ports for every column in the source.

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.

Read More »

Overview:Informatica Frequently Asked Interview Questions and Answers ,Informatica Questions expected in Job Interviews,Informatica Tools technical Interview Questions

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 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 CPU’s.

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.

Read More »

Overview:Free Informatica Data Warehousing Interview Questions,Informatica Interview Questions

What are the circumstances that Infromatica server results an unrecoverable session?

What are the measure objects

What are the uses of a Sequence Generator transformation?

What is a Materialized view? Difference Between Materialized view and view

What is difference between Mapplet and reusable transformation?

What is Partitioning ? where we can use Partition? wht is advantages?Is it necessary?

What is the difference between a Database and a Datawarehouse?

What is the exact difference between joiner and lookup transformation

What is Transaction?

Why do we need lookup sql override? Do we write sql override in lookup with special aim?

Can we update target table without using update strategy transformation? why?

For homogeneous sources we use Source Qualifier to join the sources? Why not the joiner
transformation.

How do we create primary key only on odd numbers?

How does dynamic cache handle the duplicates rows?

How to FTP a file to a remote server?

If a session fails after loading of 10,000 records in to the target.How can you load the records from 10001 th record when you run the session next time in Informatica 6.1?

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

What are the Commit & Commit Intervals?

What are the methods for creating reusable transforamtions?

What are the UTP'S

What is a metadata?

What is difference between partioning of relatonal target and partitioning of file targets?

What is Partitioning, Bitmap Indexing (when to use), how will the bitmap indexing will effect the
performance

What is the difference between a session and a task?

What is the exact meaning of domain?

What is unbounded exception in source qualifier?

Why do we need SQL overrides in Lookup transformations?

Can we use aggregator/active transformation after update strategy transformation

For joining three heterogeneous tables how many joiner transformations are required?

How do we delete staging area in our project?

How does read permission affect the use of the command line program, pmcmd?

How to Generate the Metadata Reports in Informatica?

If i done any modifications for my table in back end does it reflect in Informatca warehouse or mapping designer or source analyzer?

To provide support for Mainframes source data,which files are used as a source definitions?

What are the common errors that you face daily?

Read More »

Overview:Informatica FAQs Interview Questions,Informatica Interview Questions
helpful for clearing any interview

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.

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.

Read More »

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 you’are 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

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.

Read More »

Overview:Informatica ETL Tool Interview Questions, ETL Tool Informatica Interview Questions,Informatica Interview Questions.

Name some measures in your fact table?

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

What are the mapping parameters and mapping variables?

What are the types of target loads

What is a Lookup transformation and what are its uses?

What is difference between Informatica 7.1 and Abinitio

What is one disadvantage of using an unconnected (sometimes called function mode)

What is Lookup transformation?

What is the difference between Local and Global repository?

What is the economic comparison of all the Informatica versions?

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

Why did you use update strategy in your application?

Can we revert back reusable transformation to normal transformation?

Explain the pipeline partition with real time example?

How do I import VSAM files from source to target. Do I need a special plugin

How do you validate all the mappings in the repository at once?

How to export mappings to the production environment?

I have the Administer Repository Privilege, but I cannot access a repository using the
Repository Manager.

How to get particular record from the table in Informatica?

What are the challenges you have faced in your Project (DWH-ETL)? Explain with example?

What are the mapping specifications? How versioning of repository objects?

What are the unsupported repository objects for a mapplet?

What is a mapplet?

What is difference between lookup cashe and unchashed lookup?Can I run the mapping with out
starting the Informatica server?

What is parameter file?

What is the difference between a Bulk and Normal mode and where exactly is it defined?

What is the event-based scheduling?

What is tracing level?

Why dimenstion tables are normalized in nature ?

Can we run a group of sessions without using workflow manager

For a session, can I use 3 mappings?

How do we analyze the data at database level?

How do you write a session parameter file which will change the source and targets for every
session. i.e different source and targets for each session run.

How to extract 10 records out of 100 records in a flat file

If a Mapping is running slow, What steps you will take, to correct it?

There are two flat files and the two flat files contain two fields as states. both the flat files contain duplicates.How to eliminate this duplicates?

Read More »

Informatica FAQs, Interview Questions and Technical Interview Questions.

1. Explain what do you mean by a source qualifier in Informatica? What is meant by Query Override?
2. What do you mean by QTP in Data Warehousing?
3. What is the difference between constrained base load ordering and target load plan
4. Explain what is hierarchies in DWH?
5. What do you mean by user defined Transformation?
6. Why we use partitioning the session in Informatica? 
7. Can you copy the session to a different folder or repository? 
8. Explain how can we access MAINFRAME tables in INFORMATICA as a source ?
9. How do we load data by using period dimension?
10. Explain how is the union transformation active transformation?
11. How to implement de-normalization concept in Informatica Mappings? 
12. Using unconnected lookup, how we you remove nulls and duplicates? 
13. What are the data movement modes in Informatica ? 
14. What are the properties should be notified when we connect the flat file source definition to relational database target definition?
15. Explain what are two types of processes that Informatica runs the session? 
16. What do you mean by a surrogate key in Informatica ?
17. Can you tell me what do you mean by a rank transformation?where can we use this transformation?
18. What is the difference between constraints based load ordering and target load plan?
19. What is the limit to the number of sources and targets you can have in a mapping
20. Can you tell me what do you mean by version controlling? 
21. Why we use stored procedure transformation in Informatica? 
22. Explain how can we delete duplicate rows from flat files ?
23. How can we load from PL/SQL script into Informatica mapping?
24. How many Data marts are there in your project? 
25. How can you import oracle sequence into Informatica?
26. If you are workflow is running slow in Informatica. Where do you start trouble shooting and what are the steps you follow?
27. What is a time dimension? Please provide an example.
28. What do you mean by Dynamic Data Store. Explain? 
29. Can you tell me what do you mean dynamic insert in Informatica?
30. What do you mean by re-dimension in Informatica?
31. Explain what is the difference between dynamic and static lookup?
32. What do you mean by DTM session in Informatica?

Read More »

Informatica Tool Job Interview Questions.

Professionals are invited to share the answers for the questions cited below.

1. Explain what is the logic will you implement to load data into a fact table from n dimension tables?
2. What do you mean by workflow in Informatica?
3. Why you use repository connectivity and when?
4. Can you generate reports in Informatica ? 
5. Explain how can we delete duplicate rows from your source in mapping without using SELECT DISTINCT option in source qualifier?
6. How many dimension tables did you had in your project and name some dimensions (columns)?
7. Explain how to join two tables without using the Joiner Transformation.
8. We can insert or update the rows without using the update strategy. Then what is the necessity of the update strategy?
9. Explain in brief what is a transformation in Informatica?
10. What is E-R modelling and why is it used for OLTP design what is dimensional modelling and why is it used for data warehouse design?
11. What is the use of redo log file in Informatica ?.
12. Differentiate difference between Enterprise data warehouse and a data warehouse?
13. What do you mean by worklet and what use of worklet and in which situation we can use it?
14. With out using Update strategy and sessions options, how we can do the update our target table?
15. Can you look up a flat file, Please explain the process. What is test load?
16. How can we delete the cache files while we using aggregator transformation?
17. How can we remove the staging area in Informatica?
18. How many Fact and Dimension tables are there in your project?
19. How to load data to target where the source and targets are XML'S?
20. If you have four lookup tables in the workflow. How do you troubleshoot effectively to improve performance?
21. Under what circumstances Informatica server results an unrecoverable session? 
22. Explain what do you mean by transformation?
23. What do you mean by Event-Based Scheduling in Informatica?
24. Explain what is referential Integrity error? What steps will you take to rectify the error?
25. Without the use of ETL tool can you prepare a Data Warehouse and maintain it?
26. Can you look up a flat file using Informatica?
27. How can we display delimiters beside column names of target flat file?
28. Explain how do you add and delete header, footer records from flat file during load to oracle?
29. How many joiner transformations needed to join 10 different sources?

Read More »

Informatica Interview Questions, Data Warehouse Questions and Answers on Informatica.

Flat file is having 10 records as input and I want to push 5 records to the target?
Ans : one variable with the name $$count at mapping variable with a Count aggregation type and initial value for that $$count=0. Create one expression and use SETCOUNTVARIABLEv_count=SetCountVariable ($$Count) Create one filter after expression and put condition v_count <=5 

Explain what is the difference between source qualifier transformation and application source qualifier transformation?
Ans: The Source qualifier transformation extracts data from RDBMS or from a single flat file system. Application source qualifier transformation extracts data from application sources like ERP.

Explain what is a mapplet in Informatica?
Ans: A mapplet is nothing but a reusable object that is created by using mapplet designer. The mapplet contains set of transformations which allows us to reuse that transformation logic in multiple mappings.

Explain what does reusable transformation mean in Informatica?
Ans: Reusable transformations can be used multiple times in a mapping. The reusable transformation is stored as a metadata separate from any other mapping that uses the transformation. Whenever any changes to a reusable transformation are made, all the mappings where the transformation is used will be invalidated.

Explain how can we create primary key only on odd numbers?
Ans: To create primary key, we use sequence generator and set the 'Increment by' property of sequence generator to 2.

What do you mean by a authenticator?
Ans: It validates user name and password to access the PowerCenter repository.

What is the use of auxiliary mapping?
Ans: Auxiliary mapping reflects change in one table whenever there is a change in the other table.

How the Informatica server sorts the string values in Rank transformation?
Ans: When the Informatica server runs in the ASCII data movement mode it sorts session data using Binary sort order. If you configure the session to use a binary sort order, the informatica server calculates the binary value of each string and returns the specified number of rows with the highest binary values for the string.

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

What are the rank caches?
Ans: During the session, the Informatica server compares an input row with rows in the data cache. If the input row out-ranks a stored row, the Informatica server replaces the stored row with the input row. The informatica server stores group information in an index cache and row data in a data cache.

What is the Rank index in Rank transformation?
Ans: The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For example, if you create a Rank transformation that ranks the top 5 salespersons for each quarter, the rank index numbers the salespeople from 1 to 5.
Explain what is the difference between Normal load and Bulk load?
Ans: The difference between Normal Load and Bulk Load is cited below.

Normal Load: Normal load will write information to the database log file so that if any recovery is needed it is will be helpful. when the source file is a text file and loading data to a table, in such cases we should you normal load only, else the session will be failed.

Bulk Mode: Bulk load will not write information to the database log file so that if any recovery is needed we can't do any thing in such cases.
comparatively Bulk load is pretty faster than normal load.

Read More »

Informatica Interview Questions asked in MNCs.

Professionals are invited to share the answers for the questions cited below.

1. Explain what are the joiner caches in Informatica?
2. What are the different types of mapping in Getting Started Wizard?
3. Explain what is the difference between constraint based commit and target based commit?Under what circumstances we use constraint based?
4. What do you mean by metadata reporter in Informatica? 
5. Differentiate between summary filter and detail filter.
6. Explain what is the use of incremental aggregation? Explain me in brief with an example.
7. Can you tell me where do we use LKP b/n source to stage or stage to target?
8. Why and where do we use factless fact table?
9. Can we create duplicate rows in star schema, If yes then how?
10. Explain in detail about Performance tuning in Informatica?
11. Explain how can you say that union Transformation is Active transformation.
12. How do you delete duplicate rows in flat files source is any option in Informatica?
13. How do you create header and footer in target using Informatica?
14. What do you mean by a look up function? What is default transformation for the look up function?
15. Define Micro Strategy? Why is it used for? Can you explain in detail about it?
16. Explain what is the difference between truncate and delete statements?
17. What is the use of Shared Folder in Informatica?
18. Where we use Star Schema & where Snowflake?
19. Why can't we use normalizer transformation in mapplet?
20. Can we eliminate duplicate rows by using filter and router transformation ?if so explain me in detail .
21. Explain in brief about pmcmd in Informatica?
22. What do you mean by a junk dimension in Informatica?
23. How can you test mapping and what is associate port?
24. How do you do aggregation with out using AGGREGAROR Transformation ?
25. What are the locks with respect to mappings? How do you manually lock or unlock the mappings for changes?
26. Explain how can you stop a batch in Informatica? 
27. What is difference between COM  and DCOM?
28. How do you setup a schedule for data loading from scratch in Informatica?
29. Can you mention the various test procedures to check for whether the data is loaded in the backend, performance of mapping, and quality of the data loaded in Informatica
30. What are the active and passive transformations in Informatica? 
31. Explain what are the types of mapping wizards that are to be provided in Informatica? 

Read More »

Data Warehousing Interview Questions, Informatica Interview Questions and Answers.

What is the dissimilarity between the start and abort?
Ans: There are various session in one batch. If you want to stop a session which is a part of a batch then you will have to use the stop command. If the batch is nested in another batch then you will have to stop the outer most batches. The abort function provides the time out of 60 second. If the processor or the server is not able to finish the job during this time then it will be automatically aborted.

How to delete duplicate record in Informatica
Ans : Following are ways to remove duplicate records
1. In source qualifier use select distinct
2. Use Aggregator and group by all fields
3. Override SQL query in Source qualifier

What is DTM process?
Ans: After the load manger performs validations for session, it creates the DTM process. DTM is to create and manage the threads that carry out the session tasks. Master thread creates and manages all the other threads.

What are the data movement modes in Informatica?
Ans: Data movement modes determine how Informatica server handles the character data. U choose the data movement in the Informatica server configuration settings.
Two types of data movement modes available in Informatica. ASCII mode Unicode mode

Why do we use the lookup transformation? How can we improve session performance in aggregator transformation?
Ans: A lookup transformation is used for checking the matched values from the source or target tables and check whether the record already existing in the table. It is also used for updating the slowly changing dimensions and also performs some calculations.
Using Incremental Aggregation we create Sorted Input option to improve the performance since performance is reduced using the caches.

What are the types of metadata that stores in repository?
Ans: Following are the types of metadata that is stored in the repository.
Database connections, Global objects, Mappings, Mapplets, Multidimensional metadata, Reusable transformations, Sessions and batches, Short cuts, Source definitions, Target definitions, Transformations

Where should you place the flat file to import the flat file definition to the designer?
Ans: Place it in local folder.

To provide support for Mainframes source data, which files are used as a source definitions?
Ans: COBOL files

Which transformation should you need while using the Cobol sources as source definitions?
Ans: Normalizer transformation which is used to normalize the data. Since COBOL sources are often consists of Demnormalized data.

How can you create or import flat file definition in to the warehouse designer?
Ans: You can not create or import flat file definition in to warehouse designer directly. Instead of that you must analyze the file in source analyzer and then drag it into the warehouse designer. When you drag the flat file source definition into warehouse designer workspace, the warehouse designer creates a relational target definition not a file definition. If you want to load to a file, configure the session to write to a flat file. When the informatica server runs the session, it creates and loads the flat file.

What is incremental aggregation?
Ans: When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you can capture changes, you can configure the session to process only those changes. This allows the Informatica Server to update your target incrementally, rather than forcing it to process the entire source and recalculate the same calculations each time you run the session.

What is throughput in Informatica? Where can we find this option to check? How does it works?
Ans: Throughput is the rate at which power centre server read the rows in bytes from source or write the rows in bytes into the target per second.We can find this option in workflow monitor.Its working is as follows: right click on session choose properties and transformation statistics tab, there we can find throughput details for each instance of source and target.

Under what circumstances does informatica server create Reject files?
Ans: 1.When it encounters the DD_Reject in update strategy transformation.
2.Violates database constraint in the rows (truncated or overflowed)

Read More »