1. How many types of facts and what are they?

Factless Facts:Facts without any measures.
Additive Facts:Fact data that can be additive/aggregative.
Non-Additive facts: Facts that are result of non-additon
Semi-Additive Facts: Only few colums data can be added.
Periodic Facts: That stores only one row per transaction that happend over a period of time.
Accumulating Fact: stores row for entire lifetime of event.

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

2. How to import VSAM files from source to target. Do we need a special plugin?

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

3. Whats the diff between Informatica powercenter server, repositoryserver and repository?

Repository is a database in which all informatica componets are stored in the form of tables. The reposiitory 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.

4. What are variable ports and list two situations when they can be used?

We have mainly tree ports Inport, Outport, Variable port. Inport represents data is flowing into transformation. Outport is used when data is mapped to next transformation. Variable port is used when we mathematical calculations are required. If any addition i will be more than happy if you can share.

We can use variable ports to store values of previous records which is not otherwise possible in Informatica.

5. Is union Transormation is Active transformation?

By Definiation, Active transformation is the transformation that changes the number of rows that pass through it...in union transformation the number of rows resulting from union can be (are) different from the actual number of rows.

As we are combining results of two select queries using Union Tr Most probably no of rows increases.So it is An Active Tr.

6. What is source qualifier transformation?

When you add a relational or a flat file source definition to a maping,U need to connect it to a source qualifer transformation.The source qualifier transformation represnets the records that the informatica server reads when it runs a session.

SQ transformation is a transformation which is automatically generated to read data from source tables into informatica designer.

7. What is the difference between constraind base load ordering and target load plan?

Constraint based load ordering


Table 1---Master

Tabke 2---Detail

If the data in table1 is dependent on the data in table2 then table2 should be loaded first.In such cases to control the load order of the tables we need some conditional loading which is nothing but constraint based load

In Informatica this feature is implemented by just one check box at the session level.

A CBl specifies the order in which data loads into the targets based on key constraints

A target load plan defines the order in which data being extracted from the source qualifier

8. What are partition points?

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

the pipeline into stages.

Partition points mark the thread boundaries in a pipeline and
divide the pipeline into stages. The Informatica Server sets partition points at several
transformations in a pipeline by default. If you use PowerCenter, you can define other partition
points. When you add partition points, you increase the number of transformation threads,
which can improve session performance. The Informatica Server can redistribute rows of data at partition points, which can also improve session performance.

9. What is the difference between connected and unconnected stored procedures?

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.

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.

by using unconnected stored procedure reusability is possible in connected only one time is possible

10. What is the difference between Narmal load and Bulk load?

Normal Load: Normal load will write information to the database log file so that if any recorvery 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 recorvery is needed we can't do any thing in such cases.

compartivly Bulk load is pretty faster than normal load.

Download Interview PDF