Reusable Mappings in ODI 12c

Eg Scenario : We need to pass analytical Functions to avoid the duplicate records:

Here in the Expression we have taken extra column ( S.NO) and adding analytical Function ( Row_number)

 

 

My Requirement to find out Unique columns, Hence need to pass Filter Transformation , and write Condition as Sno = 1

And Link to Target , Give integration type as IKM Oracle Insert.GLOBAL.

 

Save and Execute , Here mapping will be failed because to the analytical functions we cannot pass Where clause directly we need to write in inline view ..

Eg:  select * from (

select emp.*,

Row_Number() over (partition by EMPNO order by SAL desc) SNO from emp ) Temp where SNO =1;

To achieve this ( to write inline query) we need to create Reusable mapping

When you create a Reusable mapping , in the Components section, you will find 2 extra components

1.       Input Signature

2.       Output Signature

Or you can directly drag source to reusable mapping design ( but you cannot drag target directly we need take ouput signature only ) … ( here am directly dragging my source and expression link each other)

    

Now drag output signature and link from Expression  and save it

Now the reusable mapping is ready with analytical Function

 

Now create a new mapping (Original)

Call Reusable mapping to design, Observe here  we are having only Output signature , we do not have any input signature as we have taken direct source in reusable mapping ..

Now Lets apply Filter on SNO column to get Unique data only..

Drag a filter and link each other ( reusable mapping source and filter ) and write filter condition

Now Load this to target ..

Drag target and link to Filer..

Save and Execute ,,, the Mappings Fails again

Reusable mapping logic contains ( sub query) so you need to check subselect enabled.. as shown below ..

Save and execute 

Now the Mapping will be Executed successfully..

Observe inline view and where clause ..

 

 

Note : Reusable Mapping are not used for any performance but if you want to write same logic in Multiple  mappings instead of writing logic in each mapping you can create one reusable mapping  and call to required mappings..