Working with Stored Procedure Transformation in informatica

We have connected and unconnected in Stored procedure transformations in informatica

For connected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_agg (in_deptno in number, max_sal out number,

min_sal out number, avg_sal out number, sum_sal out number)

As

Begin

select max(Sal),min(sal),avg(sal),sum(sal) into max_sal,min_sal,avg_sal,sum_sal

from emp where deptno=in_deptno group by deptno;

End;

/

Execute the above script in TOAD or Database

import stored procedure in mapping area directly connect to target.

For unconnected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_unconn_1_value(in_deptno in number, max_sal out number)

As

Begin

Select max(Sal) into max_sal from EMP where deptno=in_deptno;

End;

/

Execute the above script in TOAD or Database

call stored procedure same as like unconnected lookup

use expression transformation for calling Unconnected stored procedure,

in functions tab in expression window

Easy steps to do SCD Type 1

SCD type 1 explanation:

Slowly Changing dimension Type 1 doesn't carry the history simply value will be updated with new value. 

Source Table: Customer source table

Target Table: Customer target Stage table

Mapping Design 

Step 1: 

Step 2:

Lookup condition 

Step 3:

Step 4:

Expression Transformation new ports

Step 5:

Expression For changed Flog 

Step 6:

Expression For New Flog

Step 7:

Map to filters from Expression

Step 8:

1st filter condition 

Step 9:

2nd Filter Condition 

Step 10:

connect two filters to two different Update Strategies 

For Inserts Condition : DD_INSERT

For Updates Condition : DD_Update

Step 11:

Connect to target from Update Strategies

Use Target Instance for it

use sequence generator to generate the sequences 

Finally using those Sequence numbers LKP will find the new and changes records in Type 1 Dimension.

Informatica Architecture in brief