Top 20 Informatica Interview Questions with Answers:Part-1

1]       HOW CAN YOU RECOGNIZE WHETHER OR NOT THE NEWLY ADDED ROWS IN THE SOURCE ARE GETS INSERT IN THE TARGET?
Answer: You just have to check with sql's or you can check by making the changes in mappings in sessions for target and also depends on the mapping which you are goiing to use the transformations.

2]       WHAT IS THE DIFFERENCE BETWEEN INFORMATICA 7.0 AND 8.0?
Answer: The basic diff b/w in between informatica 8.0 and informatica7.0 is that in 8.0series informatica corp has introduces powerexchnage concept.

3]       PERFORMANCE TUNING IN INFORMATICA?
Answer: The goal of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server.Increase the session performance by following. The performa.


4]       WHAT IS THE TARGET LOAD ORDER?
Answer: You specify the target loadorder based on source qualifiers in a maping.If you have the multiple source qualifiers connected to the multiple targets,You can designatethe order in which informatica ser.

5]       DIFF BETWEEN STATIC AND DYNAMIC CACHE? AND PLEASE EXPLAIN WITH ONE EXAMPLE?
Answer: Difference between static and dynamic cache- 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 upda.

6]       HOW DO WE DO UNIT TESTING IN INFORMATICA? HOW DO WE LOAD DATA IN INFORMATICA?
Answer: Unit testing are of two types 1. Quantitaive testing 2.Qualitative testing Steps. 1.First validate the mapping 2.Create session on themapping and then run workflow. Once the session.

7]     IS SORTER AN ACTIVE OR PASSIVE TRANSFORMATION? WHAT HAPPENS IF WE UNCHECK THE DISTINCT OPTION IN SORTER? WILL IT BE UNDER ACTIVE OR PASSIVE TRANSFORMATION?
Answer: Sorter is an active transformation. if you don't check the distinct option it is considered as a passive transformation. becos this distinct option eliminates the duplicate records from the table.

8]     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 comma.

9]     HOW MANY TYPES OF DIMENSIONS ARE AVAILABLE IN INFORMATICA?
Answer: There r 3 types of dimensions
 1.star schema
2.snowflake schema
3.glaxy schema

10]     WHAT IS SOURCE QUALIFIER TRANSFORMATION?
Answer: SQ transformation is a transformation which is automatically generated to read data from source tables into informatica designer.

11]     CAN ANYONE EXPLAIN ERROR HANDLING IN INFORMATICA WITH EXAMPLES SO THAT IT WILL BE EASY TO EXPLAIN THE SAME IN THE INTERVIEW?
Answer: Go to the session log file there we will find the information regarding to the session initiation process, errors encountered. load summary. so by seeing the errors encountered during the.

12]     DIFFERENCE BETWEEN RANK AND DENSE RANK?
Answer: Rank: 1 2<--2nd position 2<--3rd position 4 5 Same Rank is assigned to same totals/numbers. Rank is followed by the Position. Golf game ususally Ranks this way. This is usually a Gold Ranking.

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

14]     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 si.


15]     WHEN DO U WE USE DYNAMIC CACHE AND WHEN DO WE USE STATIC CACHE IN AN CONNECTED AND UNCONNECTED LOOKUP TRANSFORMATION
Answer: We use dynamic cache only for connected lookup. We use dynamic cache to check whether the record already exists in the target table are not. And depending on that, we insert,update or delete the recor
16]     ON A DAY, I LOAD 10 ROWS IN MY TARGET AND ON NEXT DAY IF I GET 10 MORE ROWS TO BE ADDED TO MY TARGET OUT OF WHICH 5 ARE UPDATED ROWS HOW CAN I SEND THEM TO TARGET? HOW CAN I INSERT AND UPDATE THE RECORD?
Answer: We can use do this by identifying the granularity of the target table . We can use CRC external procedure after that to compare newly generated CRC no. with the old one and if they do not match then.


17]     IF A SESSION FAILS AFTER LOADING OF 10,000 RECORDS IN TO THE TARGET. HOW CAN U LOAD THE RECORDS FROM 10001 THE RECORD WHEN U RUN THE SESSION NEXT TIME?
Answer: As explained above informatcia server has 3 methods to recovering the sessions.Use performing recovery to load the records from where the session fails.

18]     WHY DID YOU USE STORED PROCEDURE IN YOUR ETL APPLICATION?
Answer: Usage of stored procedure has the following advantages 1 checks the status of the target database 2 drops and recreates indexes 3 determines if enough space exists in the database 4 perfor.

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

20]     HOW TO MOVE THE MAPPING FROM ONE DATABASE TO ANOTHER?

Answer: 1. Open the mapping you want to migrate. Go to File Menu - Select 'Export Objects' and give a name - an XML file will be generated. Connect to the repository where you want to migrate and then select.

explain index cache and data cache?

The informatica server stores conditions values in the index cache and output values in the data cache.

what is look up transformation ?

look up transformation can be used in a table view based on condition by default lookup is left outer join

65)  why use the lookup transformation?

To perform the following tasks.

Get a related value.For example if your table includes employee ID,but you want to include such as gross sales per invoice or sales tax but not the calculated value(such as net sales)

Update slowly changing dimension tables. You can use a lookup transformation to determine whether records already exist in the target.

66)  what are the types of lookup?

Connected and unconnected

explain joiner transformation? Difference between connected and unconnected transformations.

Joiner transformation joins two related heterogeneous sources residing in different locations or files.

--What are the types of joins in joiner in the joiner transformation?

Normal

Master outer

Detail outer

Full outer

Difference between connected and unconnected transformations.

*   Connected transformation is connected to another transformation with in a mapping.

* Unconnected transformation is not connected to any transformation with in a mapping.

what r the types of groups in router transformation?

 Router transformation 2 groups 1. Input group 2. output groups.

Output groups in 2 types. 1. user defined group 2. default group.

difference between expression and aggregator transformation?

 Expression transformation calculate the single row values before writes the target.Expression transformation executed by row-by-row basis only.

Aggregator transformation allows you to perform aggregate calculations like max, min,avg…

Aggregate transformation perform calculation on groups.

what are the type of transformations?

2 types:

1) active

2) passive.

   -- explain active and passive transformation?

      Active transformation can change the number of rows that pass through it.No of output rows less than or equal to no of input rows.

       Passive transformation does not change the number of rows.Always no of output rows equal to no of input rows.

Difference filter and router transformation.

 Filter transformation to filter the data only one condition and drop the rows don’t meet the condition.

Drop rows does not store any ware like session log file..

Router transformation to filter the data based on multiple conditions and give yiou the option to route rows that don’t match to a default group.

Explain bulk loading?

You can use bulk loading to improve performance of a session that inserts a large amount of data to a db2,sysbase,oracle or MS SQL server database.

When bulk loading the power center server by passes the database log,which speeds performance.

With out writing to the database log, however the target database can’t perform rollback.As a result you may not be perform recovery.

what is a commit interval and explain the types?

A commit interval is the interval at which power center server commits data to targets during a session. The commit interval the number of rows you want to use as a basis for the commit point.

Target Based commit: The power center server commits data based on the number of target rows and the key constraints on the target table. The commit point also depends on the buffer block size and the commit interval.

Source-based commit:---------------------------------------------

User-defined commit:----------------------------------------------

Explain work flow manager tools & What is a worklet?

1)      Task developer.

2)      Work flow designer.

3)      Worklet designer.

What is a worklet?

Ans:

      A worklet is an object that represents a set of taske.It can contain any task available in the work flow manager. You can run worklets inside a workflow. You can also nest a worklet in another worklet.The worklet manager does not provide a parameter file for worklets.

The power center server writes information about worklet execution in the workflow log.

what is a parameter a file & what is a server ?

parameter a file :

Paramater file defines the values for parameter and variables.

WORKFLOW MANAGER.

Server :

The power center server moves data from source to targets based on a workflow and mapping metadata stored in a repository.

what is the mapplet & Difference between mapplet and reusable transformation?

Mapplet is a set of transformation that you build in the mapplet designer and you can use in multiple mappings.

Diffrence:

Reusable transformation can be a single transformation.Where as mapplet use multiple transformations.

What is the rank index in rank transformation?

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 sales persons for each quarter, the rank index number the salespeople from 1 to 5.

what is a reusable transformation?

  Reusable transformation can be a single transformation.This transformation can be used in multiple mappings.when you need to incorporate this transformation into mapping you add an instance of it to mapping.Later if you change the definition of the transformation, all instances of it inherit the changes.Since the instance of reusable transformation is a pointer to that transformation.U can change the transformation in the transformation developer, its instance automatically reflect these changes. This feature can save U great deal of work.

-- what are the methods for creating reusable transformation?

      Two methods

1)      Design it in the transformation developer.

2)      Promote a standard transformation from the mapping designer.After you add a transformation to the mapping, you can promote it to status of reusable transformation.

Once you promote a standard transformation to reusable status, you can demote it to a standard transformation at any time.

If u change the properties of a reusable transformation in mapping , you can revert it to the original reusable transformation properties by clicking the revert.

what is a tracing level?

Amount of information sent to log file.

Q) What are the types of tracing levels?

1.Normal

2.Terse

3.verbose data

4.verbose intitialization.

31)  can you connect more than one group to the same target or transformation?

Ans :  NO

what are the options in the trarget session of update strategy transformation?

options in the trarget session:

Insert

Delete

Update

Update as update

Update as insert

Update else insert

Truncate table.

what is a tracing level?

Amount of information sent to log file.

-- What are the types of tracing levels?

Normal,Terse,verbose data,verbose intitialization.

--Expalin sequence generator transformation?

-- can you connect multiple ports from one group to multiple transformations?

   Yes

When you use for dynamic cache.

Your target table is also look up table then you go for dynamic cache .In dynamic cache multiple matches return an error.use only = operator.

Explain Snowflake schema?

Snow flake schema is a normalize dimensions to eliminate the redundancy.The dimension data has been grouped into one large table. Both dimension and fact tables normalized.

Explain Star Schema?

Star Schema consists of one or more fact table and one or more dimension tables that are  related to foreign keys.   

   Dimension tables are De-normalized, Fact table-normalized

     Advantages: Less database space &  Simplify queries.

Explain Surrogate Key?

 Surrogate Key is a series of sequential numbers assigned to be a primary key for the table.

What is a staging area?

Staging area is a temporary storage area used for transaction, integrated and rather than transaction processing.

      When ever your data put in data warehouse you need to clean and process your data.

Difference between Power Center and Power Mart?

PowerMart was reduced functionality and reduced price versus PowerCenter. Since the introduction of PowerCenter 7 PowerMart is no longer sold. Instead PowerCenter is sold as a base product with extra funtionality such as partitioning, team-based development, etc. sold as options.

                                                                    OR

Power center receive all product functionality including ability to multiple register servers and metadata across the repository and partition data.

One repository multiple informatica servers. Power mart received all features except multiple register servers and partition data.

Explain ODS?

Operational data store is a part of data warehouse. This is maintained only current transactional data. ODS is subject oriented, integrated, volatile, current data.

Difference between OLTP and OLAP?

OLTP : IT is a Online Transaction Processing. This is maintained current transactional data. That means insert, update and delete must be fast.

OLAP(On-line Analytical Processing)): It deals with Historical Data or Archival Data. Historical data are those data that are archived over a long period of time.

Data from  OLTP are collected over a period of time and store it in a very large database called Data warehouse.

Type's of ETL's Used to Implementing Data Acquisition ?

There are 2 types of ETL's Used to implement Data Acquisition.

   1.Code Base.
   2.GUI Based ETL.

1.Code based ETL:
         An ETL application can be developed using some programming language such as SQL ,PL/SQL.

      Ex:- a.Oracle sql loader
             b.Sas based
             c.Sas access
             d.Tera Data load utilities
                               BTDQ(batch tera data query)
                                Fast load
                                Multi load
                                Fast export
                                            etc.....

2. GUI Based ETL:(we used such method in informatica)
              An ETL Application can be designed using simple graphically use interface ,Point & Technology.
             
     Ex:-a. Informatica(informatica corporation)
            b.Data storage(IBM Corp.)
            c.Data service(SAP)
            d.SSIS(Microsoft)-SQL Server Integration Services (SSIS)

Bill Inmon vs. Ralph Kimball

Kimball -Let everybody build what they want when they want it, we'll integrate it all when and if we need              to.(BOTTOM-UP APPROACH).
            Pros: fast to build, quick ROI, nimble
           Cons: harder to maintain as an enterprise resource, often redundant, often difficult to
           integrate data marts.

Inmon - Don't do anything until you've designed everything.(TOP-DOWN APPROACH)
             Pros: easy to maitain, tightly integrated
             Cons: takes way too long to deliver first projects, rigid

                                    There is no right or wrong between these two ideas, as they represent different data warehousing philosophies. In reality, the data warehouse systems in most enterprises are closer to Ralph Kimball's idea. This is because most data warehouses started out as a departmental effort, and hence they originated as a data mart. Only when more data marts are built later do they evolve into a data warehouse.

What is Data mart?

A subset of data warehouse is called Data mart.

Types:

  A. Dependent data mart :It gets data from datawarehouse.

  B. Independent data mart:it does not get data from datawrehouse.

Difference between data warehouse and an operational database ?

operational database:

1.It is designed to support business transnational processing.

2. Data is volatile .

3.Current Data .

4. Design for Running the business .   

5. Normalization .

6. More joins .

 Data warehouse :

1.It is designed to support Decision Making  process.
2. Data is Non-volatile .
3.Historical Data .
4. Design for Analyzing  the business .
5. De-Normalization .
6. Few joins .

What is Data warehousing ?

Data warehousing concepts (FAQ’s)
1)      What is Data warehouse?
Data warehouse is relational database used for query analysis and reporting. By definition data warehouse is Subject-oriented, Integrated, Non-volatile, Time variant.

Subject oriented     : Data warehouse is maintained particular subject.like HR,Sales,Loan ,etc.
Integrated               : Data collected from multiple sources integrated into a
                                   user readable unique format.
Non volatile             : Maintain Historical date.
Time variant            : data display the weekly, monthly, yearly.