Explore topic-wise InterviewSolutions in .

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.

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.

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 .

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.

There is a lot of difference between informatica and AbInitio:

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.

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.

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

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

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.

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.

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.

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.

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

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.

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:

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

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

Use a sorter TRANSFORMATION, in that you will have a "DISTINCT" OPTION MAKE use of it .

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

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

As far my knowledge by USING power EXCHANGE tool convert VSAM file to ORACLE tables then do mapping as USUAL to the target table.

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

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.

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.

It is POSSIBLE TWO run two SESSION only (by precession, post session) USING pmcmd without using WORKFLOW. Not more than two.

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

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

There are three types of facts

COBOL Copy-book FILES.

COBOL Copy-book files.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

There two METHODS for creating REUSABLE transformations:

1. USING transformation developer tool.
2. Converting a non reusable transformation into a reusable transformation in MAPPING.

There two methods for creating reusable transformations:

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.

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.

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.

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.

By using COMMAND in the command task there is a option PRESSION. we can WRITE appropriate command of pmcmd to RUN workflow.

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

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.

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.

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.

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.

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.

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.

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.

Basically fact tables are two kinds.

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.

Default OPTION for update strategy transformation is dd_insert or we can PUT '0' in session level DATA DRIVEN.

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

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.

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.

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.

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.

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.

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.

Partition points MARK the THREAD BOUNDARIES in a SOURCE pipeline and divide the pipeline into STAGES.

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

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.

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

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.

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.

MAPLET :

• one or more transformations.
• SET of transformations that are REUSABLE.

Reusable transformation :

• only one transformation
• Single transformation which is reusable.

Maplet :

Reusable transformation :

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.

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:

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.

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.

In a mapping we can USE any number of transformations DEPENDING on the project, and the INCLUDED transformations in the particular RELATED transformations.

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

You can add an AGGREGATOR before insert and GROUP by the fields that NEED to be DISTINCT.

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

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.

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.