Sunday, March 29, 2009

Insert Operator - Expert

Inserting an Operator in a mapping between different connected operators is a time consuming task considereing if you have mappings with complex logic and hundreds of columns.

Often a small change in requirement (like adding new columns or change in business logic) can result in deleting hundreds of connection from one operator to another for inserting a new operator in a mapping. Recreating connection again increases the development and testing effort, since all the connections have been created manually again.

Created an expert which can insert some selected operators (aggregator, set_operator, deduplicator, expression, joiner, splitter, sorter, and filter) anywhere in a mapping retaining all connections, if logically possible. Eventually less development,maintanence and testing effort.

Expert can be downloaded here. Expert does not come with any WARRANTY !!!!!

Expert is designed to insert an operator in a mapping, retaining all the existing connections.
This Expert is governed by the OWB laws of designing a mapping.

A quick demo how to use it.

Download the zip file and Import the expert and you should see it under Global Explorer/Public Expert/MAP_ACC/INSERT_OPERATOR


Add expert to a mapping. Right click on a mapping (in my case mapping name is "TEST" ) and select "Add/Remove Experts Here" option and select the "INSERT_OPERATOR" expert


***Time to test the expert ****

Mapping “TEST” populates a table with 120+ columns and has more than 20 key lookups and multiple joins, expressions, etc.Let’s insert a filter operator after Joiner "JNR_ALL" in the mapping "TEST" as shown below
Right click on the mapping and Select "Insert Operator"




A window will pop up.
Select "FILTER" as your Insert Operator.
You can select where you want to insert the operator "BEFORE" or "AFTER" the map operator.

Select "AFTER" as your position and Select "JNR_ALL" as map operator.
Check Box "Default outgoing connections from the new operator" will allow you to remap connections from the new operator to the existing default values.

Press "OK" to continue


After the expert has run, Open the mapping and a filter is inserted and all the connections are retained.


Now lets insert a sorter in the same mapping before the target table (PS_D_CRSE_CLASSMTG_STG).



Open the mapping after the expert has run and a new sorter is inserted and all the connections retained.


Note: Expert does not adds any Conditional logic (like filter or joiner condition) to any inserted operators.

6 comments:

  1. Excellent. This would be of great help.

    - John

    ReplyDelete
  2. Hi Suraj,

    Excellent work. I was able to import and upgrade this into OWB 11gR2 successfully. But 11gR2 does not seem to add this into the menu. I could not find a way to run this expert in 11gR2. Do you know how to make this work in 11gR2?

    Thanks

    Ravi

    ReplyDelete
  3. Ravi,

    Open/Edit the expert.
    Select the OMB operator CHECK_LAUNCH_CONTEXT
    In the task editor panel , select the "Main" Tab

    Remove the existing code and replace with the following code ---

    set L_LAUNCHCNTX 0
    if {$EXP_LAUNCH_CONTEXT_TYPE == "BATCH_MAPPING"} {
    set L_CNTX [replacetoken $EXP_LAUNCH_CONTEXT "\/" " "]
    set L_WRKSPC [ lindex $L_CNTX 0 ]
    set L_PRJ [lindex $L_CNTX 1]
    set L_MOD [lindex $L_CNTX 2]
    set L_MAP [lindex $L_CNTX 3]
    } else {
    set L_LAUNCHCNTX 1
    }

    Commit and exit OWB.

    Relogin and add the expert to any mapping and then you should be able to use it.

    This code change will work for both 11gr1 and 11gr2.

    Regards,
    Suraj.

    ReplyDelete
  4. Hi Suraj,

    Thanks a lot. Finally I was able to use this operator. Repository owner was required to login and add the expert to the Menu.

    ReplyDelete
  5. I am using this expert pretty regularly, but is there any way to make it work with Pluggable Mappings as well as regular Mappings? Many thanks!

    ReplyDelete
    Replies
    1. Yes, It can be customized to work against Pluggable Mappings.

      Delete