Informatica Interview Questions – Part – 5

1. Why we use stored procedure transformation?
For populating and maintaining data bases.

2. What is aggregate cache in aggregator transformation?
The aggregator stores data in the aggregate cache until it completes aggregate calculations. When we run a session that uses an aggregator transformation, the informatica server creates an index and data caches in the memory to process the transformation. If the informatica server requires more space, it stores overflow values in cache files.

3. What is the mapplet in informatica ?
In Informatica Mapplet is a set of transformations that you build in the mapplet designer and you can use in multiple mappings.

4.What are the different types of OLAP? Give an example?
ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP

5. Briefly describe lookup transformation?
Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the look up condition for all look up ports delivered during transformations.

Informatica Interview Questions – Part – 4

1. While importing the relational source definition from database, what are the metadata of source you import?
Source name
Database location
Column names
Data types
Key constraints

2. What is the use of transformation?
Transformation is being done on the repository that is being stored in the object and it allows the synchronization to be maintained.
Transformation allows the modification and generation of the data from one source to another source.
It allows the passing of the data from one source to another source using the scripts and the functions.
The designer used in Informatica provides with the set of transformation rules that allow the process to be processed.
It transforms the group of data together so as to provide a pool of data from where things can be easily taken.

3. Where can we find the throughput option in informatica?
Throughput option can be found in informatica in workflow monitor. In workflow monitor, right click on session, then click on get run properties and under source/target statistics we can find throughput option.

4. What is Joiner transformation?
Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.

5. What is the benefit of partitioning a session?
Partitioning a session means solo implementation sequences within the session. It’s main purpose is to improve server’s operation and efficiency. Other transformations including extractions and other outputs of single partitions are carried out in parallel.

Informatica Interview Questions – Part – 3

1. In a joiner transformation, you should specify the source with fewer rows as the master source. Why?
Joiner transformation compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.

2. What is Lookup transformation?
It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.

3. What is Incremental Aggregation?
Whenever a session is created for a mapping Aggregate Transformation, the session option for Incremental Aggregation can be enabled. When PowerCenter performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.

4. What is the function of Union Transformation?
The union transformation provides the use of multiple input groups that allows the transformation to process further.
It provides the data sources to be merged by using different sources and then the data is used in the application.
The pipeline consists of the data sources that are used for merging purpose and other union purposes.
The union transformations allow the transformations of the multiple input groups that provide merging of the data from different sources.
The transformation allows the use of functions like UNION ALL statements in SQL that combines with the result of the SELECT statements.

5. Design a mapping to load the first 3 rows from a flat file into a target?
Assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3

Informatica Interview Questions – Part – 2

1. What is meant by direct and indirect loading options in sessions?
Direct loading can be used to single transformation where as indirect transformation can be used to multiple transformations or files.
In the direct we can perform recovery process but in indirect we cant do it .

2. What is predefined event?
It is a file-watch event. It waits for a specific file to arrive at a specific location.

3. What do you mean Incremental Aggregation?
Option for incremental aggregation is enabled whenever a session is created for a mapping aggregate. Power center performs incremental aggregation through the mapping and historical cache data to perform new aggregation calculations incrementally.

4. Explain sessions. Explain how batches are used to combine executions?
A teaching set that needs to be implemented to convert data from a source to a target is called a session. Session can be carried out using the session’s manager or pmcmd command. Batch execution can be used to combine sessions executions either in serial manner or in a parallel. Batches can have different sessions carrying forward in a parallel or serial manner.

5. Which all databases powercenter server on windows can connect to?
PowerCenter Server on Windows can connect to following databases:
IBM DB2
Informix
Microsoft Access
Microsoft Excel
Microsoft SQL Server
Oracle
Sybase
Teradata

Informatica Interview Questions – Part – 1

1. Difference between static and dynamic cache and explain with one example?
Answer : 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 update in the table (or source) for which it is reffering to.(ex. Connected lookup).

2. What is update strategy transformation ?
Answer : The model you choose constitutes your update strategy, how to handle changes to existing rows. In powercenter and powermart, you set your update strategy at two different levels:
Within a session : when you configure a session, you can instruct the informatica server to either treat all rows in the same way (for example, treat all rows as inserts), or use instructions coded into the session mapping to flag rows for different database operations.
Within a mapping : within a mapping, you use the update strategy transformation to flag rows for insert, delete, update, or reject. Update strategy is used to update the target.

3. What is the procedure to load the fact table. Give in detail?
Answer : Based on the requirement to your fact table, choose the sources and data and transform it based on your business needs. For the fact table, you need a primary key so use a sequence generator transformation to generate a unique key and pipe it to the target (fact) table with the foreign keys from the source tables.

4. When we create a target as flat file and source as oracle. How can I specify first rows as column names in flat files?
Answer : Use a pre sql statement., but this is a hard coding method. If you change the column names or put in extra columns in the flat file, you will have to change the insert statement.
You can also achieve this by changing the setting in the informatica repository manager to display the columns heading. The only disadvantage of this is that it will be applied on all the files that will be generated by this server.

5. When to use Abort, Decode functions?
Answer : Abort can be used to Abort / stop the session on an error condition. If the primary key column contains NULL, and you need to stop the session from continuing then you may use ABORT function in the default value for the port. It can be used with IIF and DECODE function to Abort the session.