Answer:
When we handle data insertion, updating, deletion and/or rejection in a single mapping, we use Update Strategy transformation to flag the rows for Insert, Update, Delete or Reject. We flag it by either providing the values 0, 1, 2, 3 respectively or by DD_INSERT, DD_UPDATE, DD_DELETE or DD_REJECT in the Update Strategy transformation. By default the transform has the value '0' and hence it performs insertion.
Suppose we want to perform insert or update target table in a single pipeline. Then we can write the below expression in update strategy transformation to insert or update based on the incoming row.
IIF (LKP_EMPLOYEE_ID IS NULL, DD_INSERT, DD_UPDATE)
If we can use more than one pipeline then, it’s not a problem. For the Insert part we don’t even need an Up-date Strategy transform explicitly (DD_INSERT), we can map it straight away.
8. What is the difference between update strategy and following update options in target?
Update as Update - Update as Insert - Update else Insert Even if we do not use update strategy we can still update the target by setting, for example Update as Update and treating target rows as data driven. So what's the difference here?
Answer:
The operations for the following options will be done in the Database Level.
Update as Update
Update as Insert
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
It will write a 'select' statement on the target table and will compare with the source. Accordingly if the rec-ord already exits it will do an update else it will insert. On the other hand the update strategy the operations will be done at the Informatica level itself.
Update strategy also gives conditional update option - wherein based on some condition you can update/ in-sert even reject the rows. Such conditional options are not available in target based updates (wherein it will either “update” or it will perform “update else insert” based on the keys defined in Informatica level)
9. What is the use of Forward Reject rows in Mapping?
Answer:
If DD_REJECT is selected in the Update Strategy, then we need to select this option to generate the Reject/
Bad file.
10. Scenario Implementation 1
Suppose we have source employee table and we want to load employees who belong to department 10 to Target 1, 20 to Target 2 and 30 to Target 3. Describe the approach without using FILTER or ROUTER Trans-formations.
Answer:
We will use three separate Update Strategy transformations before each of the target tables (T1, T2, T3), and provide below condition in their expression editor:
UPD_T1: IIF (DEPTNO = 10, DD_INSERT, DD_REJECT) UPD_T2: IIF (DEPTNO = 20, DD_INSERT, DD_REJECT) UPD_T3: IIF (DEPTNO = 30, DD_INSERT, DD_REJECT)
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
14. Java Transformation
Using Java transformation in Informatica we can generate as many records required as per the requirement.
Here goes the Java code.
How can I replace characters e.g. A to Z in a particular string to its ASCII value?
E.g. Input String-AB123C1; Output string-6566123671 Answer:
If the INPUT string is fixed size of 9 characters, Use the below code as expression in an Output port of an Informatica Expression transformation.
Alternatively you can use Informatica User-Defined Function with the INPUT string as an Argument:
IIF( IS_NUMBER( SUBSTR( INPUT, 1, 1 ) ) = 1, SUBSTR( INPUT, 1, 1 ),
TO_CHAR( ASCII( SUBSTR( INPUT, 5, 1 ) ) ) ) ||
As per the requirement we want to convert just the Characters in an input String to its ASCII equivalent not the Digits.
If the requirement were to convert a single character to ASCII equivalent in Informatica, then the ASCII in-built function of Informatica would have been helpful. E.g. ASCII(inp_chr)
But single this is a string and we need the ASCII equivalent of each characters in the string i.e.
parse each characters; concept of loop comes in picture. So use Informatica JAVA transformation.
Use Informatica Passive Java transformation:
I have the i/p column name as INPUT and o/p value from Java transform as OUTPUT port created.
On the Java Code tab of Java transformation use the below java code:- String inp = INPUT;
15. Source Qualifier Transformation
1. What is a Source Qualifier? What are the tasks we can perform using a Source Qualifier and why it is an ACTIVE transformation?
Answer:
A Source Qualifier is an Active and Connected transformation that reads the rows from a relational database or flat file source.
We can configure the SQ to join [Both INNER as well as OUTER JOIN] data originating from the same source database.
We can use a source filter to reduce the number of rows the Integration Service queries.
We can specify a number for sorted ports and the Integration Service adds an ORDER BY clause to the default SQL query.
We can choose Select Distinct option for relational databases and the Integration Service adds a SE-LECT DISTINCT clause to the default SQL query.
Also we can write Custom/Used Defined SQL query which will override the default query in the Source Qualifier by changing the default settings of the transformation properties for relational da-tabases.
Also we have the option to write Pre as well as Post SQL statements to be executed before and after the Source Qualifier query in the source database.
Since the transformation provides us with the property Select Distinct, when the Integration Service adds a SELECT DISTINCT clause to the default SQL query, which in turn affects the number of rows returned by the Database to the Integration Service and hence it is an Active transformation.
2. What happens to a mapping if we alter the data types between Source and its corre-sponding Source Qualifier?
Answer:
The Source Qualifier transformation displays the Informatica data types. The transformation data types de-termine how the source database binds data when the Integration Service reads it.
Now if we alter the data types in the Source Qualifier transformation or the data types in the Source defini-tion and Source Qualifier transformadefini-tion do not match, the Designer marks the mapping as invalid when we save the mapping.
3. Suppose we have used the Select Distinct and the Number of Sorted Ports property in the Source Qualifier and then we add Custom SQL Query. Explain what will happen.
Answer:
Whenever we add Custom SQL or SQL override query it overrides the User-Defined Join, Source Filter, Num-ber of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation. Hence only the user
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
4. Describe the situations where we will use the Source Filter, Select Distinct and Number of Sorted Ports properties of Source Qualifier transformation.
Answer:
Source Filter option is used basically to reduce the number of rows the Integration Service queries, so as to improve performance.
Select Distinct option is used when we want the Integration Service to select unique values from a source.
Filtering out unnecessary data earlier in the data flow, will improve performance.
Number Of Sorted Ports option is used when we want the source data to be in a sorted fashion, so as to use the same in some following transformations like Aggregator or Joiner, those when configured for sorted in-put will improve the performance.
5. What will happen if the SELECT list COLUMNS in the Custom override SQL Query and the OUTPUT PORTS order in Source Qualifier transformation do not match?
Answer:
Mismatch or changing the order of the list of selected columns in the SQL Query override of Source Qualifier to that of the connected transformation output ports may result is unexpected value result for ports if data types matches by chance, else will lead to session failure.
6. What happens if in the Source Filter property of SQ transformation we include keyword WHERE say, WHERE CUSTOMERS.CUSTOMER_ID > 1000.
Answer:
We use Source filter to reduce the number of source records. If we include the string WHERE in the source filter, the Integration Service fails the session. In the above case, the correct syntax will be CUSTOM-ERS.CUSTOMER_ID > 1000
7. Describe the scenarios where we go for Joiner transformation instead of Source Qualifier transformation.
Answer:
While joining Source Data of heterogeneous sources as well as to join flat files we will use the Joiner trans-formation. Use the Joiner transformation when we need to join the following types of sources:
Join data from different Relational Databases.
Join data from different Flat Files.
Join relational sources and flat files.
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
8. What is the maximum number we can use in Number of Sorted Ports for Sybase source system?
Answer:
Sybase supports a maximum of 16 columns in an ORDER BY clause. So if the source is Sybase, do not sort more than 16 columns.
9. What is use of Source Qualifier in Informatica? Can we create a mapping without a source qualifier?
Answer:
Source Qualifier is used to convert the data types of Heterogeneous Source Objects supported by Informatica to Native Informatica data types, after which Informatica processes the following ob-jects in a mapping with consistent Informatica data types.
Also for relational table Source Qualifier helps to join multiple tables from the same database and also al-lows doing Pre or Post SQL operations.
We cannot create a mapping without Source Qualifier; it is the first transformation in Informatica that is at-tached with the source tables or source flat file instance.
10. Suppose we have two tables of same database type, residing in different Database in-stance. If a Database Link is available, how can we join the two tables using a Source Qualifier in Informatica provided there are valid join columns.
Answer:
Source Qualifier Override:-
SELECT e.empno, e.ename, s.salary, s.comm FROM emp e, sal@dblinkname s
WHERE e.empno=s.empno
It is advisable to create a Public Synonym at Database for the remote tables so that we can avoid using the syntax : TableName@DBLinkName
11. What is the meaning of “output is deterministic” property in source qualifier transfor-mation?
Answer:
Output is deterministic means we are informing Informatica that the output does not change (for
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
case if we have set the source as deterministic, then the session would have created a cache (on the disc) of the source during normal run to be used for recovery. This saves time during recovery because we need not issue the SQL command to the source database again.
If this was not set, then the source data cache is not created during normal run and SQL will be reissued dur-ing recovery. In some cases, if this property is not set you will not be able to enable recovery for the session.
12. Scenario Implementation 1
How to delete duplicate rows present in relational database using Informatica? Suppose we have duplicate records in Source System and we want to load only the unique records in the Target System eliminating the duplicate rows. What will be the approach?
Answer:
Assuming that the source system is a Relational Database, to eliminate duplicate records, we can check the Distinct option of the Source Qualifier of the source table and load the target accordingly.
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
16. Miscellaneous
1. What are the new features of Informatica 9.x in developer level?
Answer:
From a developer's perspective, some of the new features in Informatica 9.x are as follows:
Now Lookup can be configured as an active transformation - it can return multiple rows on success-ful match.
Now you can write SQL override on un-cached lookup also. Previously you could do it only on cached lookup.
You can control the size of your session log. In a real-time environment you can control the session log file size or time.
Database deadlock resilience feature - this will ensure that your session does not immediately fail if it encounters any database deadlock, it will now retry the operation again. You can configure num-ber of retry attempts.
Cache can be updated based on a condition or expression.
New interface for admin console, now onwards called Informatica Administrator. (Create connection objects, grant permission on database connections, deploy or configure deployment units from the Informatica Administrator)
PowerCenter licensing now onwards based on the number of CPUs and repositories.
2. Name the transformations which converts one to many rows i.e. increases the I/P: O/P row count. Also what is the name of its reverse transformation?
Answer:
Normalizers as well as Router Transformations are two Active transformations which can increase the num-ber of input rows to output rows.
Aggregator Transformation performs the reverse action of Normalizer transformation.
3. How many ways we can filter records?
Answer:
Source Qualifier
Filter transformation
Router transformation
Update strategy
4. What are the transformations that use cache for performance?
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
Aggregator, Sorter, Lookups, Joiner and Rank transformations use cache.
5. What is the formula for calculation of Lookup/Rank/Aggregator index & data caches?
Answer:
Index cache size = Total no. of rows * size of the column in the lookup condition (50 * 4)
Aggregator/Rank transformation Data Cache size = (Total no. of rows * size of the column in the lookup condition) + (Total no. of rows * size of the connected output ports)
Aggregator Index cache: #Groups ((Σ column size) + 7)
6. What is the difference between Informatica PowerCenter and Exchange and Mart?
Answer:
PowerCenter:
PowerCenter can have many repositories.
It supports the Global Repository and networked local repositories.
PowerCenter can connect to all native legacy source systems such as Mainframe, ERP, CRM, EAI (TIBCO, MSMQ, JMQ)
High Availability and Load sharing on multiple servers in the grid.
Informatica Session level Partioning is available.
Informatica Pushdown Optimizer is available.
PowerMart:
PowerMart supports only one repository.
PowerMart can connect to Relational and flat file sources.
PowerExchange:
PowerExchange Client and PowerExchange ODBC are PowerExchange interfaces to extract and load data for a variety of data types on a variety of platforms relational, non-relational, and changed data in batch-mode or real-time using PowerCenter.
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
The PowerExchange Client for PowerCenter is installed with PowerCenter and integrates PowerExchange(Separate License for the required source system; Check Sources->Import from PowerExchange) and PowerCenter to extract relational, non-relational, and changed data.
7. How do we handle delimiter character as a part of the data in a delimited source file?
Answer:
For delimiter files the delimiter is the separator that identifies the data values of fields present in the file.
So ideally if the data file contains the delimiter character as a part of the data in a field value, the field value either remains within double or single quotes or an escape character precedes the delimiter that is actually to be treated as a normal character.
To handle the same flat-files in Informatica, use the following options as per the data file format while defin-ing the file structure.
1. Select Optional Quotes to Double or Single Quote. The column delimiters within the quote characters are ignored.
2. Escape Character used to escape the delimiter or quote character.
Escape character preceding the delimiter character in an unquoted string or the quote character in a quoted string is treated as regular character.