Why Sorter transformation is Active ? How to avoid duplicate records using sorter and Source Qualifier with screen shot examples

Hello all,

In this topic, I am gonna explain about why sorter transformation is active and How to avoid duplicate records using sorter transformation and source qualifier.

There are two ways to eliminate duplicate records
1. By using source qualifier
2.Using sorter transformation

Source qualifier

Edit properties >> Select Distinct( By selecting this option, we can eliminate the duplicate records.  Kindly check the pic below:

Using sorter transformation , we can eliminate the duplicate records  . Kindly check the screenshot below:

Before we sort the records, there are 5 records in the table including 2 duplicate records

Sorter Mapping below:

Edit sorter transformation properties and select distinct . (check the pic below)

After selecting the distinct properties in the sorter transformation, workflow has been executed and it eliminated the duplicate records. In the table, now  u can see that duplicate records has been eliminated. It retrieved only 4 records. so it has been proved that sorter transformation is an active transformation

Parameters in Informatica

There are parameters available in various levels like Session, Mapping, Workflow in Informatica

Mapping Parameter:
Mapping Parameters are basically a constant value which will define before running the session and that constant value will be used throughout the session.
E.g. $$LastRunDate( It can be identified with $$ sign)
We can define the parameter in parameter file like .par extension and we can use this parameter file in
mapping.

Here is the screenshot to declare mapping parameter.
Mapping designer>> mappings>> Parameter variable

Session Parameter:


Session Parameters are used to define the source or target database connections

Constraint Based Loading in Informatica

Hello all,

Let us explore what is is constraint based loading and what is the use of it in Informatica.

Constraint Based Loading is basically a session level property, when we load the table in to multiple target s from a source, it should load the parent table first and child table next.  We cannot load the child table and then load the parent table.

Constraint based loading will work under the following condition:

* Target table must have parent-child relationship
*In the properties general tab in Session, select "Treat rows as Insert".( Updates cannot be used with constraint based loading while insert)

* one source to multiple targets(must have key relationships), we must use constraint base loading.

To achieve constraint based loading , In Joiner transformation>> Session Properties>> config>> enable the Constraint based loading option ( check the pic below)

Kindly share if you have any suggestions .

Update/Insert records using Update strategy transformation

Hello all.

Here I am going to explain how to insert new records and udpate existing records using udpate strategy transformation.
Scenario: Insert & Update the records :
the Source flat file records(csv) in to target oracle database which has already 2 records.
Requirements:
Source : Flat file(csv file contains 5 records)
Flat file : csv file with 5 records.
Target : Oracle database.

Step1: For initial load, Create table and insert 2 rows in the target database.Here we used Oracle Database

Create table statement:

create table cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp) ;

Inserting records for Initial Load in Target Oracle database:

insert into cust_addr values(1,'1mainst', 'VA', sysdate, sysdate);
insert into cust_addr values(3,'30mainst', 'VA', sysdate, sysdate);

Step2: Create Source Flat file in csv format : Flat file should look like below:

*****************
Flat File (csv)
*****************
1,1mainst,VA
2,20mainst,VA
3,30mainst,VA
1,100mainst,VA
5,50mainst,VA
*******************
*Open Informatica power center Designer >> Tools>>Source analyzer >> Sources >> Import File>> Slect flat file from your desktop or flatfile location>> ( Now flat file is ready to use in mapping)
*Tools>> Target Designer >> Targets >>Targets from Database>> select table "cust_addr" (Now target db is ready to use in mapping)
*Mapping designer >> Drag source flatfile and target file in to mapping design

To achieve update / insert the records, we need update strategy transformation for update .
To confirm the updated records or insert the records, we need timestamp/datetime in expression transformation.
Expression Transformation:

We have following ports/column in the expression transformation : cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp, v_sysdate).
Here in the expression declare variaple port v_sysdate = sysdate , declare created_dt & modified_dt = v_sysdate as output port.

Update strategy Transformation:

Properties>Transformation attribute> Update strategy expression value is 1.

Target Definition:

*Make sure that created_dt& modified_dt data type is time stamp and make cust_id as primary key.

Create session and workflow >> seesion properties>>target definition>> Edit task>>Mapping>> properties >>attribute>>Insert, Update else insert, delete must be clicked..

In the connection properties make sure that we are selecting the right source/target.

Run the workflow..Records must be updated/inserted .

 Target table data structure Before update/insert

Target table data structure after mapping & executed the workflow

In the Results, You can observe that 4 records are there.

1. It eliminates the the duplicate record(since cust_id declared as primary key in target) . New record has been updated .

2- New records has been inserted.
Create table sql
Target file Initial load<< Initial load target file is here
SourceBS to CSV file << Source flat file recs are here
Mapping xml file << Complete mapping xml file is here

Note: Above link works only if you have gmail account.

Update existing record and insert new records using update strategy and expression transformation

Hello all.

Here I am going to explain how to insert new records and udpate existing records using udpate strategy transformation.
Scenario:  Insert & Update the records :
 the Source flat file records(csv) in to target oracle database which has already 2 records.
Requirements:
Source  : Flat file(csv file contains 5 records)
Flat file : csv file with 5 records.
Target : Oracle database.

Step1: For initial load, Create table and insert 2 rows in the target database.Here we used Oracle Database

Create table statement:

create table cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp) ;

Inserting records :
insert into cust_addr values(002,'20mainst', 'VA', sysdate, sysdate);
insert into cust_addr values(003,'30mainst', 'VA', sysdate, sysdate);
Step2: Create Source Flat file in csv format : Flat file should look like below:

*****************
Flat File (csv)
*****************
1,1mainst,VA
2,20mainst,VA
3,30mainst,VA
4,100mainst,VA
5,50mainst,VA
*******************
*Open Informatica power center Designer >> Tools>>Source analyzer >> Sources >> Import File>> Slect flat file from your desktop or flatfile location>> ( Now flat file is ready to use in mapping)
*Tools>> Target Designer >> Targets >>Targets from Database>> select table "cust_addr" (Now target db is ready to use in mapping)
*Mapping designer >> Drag source flatfile and target file in to mapping design

To achieve update / insert the records, we need update strategy transformation for update .
To confirm the updated records or insert the records, we need timestamp/datetime in expression transformation.
Expression Transformation:

We have following ports/column in the expression transformation : cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp, v_sysdate).
Here in the expression declare variaple port v_sysdate = sysdate ,  declare created_dt & modified_dt = v_sysdate as output port.

Update strategy Transformation:

Properties>Transformation attribute> Update strategy expression value is 1.

Target Definition:

*Make sure that created_dt& modified_dt data type is time stamp and make cust_id as primary key.

Create session and workflow >> seesion properties>>target definition>> Edit task>>Mapping>> properties >>attribute>>Insert, Update else insert, delete must be clicked..

In the connection properties make sure that we are selecting the right source/target.

Run the workflow..Records must be updated/inserted .

 Target table data structure Before update/insert

Target table data structure after mapping & executed the workflow

Complex Mapping in Informatica

Hello all,

I am here to learn and share my knowledge about Complex Mapping in Informatica.

Complex Mapping are basically contains 'n' number of mappings based on the complex business logic. In General, Complex Mapping are controlled by the Informatica designers who chose it and develops according to the complex business logic. When we design the complex mappings, the designer should consider in terms of Manageability  Scalability & flexibility. Managing the Complex mapping is not easy unless it is designed in that way. In order to Maintain Complex mapping ,It is advisable to split the mappings in to multiple pieces. Designers should considered and  give equal importance to the Scalability, Flexibility & Maintainability while designing the complex mappings. Since complex mapping has lot of transformations, designer might face some challenges in modify or debug when there is a need. I personally feel that it is up to the designers choice the one who makes the call and control on it. Complex mapping should be easy to maintain, easy to extend & easy to scale.

Any Experts can correct me if i am wrong and kindly suggest me the best if you can on this complex mapping.