Informatica Interview Questions part 1

What is a source qualifier?

Source qualifier represents the data that the informatica server reads from the source.

What is a surrogate key?

http://prashanthobiee.blogspot.in/2012/12/surrogate-key.html

What is difference between Mapplet and reusable transformation?

http://prashanthobiee.blogspot.in/2012/12/difference-between-mapplet-and-reusable.html 

What is DTM session?

 http://prashanthobiee.blogspot.in/2012/12/dtm-session.html

What is a Mapplet?

http://prashanthobiee.blogspot.in/2012/12/mapplet.html

What is up date strategy and what are the options for update strategy?

http://prashanthobiee.blogspot.com/2012/12/update-strategy.html

What is subject area?

http://prashanthobiee.blogspot.in/2012/12/subject-area.html

What is the difference between truncate and delete statements?

Truncate is a type of DDL. No commit is needed. It resets the HWM and cleans up all the storage segments. No rollback is possible because of implicit commit. Delete can be rolled back, does not clean up the data segments so the HWM stays intact. Less efficient.

What kind of Update strategies are normally used (Type 1, 2 & 3) & what are the differences?
Type 1 – DD_UPDATE,
Type 2 – DD_UPDATE/DD_INSERT
Type 3 – DD_INSERT.

 What are bitmap indexes and how and why are they used?

 http://prashanthobiee.blogspot.in/2012/12/bitmap-indexes.html

What is bulk bind? How does it improve performance?

Bulk bind is used enhance the performance of PL/SQL fetch engine. It binds a variable so that the fetches are in bulk instead of streams of one row at a time.

What are the different ways to filter rows using Informatica transformations?

Source Qualifier (if using a homogeneous database or if the tables are in the same instance),Joiner, Filter, Router.

What is target load order?

 You specify in Informatica which target instance gets loaded first or in order.

What are the different transformations where you can use a SQL override?

Source Qualifier(If Relational)/Lookup (If Relational)

What is the difference between a Bulk and Normal mode and where exactly is it defined?
http://prashanthobiee.blogspot.in/2012/12/difference-between-bulk-and-normal-mode.html

What are data driven sessions?

http://prashanthobiee.blogspot.com/2012/12/data-driven-sessions.html

What are worklets and what is their use?

 Sessions can be bundled up together logically by subject area so that it can go in the workflow as one logical piece.

What is change data capture?

Change Data Capture is a mechanism of capturing changed data since the last load. Striva in mainframes. It involves reading database log files. Subscriber/Prescriber views. Triggers/Date fields in oracle.

What exactly is tracing level?

Tracing level is defined as amount of information which will go in Informatica log files.

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

In repository manager. Select all the then validate.

How can you join two or more tables without using the source qualifier override SQL or a Joiner transformation?
Using a view also you can use lookup(which is nothing but a outer join).

How many repositories can be created in Informatica?

N(Depends on your licensing agreement with Informatica)

How can you improve performance in an Aggregator transformation?

By checking sorted input and passing a sorted data through it.

How does the Informatica know that the input is sorted?     

Check the sorted port in Source Qualifier.

How many worklets can be defined within a workflow?

N.

If you join two or more tables and then pull out about two columns from each table into the source qualifier and then just pull out one column from the source qualifier into  an Expression transformation and then do a ‘generate SQL’ in the source qualifier how many columns will show up in the generated SQL.

One

In a Type 1 mapping with one source and one target table what is the minimum number of update strategy transformations to be used?

One

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

Thumb rule for and SQL joins and Informatica joiners. If you want to join n tables minimum number of joins you need is n-1.

While importing the relational source definition from database, what

are the Meta data of source you    import?

Source name, Database location, Column names, Data types, and Key constraints

How many ways you can update a relational source definition and what are they?

   1. Edit the definition

   2. Reimport the definition

Where should you place the flat file to import the flat file definition to the designer?

Place it in local folder

To provide support for Main frames source data, which files r used as a source  definitions?

COBOL files

Which transformation do you need while using the Cobol sources as source definitions?

Normalizer transformation which is  used to normalize the data.

Since Cobol sources often consist of De-normalized data.

How can you create or import flat file definition in to the warehouse designer?

You cannot create or import flat file definition into 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  work space, the

warehouse designer creates a relational target definition not a file definition.

If you want to load to a file, configure the session to write to a flat file.

When the Informatica server runs the session, it creates and loads  the flat file.

What is the Mapplet?

http://prashanthobiee.blogspot.in/2012/12/mapplet.html

What is a Transformation?

It is a repository object that generates, modifies or passes data.

What are the designer tools for creating transformations?

Mapping designer, Transformation developer, Mapplet designer

What are the active and passive transformations?

http://prashanthobiee.blogspot.in/2012/12/active-and-passive-transformations.html

What are the connected or unconnected transformations?

http://prashanthobiee.blogspot.com/2012/12/connected-and-unconnected.html

In how many ways can you create ports?

1.Drag the port from another transformation

2.Click the add button on the ports tab.

What are the reusable transformations?

http://prashanthobiee.blogspot.com/2012/12/reusable-transformation.html

 What are the methods for creating reusable transformations?

 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 the 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 you change the properties of a reusable transformation in  mapping, you can revert it to the original reusable transformation properties by clicking the revert button.

What are the unsupported repository objects for a mapplet?

          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

What are the mapping parameters and mapping variables?

http://prashanthobiee.blogspot.com/2012/12/mapping-parameters-and-mapping-variables.html

Can you use the mapping parameters or variables created in one mapping into another  mapping?

No. We can use mapping parameters or variables in any transformation of the same mapping or mapplet in which you have created mapping parameters or variables.

Can you use the  mapping parameters or variables created in one mapping into any other reusable transformation?

Yes. Because reusable tranformation is not contained with any mapplet or mapping.

How can U improve session performance in aggregator transformation?

Use sorted input.

What  is aggregate cache in aggregator transformation?

http://prashanthobiee.blogspot.com/2012/12/aggregate-cache-in-aggregator.html

What are the difference between joiner transformation and source qualifier transformation?

http://prashanthobiee.blogspot.com/2012/12/difference-between-joiner.html

In which conditions we cannot use joiner transformation (Limitations of joiner transformation)

Both pipelines begin with the same original data source.

Both input pipelines originate from the same Source Qualifier transformation.

Both input pipelines originate from the same Normalizer transformation.

Both input pipelines originate from the same Joiner transformation.

Either input pipelines contains an Update Strategy transformation.

Either input pipelines contains a connected or unconnected Sequence

Generator transformation.

What are the settings that you use to configure the joiner transformation?

 Master and detail source, Type of join and Condition of the join

What are the join types in joiner transformation?

Normal (Default), Master outer, Detail outer, full outer

Master Outer :All rows from Detail and only matching rows from Master

Detail outer   :All rows from Master and only matching rows from Detail.

Full outer join: keeps all rows of data from both the master and detail sources

What are the joiner caches?

When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows.After building the caches, the Joiner transformation reads records from the detail source and perform joins.

What is the look up transformation?

http://prashanthobiee.blogspot.com/2012/12/look-up-transformation.html 

Why use the lookup transformation ?

Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data  Easier to read. Perform a calculation. Many normalized tables include values used in a Calculation, 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.

What are the types of lookup?

Connected and unconnected

Differences between connected and unconnected lookup?

http://prashanthobiee.blogspot.com/2012/12/differences-between-connected-and.html                           

              

What is meant by lookup caches?

http://prashanthobiee.blogspot.com/2012/12/lookup-cache.html

What are the types of lookup caches?

http://prashanthobiee.blogspot.com/2012/12/types-of-lookup-cache.html

Difference between uncached, static cache and dynamic cache

http://prashanthobiee.blogspot.com/2012/12/uncached-static-cache-and-dynamic-cache.html

                                                                    

How the informatica server sorts the string values in Rank transformation?

When the informatica server runs in the ASCII data movement mode it sorts session data using  Binary sort order.If U configure the session use a binary sort order, the informatica server calculates the binary value of each string and returns the specified Number of rows with the highest binary values for the string.

What are the rank caches?

During the session ,the informatica server compares an in out row with rows in the data cache. If the input row out-ranks a stored row, the informatica  server replaces the  stored row with the input row. The informatica server stores group information in an index cache and row data in a data cache.

What is the Rank index in Rank transformation?

http://prashanthobiee.blogspot.com/2012/12/rank-index-in-rank-transformation.html 

What is the Router transformation?

http://prashanthobiee.blogspot.in/2012/12/router-transformation.html

What are the types of groups in Router transformation?   

Input group, Output group

The designer copies property information from the input ports of the input group to create a set  of output ports for each output group.

     Two types of output groups

    User defined groups

    Default group

    you can not modify or delete default groups.

Can you look up a flat file using Informatica?

 Yes.

While running a session what default files are created?

 Default files are. Log files and Bad files. (For other files like err, target files you have let the Informatica know.

Describe the use of Materialized views and how are they different from a normal view.

 Normal View is not a physical object. It is created at runtime by the select statement. Materialized View is a physical object with real data. It has several use. For more details look into Oracle documentation. It is a major performance booster when you need summary,aggregated or complex joins to be calculated. It pre-calculates it and is ready for use when you need them and it is really fast. Be careful to choose the mechanism you want to refresh it.