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.

Operator Type at runtime - OMB command

It's possible to get all the operator names within a mapping.
It's possible to list operator names as per operator type like Joiner, Sorter, Filter,etc within a mapping.

While coding an OMB script, i needed an OMB command to find the operator type from operator name at runtime.

How can we get Operator Type ( whether its a joiner,cube,dimension,etc) from the operator name within a mapping ?

Undocumented Operator property "STRONG_TYPE_NAME" can be used to get it.

Command:-
OMBRETRIEVE MAPPING 'MAPPING_NAME' OPERATOR 'OPERATOR_NAME' GET PROPERTIES (STRONG_TYPE_NAME)

Result:-
oracle.wh.service.impl.mapping.component.Join (If the operator is Joiner )

oracle.wh.service.impl.mapping.component.Cube (If the operator is Cube )

and so on...


A TCL logic needs to written to parse the result string ( get the last word after the last period "." ) .

Ispredefined Constant ?

Whenever you drag a Constant operator into your OWB mapping and define a constant , there is a check box with "Ispredefined Constant" under the attribute properties. What is "Ispredefined Constant"?

This topic is covered on lot of forums and blogs but i would like to go over it again.

I find two good uses of it.
1) On generation of OWB code for any mapping , constants are generated by OWB like
get_model_name (name of the mapping)
get_selected (number of rows selected)
get_inserted (number of rows inserted)
get_updated (number of rows updated)
etc..




















These constants can be used within your mappings to get value at runtime.


For eg: If you are trying to get mapping name you can use the "get_model_name" in the expression value of the constant with Ispredefined checkbox checked. Make sure you do NOT put single quotes around the expression value.
























2) Whenever you declare a constant in OWB mappings make sure you always check the Ispredefined constant checkbox, this helps whenever you are testing your mappings by generating intermediate code.

If you do not have Ispredefined Constant checked, OWB generates some internally generated name instead of expression value.

With Ispredefined constant set the expression value is subsititued automatically



















Note: OWB 10.2.0.4 generates the right intermediate code without needing to check the Ispredefined Constant checkbox.