Informatica Interview Questions & Answers

Question 1. What Can We Do To Improve The Performance Of Informatica Aggregator Transformation?

Answer :

Aggregator performance improves dramatically if records are sorted before passing to the aggregator and "sorted input" option under aggregator properties is checked. The record set should be sorted on those columns that are used in Group By operation.

It is often a good idea to sort the record set in database level e.g. inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.

Question 2. How To Delete Duplicate Row Using Informatica?

Answer :

Assuming that the source system is a Relational Database, to eliminate duplicate records, we can check the Distinct option of the Source Qualifier of the source table and load the target accordingly.

Question 3. What Are The Different Lookup Cache?

Answer :

Lookups can be cached or uncached (No cache). Cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built and it remains same during the session run. On the other hand, A dynamic cache is refreshed during the session run by inserting or updating the records in cache based on the incoming source data.

A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after session run is complete or not respectively.

Question 4. How Can We Update A Record In Target Table Without Using Update Strategy?

Answer :

A target table can be updated without using 'Update Strategy'. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as "Update as Update" and check the "Update" check-box.

Let's assume we have a target table "Customer" with fields as "Customer ID", "Customer Name" and "Customer Address". Suppose we want to update "Customer Address" without an Update Strategy. Then we have to define "Customer ID" as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

Question 5. What Are The Differences Between Connected And Unconnected Lookup?

Answer :

Question 6. What Is The Difference Between Router And Filter?

Answer :

Question 7. What Type Of Repositories Can Be Created Using Informatica Repository Manager?

Answer :

Informatica PowerCenter includeds following type of repositories :

Standalone Repository : A repository that functions individually and this is unrelated to any other repositories.

Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.

Local Repository : Local repository is within a domain and it’s not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

Versioned Repository : This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

Question 8. What Is A Code Page?

Answer :

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 data. 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.

Question 9. Which All Databases Powercenter Server On Windows Can Connect To?

Answer :

PowerCenter Server on Windows can connect to following databases:

1. IBM DB2
2. Informix
3. Microsoft Access
4. Microsoft Excel
5. Microsoft SQL Server
6. Oracle
7. Sybase
8. Teradata

Question 10. Which All Databases Powercenter Server On Unix Can Connect To?

Answer :

PowerCenter Server on UNIX can connect to following databases:

1. IBM DB2
2. Informix
3. Oracle
4. Sybase
5. Teradata

Question 11. Explain Use Of Update Strategy Transformation?

Answer :

To flag source records as INSERT, DELETE, UPDATE or REJECT for target database. Default flag is Insert. This is must for Incremental Data Loading.

This is the important transformation,is used to maintain the history data or just most recent changes into the target table.

We can set or flag the records by using these two levels.

1. Within a session :
   When you configure the session,you can instruct the informatica server to either treat all the records in the same way.
2. Within a mapping :
   within a mapping we use update strategy transformation to flag the records like insert,update,delete or reject.

Question 12. What Are The Measure Objects?

Answer :

Aggregate calculation like sum,avg,max,min these are the measure objetcs.

Question 13. Discuss The Advantages & Disadvantages Of Star & Snowflake Schema?

Answer :

In a STAR schema there is no relation between any two dimension tables, whereas in a SNOWFLAKE schema there is a possible relation between the dimension tables.

In star schema there is no relationship between two relational tables. All dimensions are de-normalized and query performance is degrades. In this snow flake schema dimensions are normalized. In this SF schema table space is increased. Maintenance cost is high. Query performance is increased.

Question 14. What Is The Method Of Loading 5 Flat Files Of Having Same Structure To A Single Target And Which Transformations I Can Use?

Answer :

Two Methods.

1. write all files in one directory then use file repository concept (dont forget to type source file type as indirect in the session).
2. use union t/r to combine multiple input files into a single target.

Question 15. Compare Data Warehousing Top-down Approach With Bottom-up Approach.

Answer :

Top down
ODS-->ETL-->Datawarehouse-->Datamart-->OLAP

Bottom up
ODS-->ETL-->Datamart-->Datawarehouse-->OLAP

Question 16. Why We Use Partitioning The Session In Informatica?

Answer :

Performance can be improved by processing data in parallel in a single session by creating multiple partitions of the pipeline.

Informatica server can achieve high performance by partitioning the pipeline and performing the extract , transformation, and load for each partition in parallel.

Question 17. What Is The Router Transformation?

Answer :

A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. However, a Filter transformation tests data for one condition and drops the rows of data that do not meet the condition. A Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output group.

If you need to test the same input data based on multiple conditions, use a Router Transformation in a mapping instead of creating multiple Filter transformations to perform the same task.

Question 18. How Do You Create A Mapping Using Multiple Lookup Transformation?

Answer :

Use unconnected lookup if same lookup repeats multiple times.

Question 19. Difference Between Summary Filter And Details Filter?

Answer :

Summary Filter - we can apply records group by that contain common values.

Detail Filter - we can apply to each and every record in a database.

Question 20. Can Informatica Be Used As A Cleansing Tool? If Yes, Give Example Of Transformations That Can Implement A Data Cleansing Routine.

Answer :

Yes, we can use Informatica for cleansing data. some time we use stages to cleansing the data. It depends upon performance again else we can use expression to cleasing data.

For example an feild X have some values and other with Null values and assigned to target feild where target feild is notnull column, inside an expression we can assign space or some constant value to avoid session failure.

The input data is in one format and target is in another format, we can change the format in expression.

we can assign some default values to the target to represent complete set of data in the target.

Question 21. How Can You Create Or Import Flat File Definition Into The Warehouse Designer?

Answer :

You cannot create or import flat file definition in to warehouse designer directly. Instead you must analyze the file in source analyzer, 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.

Question 22. Is A Fact Table Normalized Or De-normalized?

Answer :

A fact table is always DENORMALISED table. It consists of data from dimension table (Primary Key's) and Fact table has foreign keys and measures.

Question 23. How You Will Create Header And Footer In Target Using Informatica?

Answer :

If you are focus is about the flat files then one can set it in file properties while creating a mapping or at the session level in session properties.

Question 24. Explain About Informatica Server Architecture?

Answer :

Informatica server, load managers, data transfer manager, reader, temp server and writer are the components of informatica server. first load manager sends a request to the reader if the reader is ready to read the data from source and dump into the temp server and data transfer manager manages the load and it send the request to writer as per first in first out process and writer takes the data from temp server and loads it into the target.

Question 25. What Are The Basic Needs To Join Two Sources In A Source Qualifier?

Answer :

Basic need to join two sources using source qualifier:

1. Both sources should be in same database
2. The should have at least one column in common with same data types

Question 26. How Do You Configure Mapping In Informatica?

Answer :

• Import src from database.
• Check if target table already exists in database.
• If it exists make sure u delete data from it and import into designer.
• Or else create it with create target wizard.
• Now you can drag needed transformations into the workspace.
• Use them according to your purpose.

For improved performance follow these tips:-

• Use sorter before aggregator
• If filter is used keep it as near to the source as possible.
• If possible use an extra expression tr before target to make corrections in future.
• Enable sorted input option if sorter is used before agg tr.
• If more filters are needed use router tr.
• you can use source filter option of SQ if filter tr is immediately after source.
• In case of router if not needed do not connect default group to any target.

Question 27. How Many Types Of Dimensions Are Available In Informatica?

Answer :

The types of dimensions available are:

1. Junk dimension
2. Degenerative Dimension
3. Conformed Dimension

Question 28. What Is The Difference Between Filter And Lookup Transformation?

Answer :

1. Filter transformation is an Active transformation and Lookup is a Passive transformation.
2. Filter transformation is used to Filter rows based on condition and Lookup is used to look up data in a flat file or a relational table, view, or synonym.

Question 29. What Are The Joiner Caches?

Answer :

Specifies the directory used to cache master records and the index to these records. By default, the cached files are created in a directory specified by the server variable $PMCacheDir. If you override the directory, make sure the directory exists and contains enough disk space for the cache files. The directory can be a mapped or mounted drive. There are 2-types of cache in the joiner:

1. Data cache
2. Index Cache

Question 30. What Is The Difference Between Informatica 7.0 And 8.0 ?

Answer :

The basic difference between informatica 8.0 and informatica 7.0 is that in 8.0 series informatica corp has introduces powerexchnage concept.

Question 31. Which Is Better Among Connected Lookup And Unconnected Lookup Transformations In Informatica Or Any Other Etl Tool?

Answer :

If you are having defined source you can use connected, source is not well defined or from different database you can go for unconnected.

Connected and unconnected lookup depends on scenarios and performance If you are looking for a single value for look up and the value is like 1 in 1000 then you should go for unconnected lookup. Performance wise its better as we are not frequently using the transformation. If multiple columns are returned as lookup value then one should go for connected lookup.

Question 32. How To Read Rejected Data Or Bad Data From Bad File And Reload It To Target?

Answer :

Correction the rejected data and send to target relational tables using load order utility. Find out the rejected data by using column indicator and row indicator.

Question 33. Which Tasks Can Be Performed On Port Level(using One Specific Port)?

Answer :

I think unconnected Lookup or expression transformation can be used for single port for a row.

Question 34. Differences Between Normalizer And Normalizer Transformation.

Answer :

Normalizer : It is a transformation mainly using for cobol sources. It change the rows into columns and columns into rows.

Normalization : To remove the redundancy and inconsistency.

Normalizer Transformation : can be used to obtain multiple columns from a single row.

Question 35. In A Joiner Transformation, You Should Specify The Source With Fewer Rows As The Master Source. Why?

Answer :

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.

Joiner Transformation will cache Master table's data hence it is advised to define table with less #of rows as master.

Question 36. How To Import Oracle Sequence Into Informatica?

Answer :

Create one procedure and declare the sequence inside the procedure, finally call the procedure in informatica with the help of stored procedure transformation.

Question 37. How To Get The First 100 Rows From The Flat File Into The Target?

Answer :

create one procedure and declare the sequence inside the procedure, finally call the procedure in informatica with the help of stored procedure transformation.

Question 38. How To Load Time Dimension?

Answer :

We can use SCD Type 1/2/3 to load any Dimensions based on the requirement. We can also use procedure to populate Time Dimension

Question 39. What Is The Difference Between Informatics 7x And 8x And What Is Latest Version?

Answer :

Java Transformation available in the 8x version and it is not available in 7x version.

Question 40. How Do You Handle Decimal Places While Importing A Flatfile Into Informatica?

Answer :

while importing flat file definition just specify the scale for a numeric data type in the mapping, the flat file source supports only number datatype (no decimal and integer). In the SQ associated with that source will have a data type as decimal for that number port of the source.

source ->number datatype port ->SQ -> decimal datatype. Integer is not supported. hence decimal is taken care.

Import the field as string and then use expression to convert it, so that we can avoid truncation if decimal places in source itself.

Question 41. What Is Hash Table Informatica?

Answer :

In hash partitioning, the Informatica Server uses a hash function to group rows of data among partitions. The Informatica Server groups the data based on a partition key.Use hash partitioning when you want the Informatica Server to distribute rows to the partitions by group. For example, you need to sort items by item ID, but you do not know how many items have a particular ID number.

Question 42. What Is The Use Of Incremental Aggregation? Explain Me In Brief With An Example.

Answer :

Its a session option, when the informatica server performs incremental aggr. it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally for performance we will use it.

When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes incrementally and you can capture changes, you can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to process the entire source and recalculate the same data each time you run the session.

Question 43. How Can I Get Distinct Values While Mapping In Informatica In Insertion?

Answer :

You can add an aggregator before insert and group by the fields that need to be distinct.

Question 44. At The Max How Many Transformations Can Be Us In A Mapping?

Answer :

In a mapping we can use any number of transformations depending on the project, and the included transformations in the particular related transformations.

Question 45. What Is The Target Load Order?

Answer :

A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping.

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.

Question 46. How To Recover Sessions In Concurrent Batches?

Answer :

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 successfully, you can recover the session as a standalone session.

To recover a session in a concurrent batch:

1. Copy the failed session using Operations-Copy Session.
2. Drag the copied session outside the batch to be a standalone session.
3. Follow the steps to recover a standalone session.
4. Delete the standalone copy.

Question 47. What Is Difference Between Maplet And Reusable Transformation?

Answer :

Maplet :

• one or more transformations.
• set of transformations that are reusable.

Reusable transformation :

• only one transformation
• Single transformation which is reusable.

Question 48. What Is The Difference Between Stop And Abort?

Answer :

stop : If the session u want to stop is a part of batch you must stop the batch, if the batch is part of nested batch, Stop the outer most batch.

Abort : You can issue the abort command, it is similar to stop command except it has 60 second time out.

Question 49. How Can You Complete Unrecoverable Sessions?

Answer :

Under certain circumstances, when a session does not complete, you need to truncate the target tables and run the session from the beginning. Run the session from the beginning when the Informatica Server cannot run recovery or when running recovery might result in inconsistent data.

If there is no recovery mode on in session and workflow failed in mid of execution then

1. Don’t truncate table immediately.
2. If there is large volume of data is performing by the load and more than 25% data has loaded then-if same workflow has multiple session then check particular session which caused to be failed and fire the delete command only to delete particular session data which has loaded and copy the session into new workflow and run only that session or dependent others.

Question 50. What Are Partition Points?

Answer :

Partition points mark the thread boundaries in a source pipeline and divide the pipeline into stages.

Question 51. What Is A Source Qualifier?

Answer :

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 represents the rows that the Informatica Server reads when it executes a session.

Question 52. How Do We Estimate The Number Of Partitions That A Mapping Really Requires? Is It Dependent On The Machine Configuration?

Answer :

It depends upon the informatica version we are using. suppose if we are using informatica 6 it supports only 32 partitions where as informatica 7 supports 64 partitions.

Question 53. How Can You Improve Session Performance In Aggregator Transformation?

Answer :

One way is supplying the sorted input to aggregator transformation. In situations where sorted input cannot be supplied, we need to configure data cache and index cache at session/transformation level to allocate more space to support aggregation.

Question 54. What Is The Difference Between Summary Filter And Detail Filter?

Answer :

Summary filter can be applieid on a group of rows that contain a common value. whereas detail filters can be applied on each and every rec of the data base.

Question 55. What Is The Default Source Option For Update Strategy Transformation?

Answer :

Default option for update strategy transformation is dd_insert or we can put '0' in session level data driven.

Question 56. What Is Meant By Aggregate Fact Table And Where Is It Used?

Answer :

Basically fact tables are two kinds.

1. Aggregated fact table and
2. Factless fact table.

Aggregated fact table has aggregarted columns. for eg. Total-Sal, Dep-Sal. where as in factless fact table will not have aggregated columns and it only has FK to the Dimension tables.

Question 57. Can You Start A Batches With In A Batch?

Answer :

You cannot. 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.

Question 58. What Is The Default Join That Source Qualifier Provides?

Answer :

Inner equi join.

Question 59. What Are The Difference Between Joiner Transformation And Source Qualifier Transformation?

Answer :

You can join heterogeneous data sources in joiner transformation which we cannot achieve in source qualifier transformation.

You need matching keys to join two relational sources in source qualifier transformation. Whereas you doesn’t need matching keys to join two sources.

Two relational sources should come from same data source in source qualifier. You can join relational sources which are coming from different sources also.

Question 60. How The Informatica Server Increases The Session Performance Through Partitioning The Source?

Answer :

For a relational sources informatica server creates multiple connections for each partition of a single source and extracts separate range of data for each connection.

Informatica server reads multiple partitions of a single source concurrently. Similarly for loading also informatica server creates multiple connections to the target and loads partitions of data concurrently.

For XML and file sources, informatica server reads multiple files concurrently. For loading the data informatica server creates a separate file for each partition (of a source file). You can choose to merge the targets.

Question 61. How Can We Use pmcmd Command In A Workflow Or To Run A Session

Answer :

By using command in the command task there is a option pression. we can write appropriate command of pmcmd to run workflow.

Question 62. Doubts Regarding Rank Transformation: Can We Do Ranking Using Two Ports? Can We Rank All The Rows Coming From Source, How?

Answer :

When ETL load the data from source we can declare the rank of the incoming data to pass a rank transformation. We can't declare two rank on a single source data. We can do rank the row by declaring the rank Transformation and declaring the rank port.

Question 63. Define Informatica Repository?

Answer :

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.

Question 64. What Are The Methods For Creating Reusable Transformations?

Answer :

There two methods for creating reusable transformations:

1. Using transformation developer tool.
2. Converting a non reusable transformation into a reusable transformation in mapping.

Question 65. What Is The Difference Between Normal Load And Bulk Load?

Answer :

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.

Question 66. What Is The Difference Between Informatica Powercenter Server, Repositoryserver And Repository?

Answer :

Repository is a database in which all informatica components are stored in the form of tables. The repository server controls the repository and maintains the data integrity and Consistency across the repository when multiple users use Informatica. Powercenter Server/Infa Server is responsible for execution of the components (sessions) stored in the repository.

Question 67. How Can You Access The Remote Source Into Your Session?

Answer :

Relational source : To access relational source which is situated in a remote place , you need to configure database connection to the datasource.

FileSource : To access the remote source file you must configure the FTP connection to the host machine before you create the session.

Heterogeneous : When you are mapping contains more than one source type, the server manager creates a heterogeneous session that displays source options for all types.

Question 68. What Is The Difference Between Connected And Unconnected Stored Procedures?

Answer :

Unconnected: The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

connected: The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.

Question 69. What Is Source Qualifier Transformation?

Answer :

When you add a relational or a flat file source definition to a mapping 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.

Question 70. To Provide Support For Mainframes Source Data,which Files Are Used As A Source Definitions?

Answer :

COBOL Copy-book files.

Question 71. How Many Types Of Facts Are There And What Are They?

Answer :

There are three types of facts

1. Additive fact: a fact which can be summarized by any one of dimension or all dimensions EX: QTY, REVENUE
2. Semi additive fact: a fact which can be summarized for few dimensions not for all dimensions. ex: current balance
3. Non additive fact: a fact which cannot be summarized by any of dimensions. ex: percentage of profit

Question 72. Can We Run A Group Of Sessions Without Using Workflow Manager.

Answer :

It is possible two run two session only (by precession, post session) using pmcmd without using workflow. Not more than two.

Question 73. If You Want To Create Indexes After The Load Process Which Transformation You Choose?

Answer :

Its usually not done in the mapping (transformation) level. Its done in session level. Create a command task which will execute a shell script (if Unix) or any other scripts which contains the create index command. Use this command task in the workflow after the session or else, You can create it with a post session command.

Question 74. How Do I Import Vsam Files From Source To Target. Do I Need A Special Plugin

Answer :

As far my knowledge by using power exchange tool convert VSAM file to oracle tables then do mapping as usual to the target table.

Question 75. How Can We Partition A Session In Informatica?

Answer :

The Informatica PowerCenter Partitioning option optimizes parallel processing on multiprocessor hardware by providing a thread-based architecture and built-in data partitioning.

GUI-based tools reduce the development effort necessary to create data partitions and streamline ongoing troubleshooting and performance tuning tasks, while ensuring data integrity throughout the execution process. As the amount of data within an organization expands and real-time demand for information grows, the PowerCenter Partitioning option enables hardware and applications to provide outstanding performance and jointly scale to handle large volumes of data and users.

Question 76. What Is The Procedure To Load The Fact Table. Give In Detail?

Answer :

Based on the requirement to your fact table, choose the sources and data and transform it based on your business needs. For the fact table, you need a primary key so use a sequence generator transformation to generate a unique key and pipe it to the target (fact) table with the foreign keys from the source tables.

Question 77. What Are The Differences Between Informatica Power Center Versions 6.2 And 7.1, Also Between Versions 6.2 And 5.1?

Answer :

The main difference between informatica 5.1 and 6.1 is that in 6.1 they introduce a new thing called repository server and in place of server manager(5.1), they introduce workflow manager and workflow monitor.

Question 78. Why We Use Stored Procedure Transformation?

Answer :

A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate time-consuming tasks that are too complicated for standard SQL statements.

Question 79. What Is Difference Between Partitioning Of Relational Target And Partitioning Of File Targets?

Answer :

Partition's can be done on both relational and flat files.
Informatica supports following partitions

1. Database partitioning
2. RoundRobin
3. Pass-through
4. Hash-Key partitioning
5. Key Range partitioning

All these are applicable for relational targets. For flat file only database partitioning is not applicable.

Informatica supports Navy partitioning. you can just specify the name of the target file and create the partitions, rest will be taken care by informatica session.

Question 80. What Are Two Modes Of Data Movement In Informatica Server?

Answer :

The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and code page validation in the Informatica Client and Server.

1. Unicode - IS allows 2 bytes for each character and uses additional byte for each non-ascii character (such as Japanese characters)
2. ASCII - IS holds all data in a single byte.The IS data movement mode can be changed in the Informatica Server configuration parameters. This comes into effect once you restart the Informatica Server.

Question 81. What Are Main Advantages And Purpose Of Using Normalizer Transformation In Informatica?

Answer :

Narmalizer Transformation is used mainly with COBOL sources where most of the time data is stored in de-normalized format. Also, Normalizer transformation can be used to create multiple rows from a single row of data.

1. Normalizer Transformation read the data from COBOL Sources.
2. It support Horizontal Pivot .It is a processing of single input into a multiple output

Question 82. What Is Change Data Capture?

Answer :

Change data capture (CDC) is a set of software design patterns used to determine the data that has changed in a database so that action can be taken using the changed data.

Question 83. How To Delete Duplicate Rows In Flat Files Source Is Any Option In Informatica?

Answer :

Use a sorter transformation, in that you will have a "distinct" option make use of it .

Question 84. What Is Update Strategy Transformation ?

Answer :

The model you choose constitutes your update strategy, how to handle changes to existing rows. In PowerCenter and PowerMart, you set your update strategy at two different levels:

Within a session : When you configure a session, you can instruct the Informatica Server to either treat all rows in the same way (for example, treat all rows as inserts), or use instructions coded into the session mapping to flag rows for different database operations.

Within a mapping : Within a mapping, you use the Update Strategy transformation to flag rows for insert, delete, update, or reject. update strategy is used to update the target

Question 85. Discuss Which Is Better Among Incremental Load, Normal Load And Bulk Load.

Answer :

It depends on the requirement. Otherwise Incremental load which can be better as it takes one that data which is not available previously on the target.

According to performance bulk is better than normal. But both having some conditions in source data.

Conditions are like:

1. Does not contain any constraint in data.
2. Dont use the double datatype if necessary to use then use it as last row of the table.
3. It does not support the CHECK CONSTRAINT.

Question 86. What Are The Unsupported Repository Objects For A Mapplet?

Answer :

• COBOL source definition
• Joiner transformations
• Normalizer transformations
• Non reusable sequence generator transformations.
• Pre or post session stored procedures
• Target definitions
• Power mart 3.5 style Look Up functions
• XML source definitions
• IBM MQ source defintions.

Question 87. What Is Data Merging, Data Cleansing, Sampling?

Answer :

Cleansing: TO identify and remove the redundancy and inconsistency.

sampling: just sample the data through send the data from source to target.

Data merging: It is a process of combining the data with similar structures in to a single output.

Data Cleansing: It is a process of identifying and rectifying the inconsistent and inaccurate data into consistent and accurate data.

Data Sampling: It is the process of sample by sending the data from source to target.

Question 88. How The Informatica Server Sorts The String Values In Ranktransformation?

Answer :

When Informatica Server runs in UNICODE data movement mode ,then it uses the sort order configured in session properties.

We can run informatica server either in UNICODE data moment mode or ASCII data moment mode.

Unicode mode: In this mode informatica server sorts the data as per the sorted order in session.

ASCII Mode: In this mode informatica server sorts the date as per the binary order.

Question 89. Difference Between Static And Dynamic Cache And Explain With One Example?

Answer :

Static - Once the data is cached , it will not change, example unconnected lookup uses static cache.

Dynamic - The cache is updated as to reflect the update in the table (or source) for which it is reffering to.(ex. connected lookup).

Question 90. How To Join Two Tables Without Using The Joiner Transformation?

Answer :

Its possible to join the two or more tables by using source qualifier. But provided the tables should have relationship.

When you drag and drop the tables you will getting the source qualifier for each table. Delete all the source qualifiers. Add a common source qualifier for all. Right click on the source qualifier you will find EDIT click on it. Click on the properties tab, you will find sql query in that you can write your sqls.

You can also do it using Session --- mapping---source there you have an option called User Defined Join there you can write your SQL.

Question 91. What Is Difference Between Informatica 7.1 And Abinitio?

Answer :

There is a lot of difference between informatica and AbInitio:

• In AbInitio we are using 3 parallelism but Informatica using 1 parallelism.
• In Ab Initio no scheduling option we can schedule manually or pl/sql script but informatica contains 4 scheduling options.
• In Ab Inition contains co-operating system but informatica is not.
• Ramp time is very quickly in Ab Initio compare than Informatica.
• Ab Initio is user friendly than Informatica.

Question 92. What Is Meant By Direct And Indirect Loading Options In Sessions?

Answer :

1. Direct loading can be used to Single transformation where as indirect transformation can be used to multiple transformations or files.
2. In the direct we can perform recovery process but in Indirect we cant do it .

Question 93. When We Create A Target As Flat File And Source As Oracle. How Can I Specify First Rows As Column Names In Flat Files?

Answer :

Use a pre sql statement., but this is a hard coding method. If you change the column names or put in extra columns in the flat file, you will have to change the insert statement.

You can also achieve this by changing the setting in the Informatica Repository manager to display the columns heading. The only disadvantage of this is that it will be applied on all the files that will be generated by this server.