Explore topic-wise InterviewSolutions in .

JAVA Transformation AVAILABLE in the 8X version and it is not available in 7x version.

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

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

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

CREATE one PROCEDURE and declare the SEQUENCE inside the procedure, finally call the procedure in informatica with the HELP of stored procedure TRANSFORMATION.

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

CREATE ONE procedure and declare the sequence inside the procedure, finally call the procedure in informatica with the HELP of STORED procedure TRANSFORMATION.

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

I THINK unconnected Lookup or EXPRESSION transformation can be USED for single PORT for a row.

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

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.

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.

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.

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.

The BASIC difference between INFORMATICA 8.0 and informatica 7.0 is that in 8.0 SERIES informatica corp has INTRODUCES powerexchnage CONCEPT.

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

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

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

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

For improved performance follow these tips:-

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Use UNCONNECTED lookup if same lookup repeats MULTIPLE TIMES.

Use unconnected lookup if same lookup repeats multiple times.

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.

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.

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.

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.

TOP down
ODS-->ETL-->Datawarehouse-->DATAMART-->OLAP

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

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

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

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.

Two Methods.

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.

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.

AGGREGATE CALCULATION LIKE sum,AVG,max,min these are the measure objetcs.

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

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.

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.

POWERCENTER SERVER on UNIX can CONNECT to following DATABASES:

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

PowerCenter Server on UNIX can connect to following databases:

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

PowerCenter Server on Windows can connect to following databases:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.