Informatica Interview Question Part-5 - ETL- iNFORMATICA DEVELOPER

Tuesday, June 18, 2019

Informatica Interview Question Part-5


Q. How can i explain my project architecture in interview..? Tell me your project flow from source to target..?

Project architecture is like

1. Source Systems: Like Mainframe,Oracle,People soft,DB2.

2. Landing tables: These are tables act like source. Used for easy to access, for backup purpose, as reusable for other mappings.

3. Staging tables: From landing tables we extract the data into staging tables after all validations done on the data.

4. Dimension/Facts: These are the tables those are used for analysis and make decisions by analyzing the data.

5. Aggregation tables: These tables have summarized data useful for managers who wants to view monthly wise sales, year wise sales etc.

6. Reporting layer: 4 and 5 phases are useful for reporting developers to generate reports. I hope this answer helps you.

Q. What type of transformation is not supported by mapplets?

·         Normalizer transformation
·         COBOL sources, joiner
·         XML source qualifier transformation
·         XML sources
·         Target definitions
·         Pre & Post Session stored procedures
·         Other mapplets

Q. How informatica recognizes mapping?

All are organized by Integration service.
Power center talks to Integration Service and Integration service talk to session. Session has mapping Structure. These are flow of Execution.

Q. Can every transformation reusable? How?

Except source qualifier transformation, all transformations support reusable property. Reusable transformation developed in two ways.
1. In mapping which transformation do you want to reuse, select the transformation and double click on it, there you got option like make it as reusable transformation
option. There you need to check the option for converting non reusable to reusable transformation. but except for source qualifier trans.
2. By using transformation developer

Q. What is Pre Sql and Post Sql?

Pre SQL means that the integration service runs SQL commands against the source database before it reads the data from source.

Post SQL means integration service runs SQL commands against target database after it writes to the target.

Q. Insert else update option in which situation we will use?

if the source table contain multiple records .if the  record specified in the associated port to insert into lookup cache. it does not find a record in the lookup cache when it is used find the particular record & change the data in the associated port.

We set this property when the lookup TRFM uses dynamic cache and the session property TREAT SOURCE ROWS AS "Insert" has been set.

This option we use when we want to maintain the history.

If records are not available in target table then it inserts the records in to target and records are available in target table then it updates the records.

Q. What is an incremental loading? in which situations we will use incremental loading?

Incremental Loading is an approach. Let suppose you a mapping for load the data from employee table to a employee_target table on the hire date basis. Again let suppose you already move the employee data from source to target up to the employees hire date 31-12-2009.Your organization now want to load data on employee_target today. Your target already have the data of that employees having hire date up to you now pickup the source data which are hiring from 1-1-2010 to till date. That's why you needn't take the data before than that date, if you do that wrongly it is overhead for loading data again in target which is already exists. So in source qualifier you filter the records as per hire date and you can also parameterized the hire date that help from which date you want to load data upon target.
This is the concept of Incremental loading.

Q. What is target update override?

By Default the integration service updates the target based on key columns. But we might want to update non-key columns also, at that point of time we can override the
UPDATE statement for each target in the mapping. The target override affects only when the source rows are marked as update by an update strategy in the mapping.

Q. What is the Mapping parameter and Mapping variable?

Mapping parameter: Mapping parameter is constant values that can be defined before mapping run. A mapping parameter reuses the mapping for various constant values.

Mapping variable: Mapping variable is represent a value that can be change during the mapping run  that can be stored in repository the integration service retrieve that value from repository and incremental value for next run.

Q. What is rank and dense rank in informatica with any examples and give sql query for this both ranks

for eg: the file contains the records with column
200(repeated rows)
the rank function gives output as
and dense rank gives

for eg: the file contains the records with column
empno sal
100  1000
200(repeated rows) 2000
200  3000
300 4000
400 5000
500 6000

Rank :

select rank() over (partition by empno order by sal) from emp


Dense Rank
select dense_rank() over (partition by empno order by sal) from emp
and dense rank gives


Q. What is the incremental aggregation?

The first time you run an upgraded session using incremental aggregation, the Integration Service upgrades the index and data cache files. If you want to partition a session using a mapping with incremental aggregation, the Integration Service realigns the index and data cache files.

Q. What is session parameter?

Parameter file is a text file where we can define the values to the parameters .session parameters are used for assign the database connection values

Q. What is mapping parameter?

A mapping parameter represents a constant value that can be defined before mapping run. A mapping parameter defines a parameter file which is saved with an extension.prm a mapping parameter reuse the various constant values.

Q. What is parameter file?

A parameter file can be a text file. Parameter file is to define the values for parameters and variables used in a session. A parameter file is a file created by text editor such as word pad or notepad. You can define the following values in parameter file
·         Mapping parameters
·         Mapping variables
·         Session parameters

Q. What is session override?

Session override is an option in informatica at session level. Here we can manually give a sql query which is issued to the database when the session runs. It is nothing but over riding the default sql which is generated by a particular transformation at mapping level.

Q. What are the diff. b/w informatica versions 8.1.1 and 8.6.1?

Little change in the Administrator Console. In 8.1.1 we can do all the creation of IS and repository Service, web service, Domain, node, grid ( if we have licensed version),In 8.6.1 the Informatica Admin console we can manage both Domain page and security page. Domain Page means all the above like creation of IS and repository Service, web service, Domain, node, grid ( if we have licensed version) etc. Security page means creation of users, privileges, LDAP configuration, Export Import user and Privileges etc.

Q. What are the uses of a Parameter file?

Parameter file is one which contains the values of mapping variables.
type this in it .
Parameter files are created with an extension of .PRM

These are created to pass values those can be changed for Mapping Parameter and Session Parameter during mapping run.

Mapping Parameters:
A Parameter is defined in a parameter file for which a Parameter is create already in the Mapping with Data Type , Precision and scale.

The Mapping parameter file syntax (xxxx.prm).

After that we have to select the properties Tab of Session and Set Parameter file name including physical path of this xxxx.prm file.

Session Parameters:
The Session Parameter files syntax (yyyy.prm).
$InputFileValue1=Path of the source Flat file

After that we have to select the properties Tab of Session and Set Parameter file name including physical path of this yyyy.prm file.

Do following changes in Mapping Tab of Source Qualifier's
Properties section
  Attributes                values

Source file Type ---------> Direct
Source File Directory  --------> Empty
Source File Name       --------> $InputFileValue1

No comments:

Post a Comment