Interview Question Set 01

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

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

2. How many ways do you create ports?

Ans: Two ways:-
1.Drag the port from another transformation
2.Click the add button on the ports tab.

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

Ans: Two ways:–
1. Edit the definition
2. Reimport the definition.

4.What are the basic needs to join two sources in a source qualifier?

Ans: 1.Two sources should have primary and Foreign key relationships.
2.Two sources should have matching data types.

5.What are the different options used to configure the sequential batches?

Ans: Two options
Run the session only if the previous session completes successfully. Always runs the session.

6.What are the methods for creating reusable transformations?

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

7.What are the active and passive transformations?

Ans: Transformations can be active or passive.

An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter condition.

A passive transformation does not change the number of rows that pass through it, such as an Expression transformation that performs a calculation on data and passes all rows through the transformation.

8.How can you work with a remote database in informatica? Did you work directly by using remote connections?

Ans: To work with remote data source you need to connect it with remote connections. But it is not preferable to work with that remote source directly by using remote connections .Instead u bring that source into your local machine where informatica server resides. If u work directly with a remote source the session performance will decrease by passing less amount of data across the network in a particular time.

9.What is the update strategy transformation?

Ans: This transformation is used to maintain the history data or just most recent changes into the target table.

10.Why is the sorter transformation an active transformation?

Ans: This is a type of active transformation which is responsible for sorting the data either in the ascending order or descending order according to the key specifier. the port on which the sorting takes place is called as sort Keyport properties if u select distinct eliminate duplicates.

11.How can you complete unrecoverable sessions?

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

12.What are the different types of Type2 dimension mapping?

Ans: Type2

1. Version number
2. Flag
3.Date

13.What are the different types of Type2 dimension mapping?

Ans: Type1

Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data. Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

Type 2: 

The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target. Changes are tracked in the target table by versioning the primary key and creating a version number for each dimension in the table.
Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.

Type 3: 

The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Informatica Server saves existing data in different columns of the same row and replaces the existing data with the updates.

14.What is the target load order?

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

15.When conventional data warehousing is able to handle and answer complex queries then what is the need of OLTP and OLAP?

Ans: Designed for analysis of business measures by categories and attributes Optimized for bulk loads and large, complex, unpredictable queries that access many rows per table. Loaded with consistent, valid data; requires no real time validation Supports few concurrent users relative to OLTP Again, all these are covered in the tutorials, why and how you should select.

16.How can we join 3 databases like Flat File, Oracle, Db2 in Informatica?

Ans: You have to use two joiner transformations. First one will join two tables and the next one will join the third with the resultant of the first joiner.

17.How do you transfer the data from a data warehouse to a flat file?

Ans: You can write a mapping with the flat file as a target using a DUMMY_CONNECTION. A flat file target is built by pulling a source into target space using the Warehouse Designer tool.

18.How can you work with a remote database in informatica? Did you work directly by using remote connections?

Ans: You can work with remote, But you have to Configure FTP Connection details IP address.

19.Define informatica repository?

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

20. Which tool do you use to create and manage sessions and batches and to monitor and stop the informatica server?

Ans: Informatica Workflow Manager and Informatica Workflow Monitor.