Introduction to Operation-Codes & The Behavior of Map_Operation Transform when used individually.

Although it is not very complex transform, but let us go somewhat deeper into the basics of it and find out the basic needs of this transform. and a view of operation-codes for beginners.

The purpose of Map_Operation transform is to map/change the Operation-Code of incoming/input row to the desired Operation-Code/Operation to be performed on the target table.

Why We need it After all? : It might be the case that the incoming/input row needs to be updated in the target table ( because some data has been changed since the target was populated last time) , but instead of updating it in the Target_table, you are willing to insert a new row and keep the older one as well OR you might be willing to delete the changed row from the target table OR you might be willing to do nothing at all for the rows which are changed. Hence, the operation-codes are the arms which help doing all these things.

But what are these Operation-Codes? : Let us suppose you have one source table named “source_table” and one target table named “target_table” . Both have exactly same schema. Now , in the first run you populate the target_table with all the data in source_table , hence both tables have got exactly the same data as well. Now some changes are done on the Source_table, few new rows are added , few rows got updated , few rows are deleted . Now if we compare the Source_table with the Target_table and try to set the status of each row in the Source_table relative to the Target_table then we’ll have one of the following statuses for each input row :

-The rows which are there in the Source_table and not in the Target_table, basically the new rows: These rows need to be inserted to the Target_table, hence the Operation-Code “insert” will be associated with these rows coming from the Source_table.

-The rows found in both tables, but are updated in the Source_Table : These rows need to be updated in the Target_table, hence the Operation-Code  “update” will be associated with these rows coming from the Source_table.

-The rows which are there in Target_Table and deleted from Source_Table after the last run. These rows need to deleted from the Target_table (although we hardly perform deletion in a datawarehouse), hence the Operation-Code “delete” will be associated with each row of this kind.

-The rows which are there in both of the tables. These rows ideally doesn’t need any operation , hence the Operation-Code “normal” is associated with such rows.

Well, how to perform this comparison of Source and Target Tables? : This can be done by the Table_Comparison transform. It compares the input table (Source_table in our example) with the another table called as comparison_table in BODS jargon (Target_table in our recent example). and after comparing each row it associates an Operation-Code to each row of the input table. and if we choose , it also detects the rows which were deleted from input table (to choose whether we need to perform deletion on Target_table or not). But we are not going in the details of Table_Comparison transform here, as i was going to play with the map_operation transform alone, and i know it looks crazy to do so. Because , like in the figure given below, if i connect the Source Table directly to the Map_Operation transform, “by default” the operation-code associated with all rows is “normal”, until we change it using table_comparison transform.

Playing with Basics of Map_Operation Transform: So, as said earlier the job of map_operation transform is to change/map the incoming op-code (operation-code) to the desired/outgoing op-code. See the pic below we have options of “Input row type” and “Output row type”. But, in the above mapping because we have connected the source directly to the map_operation transform, All Incoming Rows Have the Op-Code “normal” Associated with them. Hence, the second, third and fourth option doesn’t matter for now because there are no input rows with operation-code “update” or “insert” or “delete” associated with them.

So, let us see what will happen if we use one by one the provided options in the drop-down menu given for the “Output row type”. The interesting ones are “update” and “delete”. let us see why:

First,  Let us suppose there is no primary-key defined on the target table :

-“normal” : If we choose “normal” as Output row type op-code, all input rows will be inserted to the target table.

-“insert” : same as “normal” , all input rows will be inserted to the target table.

-“discard”: Nothing will be done.

-“delete”: It’ll delete the matching rows from the target table. Even if the primary key is not defined on target , delete operation is performed by matching the entire row. i.e. Value of each field in the input row matched with Value of each field in the Target table row, if matched the target row is deleted. No need of a Key column for delete operation.

-“update”: This time, when no primary key is defined on the target , the update operation will not do anything. This is actually logical, I have an input row with a key field (say EmpNo) , but how can i ask my software to update the this row in target table if i haven’t mentioned the same key in the target table as well? How my software will find the row which needs to be updated ? If i say , match the entire row , then it’ll find the exact match of entire row if found, and that would mean that there is nothing to be updated as the entire row matches. So, i need to mention something (some column) in the target using which i can find the row to be updated.

So, there are two ways to do this:

First is to define a Primary key on the target table and re-import the table.

Second (assuming that input table has a primary key defined) is open the table by double-clicking on it, in the option tab we have the option to “Use Input keys” as shown below. By choosing yes here, It’ll use the Primary key mentioned in the input table, provided that the column with same name and datatype is present in the Target table as well.

Secondly If a primary key is defined on the Target table as well then the “normal” and “insert” operations will fail if a row with same primary-key value will be tried to be inserted again, the job will fail and stop.Whereas the “update” and “delete” operations would work as before.

The behavior of map_operation transform changes somewhat when it is preceded by the table_comparison transform. The need of primary key on the target Or mentioning to use the input keys (as shown above) eliminates, It updates the target rowswithout it as well. Might be because we mention the primary key column of the input table in the table_comparison transform along with the comparison columns, and might be the rows carry this information along with the associated op-code to the map_operation transform. I checked and experimented this practically but can just guess about the internal engineering of it.