Informatica PowerCenter

2013-05-29T07:38:00.000+05:30

Scenario 1

We have a source table as follows:

We have to move the into the target tables such that one

MY SOURCE

MY TARGET

SNo	Name
1	A
2	B
1	C
3	D
4	F
1	E
2	G

SNo	Name
1	A
2	B
1	C
3	D
4	F
1	E
2	G

After importing Source and Target, create a Sorter transformation to sort the data.
Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
Give an expression as follows
v_Name -> IIF(SNo = v_SNo, v_Name||Name, Name)
v_SNO -> SNo
o_Name -> v_Name
Finally connect the ports to Target, create and execute the session and workflow.

Sequence Generator transformation:

2012-10-14T19:42:00.005+05:30

Sequence Generator transformation is Passive and Connected Transformation.
The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
We use it to generate Surrogate Key in Data Ware House environment mostly. When we want to maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key.

For example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports:

The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
We cannot edit or delete these ports.
Likewise, we cannot add ports to the transformation.
NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a transformation or target.

For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.

CURRVAL: CURRVAL is NEXTVAL plus the Increment By value.

We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.

If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
When we connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping.

Points to remember:

If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
If we connect just CURR_VAL only, the value will be same for all records.
If Current value is 1 and end value 10, cycle option there. Start value is 0. There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
If Current value is 1 and end value 10, cycle option there. Start value is 1. There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
Use reset option if you want to start sequence from CURR_VAL every time.

Stored Procedure transformation

2012-10-07T20:24:00.003+05:30

Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
Stored procedures are stored and run within the database.
A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

Check the status of a target database before loading data into it.
Determine if enough space exists in a database.
Perform a specialized calculation.
Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
Return values: Value returned by Stored Procedure if any.
Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

Create the stored procedure in the database.
Import or create the Stored Procedure transformation.
Determine whether to use the transformation as connected or unconnected.
If connected, map the appropriate input and output ports.
If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
Configure the session.

Findind sum of the salary

2012-07-15T20:41:00.000+05:30

Drag source and target tables into Mapping Designer.
Design the mapping as shown below.

Connect all ports from Source Qualifier transformation to Expression transformation.
Create an output variable in Expression transformation as shown below:

o_Sum_Sal = CUME(sal)

Connect all ports fromm Expression transformation to target.

Generating sequence numbers without using Sequence Generator transformation

2012-07-08T04:15:00.000+05:30

Create a target table with a field name SEQ_NO for sequence number.
Design the Mapping as shown above.
In the Expression transformation create a variable port and increment it by 1.

V_Count=V_Count+1

In the Expression transformation create a output port and assign variable port to it.

O_Count=V_Count

Connect O_Count to SEQ_NO.

Finding Maximum value in the column

2012-05-28T19:09:00.000+05:30

Description:
My source and target tables as shown below:

MY SOURCE

MY TARGET

Col_1	Col_2	Col_3
A	0	0
0	B	0
0	0	C

Col_1	Col_2	Col_3
A	B	C

Solution:

Drag source and taget tables in mapping
Create an Aggregator t/f and connect all ports from Source Qualifier t/f to Aggregator.
In Aggregator t/f, create 3 output ports as shown below:

out_Col1=MAX (Col1)
out_Col2 = MAX (Col2)
out_Col3 =MAX (Col3)

Connect all output ports to target table.

The mapping pipeline as shown above.

Stored Procedures

2012-05-24T19:48:00.000+05:30

Stored procedures provide a powerful way to code application logic that can be stored on the server. Stored procedures reduce network traffic, improve performance, and improving productivity (statements in a stored procedure only need to be written one time). Additionally, stored procedures can be used to help ensure the integrity of the database (information is entered in a consistent manner).

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Join the data of two tables which does not have common columns

2012-05-24T00:23:00.001+05:30

Description:

Lets assume I have two source files as shown below:

SOURCE 1

SOURCE 2

EmpNo	Name
100	Sivan
200	Uday
300	Chandu
400	Satish

Sal	Location
2000	Noida
3000	Banglore
1800	Hyderabad
1500	Chennai

I would like to load the target a shown below:

SNo	Name	Sal	Location
100	Sivan	2000	Noida
200	Uday	3000	Banglore
300	Chandu	1800	Hyderabad
400	Satish	1500	Chennai

Solution:

Drag Source1, Source2, Target tables.
Create a Expression t/f and connect SQ_Sorce1 to Expression t/f.
Create a variable port say v_Dummy and assign default value as 1.
Create a Expression t/f and connect SQ_Sorce2 to Expression t/f.
Create a variable port say v_Dummy (with same name and data type used in previous pipeline) and assign default value as 1.
Join two piplines to a Joiner t/f. The Joinner condition v_dummy=v_dummy.
Connect all ports to the target.

Update Strategy transformation

2012-05-17T16:17:00.000+05:30

Update Strategy transformation Active and Connected Transformation. The Update Strategy transformation to update, delete or reject rows coming from source based on some condition.

For example; if Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In Data warehouse, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In PowerCenter, we set the update strategy at two different levels:

Within a session
Within a Mapping

Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default.

Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

Insert: Select this option to insert a row into a target table.
Delete: Select this option to delete a row from a table.
Update: We have the following options in this situation:

Update as Update. Update each row flagged for update if it exists in the target table.
Update as Insert. Inset each row flagged for update.
Update else Insert. Update the row if it exists. Otherwise, insert it.

Truncate: Select this option to truncate the target table before loading data.

Flagging Rows within a Mapping:

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition. You can write these expression in Properties Tab of Update Strategy transformation.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

Note: We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. To do, see the Properties Tab for the Option.

Interview questions on Lookup transformatioin?

2012-05-15T22:53:00.001+05:30

Q. What is a lookup transformation?

Ans: The Lookup transformation is passive transformation. Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.

There are two types of Lookups in Powercenter Designer, namely:

Connected Lookup
Unconnected Lookup .

Q. What are the tasks of a lookup transformation?

Ans: We can use the Lookup transformation to perform following:

Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

Q. How do you configure a lookup transformation?

Ans: Configure the lookup transformation to perform the following types of lookups:

Relational or flat file lookup
Pipeline lookup
Connected or unconnected lookup
Cached or uncached lookup

Q. What is a pipeline lookup transformation?

Ans: A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Q. What is connected and unconnected lookup transformation?

Ans: A connected lookup transformation is connected the transformations in the mapping pipeline. It receives source data, performs a lookup and returns data to the pipeline.

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation. Unconnected lookup does not participate in mapping data flow. In fact it is not connected to the other transformation so Informatica server creates a separate cache for unconnected and processing takes place in parallel. That’s why performance increases.

The major advantage of unconnected lookup is its reusability. We can call an unconnected lookup multiple times in the mapping unlike connected lookup.

Q. What are the differences between connected and unconnected lookup transformation?

Connected lookup transformation receives input values directly from the pipeline. Unconnected lookup transformation receives input values from the result of a :LKP expression in another transformation.
Connected lookup transformation can be configured as dynamic or static cache. Unconnected lookup transformation can be configured only as static cache.
Connected lookup transformation can return multiple columns from the same row or insert into the dynamic lookup cache. Unconnected lookup transformation can return one column from each row.
If there is no match for the lookup condition, connected lookup transformation returns default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. If there is no match for the lookup condition, the unconnected lookup transformation returns null.
In a connected lookup transformation, the cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. In an unconnected lookup transformation, the cache includes all lookup/output ports in the lookup condition and the lookup/return port.
Connected lookup transformation passes multiple output values to another transformation. Unconnected lookup transformation passes one output value to another transformation.
Connected lookup transformation supports user-defined values. Unconnected lookup transformation does not support user-defined default values.

Q. How do you handle multiple matches in lookup transformation? or what is "Lookup Policy on Multiple Match"?

Ans: "Lookup Policy on Multiple Match" option is used to determine which rows that the lookup transformation returns when it finds multiple rows that match the lookup condition. You can select lookup to return first or last row or any matching row or to report an error.

Q. What is "Output Old Value on Update"?

Ans: This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Q. What is "Insert Else Update" and "Update Else Insert"?

Ans: These options are used when dynamic cache is enabled.

Insert Else Update option applies to rows entering the lookup transformation with the row type of insert. When this option is enabled the integration service inserts new rows in the cache and updates existing rows when disabled, the Integration Service does not update existing rows.
Update Else Insert option applies to rows entering the lookup transformation with the row type of update. When this option is enabled, the Integration Service updates existing rows, and inserts a new row if it is new. When disabled, the Integration Service does not insert new rows.

Q. What are the options available to configure a lookup cache?

Ans: The following options can be used to configure a lookup cache:

Static cache
Dynamic cache
Persistent cache
Recache
Shared Cache

Q. What is a cached lookup transformation and uncached lookup transformation?

Ans:

Cached lookup transformation: The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.
Uncached lookup transformation: For each row that enters the lookup transformation, the Integration Service queries the lookup source and returns a value. The integration service does not build a cache.

Q. How the integration service builds the caches for connected lookup transformation?

Ans: The Integration Service builds the lookup caches for connected lookup transformation in the following ways:

Sequential cache: The Integration Service builds lookup caches sequentially. The Integration Service builds the cache in memory when it processes the first row of the data in a cached lookup transformation.
Concurrent caches: The Integration Service builds lookup caches concurrently. It does not need to wait for data to reach the Lookup transformation.

Q. How the integration service builds the caches for unconnected lookup transformation?

Ans: The Integration Service builds caches for unconnected Lookup transformations as sequentially.

Q. What is a dynamic cache?

Ans: To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.

Q. When you use a dynamic cache, do you need to associate each lookup port with the input port?

Ans: Yes. You need to associate each lookup/output port with the input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache.

Q. What are the different values returned by NewLookupRow port?

Ans: The different values are

0 - Integration Service does not update or insert the row in the cache.
1 - Integration Service inserts the row into the cache.
2 - Integration Service updates the row in the cache.

Q. What is a persistent cache?

Ans: If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.

Q. What is Recache?

Ans: If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.

Q. What is a shared cache?

Ans: You can configure multiple Lookup transformations in a mapping to share a single lookup cache. The Integration Service builds the cache when it processes the first Lookup transformation. It uses the same cache to perform lookups for subsequent Lookup transformations that share the cache. There are two types of Shared Cache. They are...

Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Q. How do you improve the performance of lookup transformation?

Ans:

Create an index on the columns used in the lookup condition
Place conditions with equality operator first
Cache small lookup tables.
Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.
Use persistent cache for static lookups.
Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
For flat file lookups, provide Sorted files as lookup source.

How to return muliple values using Unconnected lookup?

2012-05-12T12:06:00.002+05:30

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

Let us assume EMP will be source table and DEPT will be LOOKUP table.
Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

Then concatnate multiple ports with the separator '&' into single port in Lookup transformation.

To do edit Lookup transformation and create a return port (DNAME).
Then go to Properties tab, there write the following expression in Lookup SQL Over.

SELECT dname||'&'||loc FROM dept

In expression transformation spilt it into multiple ports as follow.
Declare 2 output variables with

o_DName = SUBSTR (dname, 1, (INSTR(dname, '&', 1))
o_Loc = SUBSTR(dname, INSTR (dname, '&'))

Connect all ports from Expression transformation to target table.

SUBSTR function in Sql

2012-05-12T08:43:00.002+05:30

The SUBSTR functions allows you to extract a substring from a string. The syntax for the substr function is:

SUBSTR ( string, start_position, [ length ] )

Where

string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note:

If start_position is 0, then SUBSTR treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then SUBSTR starts from the beginning of the string.
If start_position is a negative number, then SUBSTR starts from the end of the string and counts backwards.
If length is a negative number, then SUBSTR will return a NULL value.

For Example:

SUBSTR ('This is a Informatica blog', 11, 11) return 'Informatica'
SUBSTR ('This is a Informatica blog', 11) return 'Informatica blog'
SUBSTR ('This is a Informatica blog', -4, 4) return 'Blog'
SUBSTR ('This is a Informatica blog', -16, 4) return 'Info'

INSTR function in Sql

2012-05-12T01:37:00.001+05:30

The INSTR function returns the location of a substring in a string. The syntax for the INSTR function is:

INSTR ( string1, string2 [, start_position [, n^th_appearance ] ] )

Where

string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
n^th appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

Note: If string2 is not found in string1, then the INSTR Oracle function will return 0.

Example:

INSTR ('This is Informatica blog', 'i') return 3; the first occurrence of 'i'
INSTR ('This is Informatica blog', 'i', 1, 1) return 3; the first occurrence of 'i'
INSTR ('This is Informatica blog', 'i', 1, 2) return 6; the second occurrence of 'i'
INSTR ('This is Informatica blog', 'i', 1, 3) return 9; the third occurrence of 'i'
INSTR ('Tech on the net', 'e', -3, 2) return 2.

Split non-key columns

2012-05-12T01:33:00.000+05:30

Description: I have a source table with 3 columns EMPNO, ENAME, LOC. Now I would like to split the source into 2 taget tables based on the primary key EMPNO as one target table contains EMPNO, ENAME and another target table contains EMPNO, LOC as shown below.

MY SOURCE

TARGET_1

TARGET_2

SNo	Name	Loc
100	Chandu	Hyderabad
200	Satish	Chennai
300	Siva	Noida
400	Srinivas	Bengaluru

EmpNo	EName
100	Chandu
200	Satish
300	Siva
400	Srinivas

EmpNo	EName
100	Hyderabad
200	Chennai
300	Noida
400	Bengaluru

Solution:

Connect all ports from Source transformation to Aggregate transformation.
Connect EMPNO and ENAME ports to one target table.
And connect EMPNO and LOC ports to another target table.
The final mapping looks like as shown below:

Sending second half records of the table to target

2012-05-10T19:38:00.001+05:30

Drag source and target into the mapping.
Let us assume EMP is source, and create a new table say T_EMP with same structure as EMP.
Create the mapping as shown below:

Now edit Source Qualifier tranformation and click on properties tab.
Enter the following query in SQL Query property:

SELECT * FROM emp

MINUS

SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM emp

Save the mapping.
Finally create session, Workflow and run thee workflow.

Sending first half records of the table to target

2012-05-10T19:34:00.002+05:30

Drag source and target into the mapping.
Let us assume EMP is source, and create a new table say T_EMP with same structure as EMP.
Create the mapping as shown below:

Now edit Source Qualifier tranformation and click on properties tab.
Enter the following query in SQL Query property:

SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM emp

Save the mapping.
Finally create session, Workflow and run thee workflow.

Lookup Caches

2012-05-09T12:44:00.001+05:30

We can configure a Lookup transformation to cache the lookup table. The Integration Service (IS) builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the IS stores the overflow values in the cache files. When session completes, IS releases cache memory and deletes the cache files.

Note:

If we use a flat file lookup, the IS always caches the lookup source.

We set the Cache type in Lookup Properties.

Lookup Cache Files:

Lookup Index Cache: Stores data for the columns used in the lookup condition.

Lookup Data Cache: For a connected Lookup transformation, stores data for the connected output ports, not including ports used in the lookup condition. For an unconnected Lookup transformation, stores data from the return port.

Types of Lookup Caches:

Static Cache:

By default, the IS creates a static cache. It caches the lookup file or table and looks up values in the cache for each row that comes into the transformation. The IS does not update the cache while it processes the Lookup transformation.

Dynamic Cache:

To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.

Persistent Cache:

If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.

Recache from Source:

If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.

Shared Cache:

There are two types of Shared Cache. They are...

Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Concatinate duplicate values

2012-05-08T19:12:00.007+05:30

MY SOURCE

MY TARGET

SNo	Name
1	A
2	B
1	C
3	D
4	F
1	E
2	G

SNo	Name
1	A
1	AC
1	ACE
2	B
2	BG
3	D
4	F

Solution:

Here the no. of rows in source and target tables are same; so here we think about Passive transformations.
After importing Source and Target, create a Sorter transformation to sort the data.
Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
Give the expressions as follows

v_Name as variable port

IIF(SNo = v_SNo, v_Name||Name, Name)

v_SNO as variable port

SNo

o_Name as output port

v_Name

The final mapping looks like..

Finally connect the ports to Target, create and execute the session and workflow.

Difference between Connected and UnConnected Lookup Transformation:

2012-05-08T05:22:00.001+05:30

Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from :LKP expression in another transformation.
Connected lookup trasformation will process each and every row whereas UnConnected lookup will not process each and evry row. It will return the values based expression condition.
If the LKP condition is not matched the lookup transformation will return the default value in case of Connected lookup. If no match found for the LKP condition, the lookup transformation will return null values in case of Unconnected lookup.
Connected lookup transformation can use static or dynamic cache, whereas Unconnected lookup can use only static cache.
Connected lookup returns multiple columns from the same row whereas the UnConnected lookup has one return port and returns one column from each row.
Connected lookup supports user-defined default values and the UnConnected lookup does not support user defined values.

Unconnected Lookup transformation

2012-05-07T06:52:00.003+05:30

Add input ports.
Add the lookup condition.
Designate a return value.
Call the lookup from another transformation.

Example for create a unconnected Lookup Transformation

Let us assume EMP will be source table and DEPT will be LOOKUP table.
Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus LOC.
Go to the Mapping Designer.
Click Mapping-> Create-> Give name. Ex: m_UNCONN_LKP
Drag Source and Target table.
Creat an EXPRESSION transformation and pass all ports from Source Qualifier to EXPRESSION transformation.
Then connect all fields from EXPRESSION to target except DNAME.
Create a LOOKUP transformation.

As DEPT is the Source definition, click Source and then Select DEPT.
Click Ok.
Now Edit the Lookup Transformation. Go to Ports tab.
As DEPTNO is common in source and Lookup, create a port IN_DEPTNO ports tab. Make it Input port only and Give Datatype same as DEPTNO.

Designate LOC as Return Port. Check on R to make it.
Now add a condition in Condition Tab.
DEPTNO = IN_DEPTNO and Click Apply and then OK.
Now we need to call this Lookup from Expression Transformation.
Edit Expression transformation and create a new output port out_LOC of datatype as LOC.

Open the Expression editor and call Lookup as given below:
We double click Unconn in bottom of Functions tab and as we need only DEPTNO, we pass only DEPTNO as input.

Write the expression as :LKP.LKPTRANS(DeptNo)
The final mapping looks like as below..

Validate the call in Expression editor and Click OK.
Then save the mapping.
Create Session and Workflow. Run the workflow and see the data in target table.
Note:
Make sure to give connection information for all tables.
Make sure to give connection for LOOKUP Table also.

Connected Lookup Transformation

2012-05-05T19:22:00.002+05:30

Let us assume that EMP will be source table. DEPT will be LOOKUP table.
Create a target table T_CONN_LKP_EMP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
Creating Mapping by click Tools -> Mapping Designer.
Click Mapping-> Create-> Give name. Ex: m_CONN_LKP_EMP
Drag EMP and Target table.
Connect all fields from SQ_EMP (Source Qualifier transformation) to target except DNAME and LOC.
Then go to Transformation-> Create -> Select LOOKUP from list. Give name and click Create.

As DEPT is the Source definition, click Source and then Select DEPT.
Click Ok.
Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.
Now go to CONDITION tab and add CONDITION.
DEPTNO = IN_DEPTNO and Click Apply and then OK.
We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.
The final mapping look like as shown below..

Then Mapping -> Validate and save the mapping.
Create Session and Workflow. Run the workflow and see the data in target table.
Make sure to give connection information for all tables.
Make sure to give connection for LOOKUP Table also.
We use Connected Lookup when we need to return more than one column from Lookup table.

Lookup transformation

2012-05-05T05:30:00.001+05:30

The Lookup transformation is passive transformation.

Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.
There are two types of Lookups in Powercenter Designer, namely:

Connected Lookup
Unconnected Lookup .

We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. We can use multiple Lookup transformations in a mapping.

Lookup transformation act as left outer joiner. If a particular null record matches found, then it returns null value.

The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Different caches can also be used with lookup like static, dynamic, persistent, and shared. The dynamic cache cannot be used while creating an un-connected lookup.

We can use the Lookup transformation to perform following:

Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

TIP: We use Connected Lookup when we need to return more than one column from Lookup table.

Source Qualifier transformation

2012-05-01T18:39:00.004+05:30

Source Qualifier transformation is active and connected transformation.

The Source Qualifier transformation represents the rows that the PowerCenter Server reads when it runs a
session.

It is only transformation that is not reusable. And it is the default transformation except in case of XML or COBOL files.

Tasks to performed by Source Qualifier:

Join data originating from the same source database: We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.
Filter rows when the PowerCenter Server reads source data: If we include a filter condition, the PowerCenter Server adds a WHERE clause to the default query.
Specify an outer join rather than the default inner join: If we include a user-defined join, the PowerCenter Server replaces the join information specified by the metadata in the SQL query.
Specify sorted ports: If we specify a number for sorted ports, the PowerCenter Server adds an ORDER BY clause to the default SQL query.
Select only distinct values from the source: If we choose Select Distinct, the PowerCenter Server adds a SELECT DISTINCT statement to the default SQL query.
Create a custom query to issue a special SELECT statement for the PowerCenter Server to read source data: For example, you might use a custom query to perform aggregate calculations.

What are the Joins?

2012-04-29T22:23:00.003+05:30

In SQL, a join is a relational operator that combines data from multiple tables into a single result set. The Joiner transformation acts in much the same manner, except that tables can originate from different databases or flat files.

Types of Joins:

Normal joins: With a normal join, the PowerCenter Server discards all rows of data from the master and detail source that do not match, based on the condition.
Master Outer join: This join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
Detail Outer join: This join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
Full Outer join: A full outer join keeps all rows of data from both the master and detail sources.

Example:

In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join:

All employees of 10, 20 and 30 will be there as only they are matching.

Synax: DEPT.DEPTNO=EMP.DEPTNO

Master Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL.

Syntax: {EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Detail Outer Join:

All employees of 10, 20 and 30 will be there. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Full Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Caution: Curly braces are needed in Syntax.

Note: A normal or master outer join performs faster than a full outer or detail outer join.

Joiner Transformations

2012-04-29T06:00:00.000+05:30

Joiner transformations connected and active transformation.

Joiner transformation is an example for horizontal merging of tables..

Used to join source data from two related heterogeneous sources residing in different locations or file systems. Or, we can join data from the same source.

If we need to join 'n' tables, then we need 'n-1' Joiner transformations.

The Joiner transformation requires atleast two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources.

Joiner can't be used in following conditions:

Either input pipeline contains an Update Strategy transformation.
We connect a Sequence Generator transformation directly before the Joiner transformation.

Workig with the Joiner transformation:

In the Mapping, import create Joiner Transformation.
Edit Joiner Transformation -> Condition Tab
Add condition

In Joiner, one table is called as MASTER and other as DETAIL.
MASTER table is always cached. We can make any table as MASTER.
Edit Joiner Transformation -> Ports Tab -> Select M for Master table.

We can add as many conditions as needed.
Only = operator is allowed.
Set Master in Ports tab.
Mapping -> Validate
Repository -> Save.
Create Session and Workflow. Run the workflow and see the data in the target table.

Points to remember:

Table with less number of rows should be made MASTER to improve performance. Because
When the PowerCenter Server processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. So table with fewer rows will be read fast and cache can be made as table with more rows is still being read.
The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.
If we join Char and Varchar datatypes, the PowerCenter Server counts any spaces that pad Char values as part of the string. So if you try to join the following:

Char (40) = “abcd” and Varchar (40) = “abcd”

Then the Char value is “abcd” padded with 36 blank spaces, and the PowerCenter Server does not join the two fields because the Char field contains trailing spaces.

The Joiner transformation does not match null values.

Any one of the pipeline contains Sequential transformation, then the Joiner transformation rejects the records.