informatica faqs

What are the type of repositories created using Informatica Repository Manager?

Informatica PowerCenter includes following type of repositories:

Standalone Repository, which functions individually.

Global Repository which is a centralized repository in a domain and it also contain shared objects across the repositories in a domain.

Local Repository is one which is within a domain.

Versioned Repository can be either local or global but it allows version control.

What is a code page?

A code page contains encoding to specify characters in a set of one or more languages and is selected based on source of the data. The set code page refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.

What do you mean by code page compatibility?

When two code pages are compatible, the characters encoded in the two code pages are virtually identical which ensures no data loss. This compatibility is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. One code page can be a subset or superset of another. For proper data movement, the target code page must be a superset of the source code page.

What is a transformation?

A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. Each transformation has rules for configuring and connecting in a mapping. Transformation is created to use once in a mapping or reusable transformations can be created to use in multiple mappings.

Eg. Aggregator transformation performs calculations on groups of data.

What are the types of loading in Informatica?

There are two types of loading in informatica, normal loading and bulk loading.

In normal loading record by record are loaded and writes log for that. In this longer time is needed to load data to the target.

In bulk loading number of records are loaded at a time to target database. It takes less time to load data to the target than in normal loading.

Why do we use the lookup transformation? How can we improve session performance in aggregator transformation?

A lookup transformation is used for checking the matched values from the source or target tables and check whether the record already existing in the table. It is also used for updating the slowly changing dimensions and also performs some calculations.

Using Incremental Aggregation we create Sorted Input option to improve the performance since performance is reduced using the caches.

What is the difference between static cache and dynamic cache?

In case of dynamic cache, when we are inserting a new row it checks the lookup cache to see if it exists, if not inserts it into the target as well as the cache but in case of static cache the new row is written only in the target and not the lookup cache.

The lookup cache remains static and does not change during the session but incase of dynamic cache the server inserts, updates in the cache during session.

sql queries

21) Display the names of employees whose names have second alphabet A in

their names.

SQL>select ename from emp where ename like ‘_A%’;

22) select the names of the employee whose names is exactly five characters

in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in(‘MANAGER’);

24) Display the names of the employee who are not working as SALESMAN OR

CLERK OR ANALYST.

SQL>select ename from emp where job not

in(‘SALESMAN’,'CLERK’,'ANALYST’);

25) Display all rows from emp table.The system should wait after every

screen full of informaction.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

informatica faqs

In real time scenario where update strategy transformation is used?if we dml operations in session properties?

We can use Update strategy transformation in two ways .

1.Mapping level.

2.session level.

Importence of Update strategy transformation in both cases as follows.

In real time if we want to update the existing record with the same source data you can go for session level update logic.

If you want to applay different set of rules for updating or inserting a record even that record is existed in the warehouse table .you can go for mapping level Update strategy transformation.It means that if you are using Router transformation for performaning different activities.

EX: If the employee ‘X1234 ‘ is getting Bonus then updating the Allowance with 10 less.If not inserting the record with new Bonus in the Warehouse table.

How you capture changes in data if the source system does not have option of storing date/time field?

The DW database can be Oracle or Teradata. The requirement here is to pull data from source system and ETL need to device a mechanism to identify the changes or new records. The source system can be a legacy system like AS400 application or Mainframe application. List out all such methods of data capture. The ETL can be Informatica, data stage or custom etl code.

If LKP on target table is taken, can we update the rows without update strategy transformation?

Update strategy transformation determines whether to insert, update, delete or reject a record for the target.  We can bypass update strategy transformation by creating a router to divide rows based on insert, update, etc and connecting to one of the multiple instances of target.  In the session, for that target instance we can check the appropriate box  to mark records for insert or update or delete

In what scenario ETL coding is preferred than Database level SQL,PL/SQL coding?

Data scrubbing process is difficult. That is file contains date column like 20070823 but data warehouse requires date as 08/23/2007 in that case it is difficult.

In What scenario ETL coding is preferred than Database level SQL, PL/SQL coding? What is the methodology?

We should go for an ETL tool when we have to extract the data from multiple source systems like flat file, oracle , COBOL etc at one instance. where PL/SQL or SQL can not fit.

2. we can update the target with out using update strategy by setting the session parameters ..if the source is a database.

3. Stop on errors=1 (if you set this option to 1 the session will be stopped after occurance of 1 error row. if it is 0 the  session will not be stopped even u got n number of errors.

4. Lookups can be used for validation purpose.