Recently I started practicing SAP BODS and found interesting, as we get to achieve the scenarios fast by using BODS — Designer absolutely in the context of WYSWYG (What You See is What You Get). I would like to share a blog on using Map_Operation transformation.
From the help – Technical Manuals we learn as below:
It allows conversions between data manipulation operations.
The ‘Map_Operation’ transform allows you to change operation codes on data sets to produce the desired output. For example, if a row in the input data set has been updated in some previous operation in the data flow, you can use this transform to map the UPDATE operation to an INSERT. The result could be to convert UPDATE rows to INSERT rows to preserve the existing row in the target.
For Demo purposes, I would like to simply use Update from source to Update.
Last Name of customer is maintained incorrectly in the database and an update is recorded in a reference table pulled into Staging area. Construct a Job to save the updates into database.
Before cooking, let us plan; create two tables (Customer_ref and cust_dim_scd2) as per the screen shot in the database.
Notice the Last_name in the table Cust_dim_scd2 is maintained incorrectly as ‘Poli’ instead of ‘Polo’.
To achieve the scenario, we need to build
SOURCE_TABLE > TABLE COMPARISON > MAP_OPERATION > TARGET_TABLE
(1) Create the Job and pull dataflow to name it appropriately.
(2) Pull the table customer_ref from source data store and select “Make Source”.
(3) Pull the simple Query from ‘Palette’ into the DataFlow, Drag all the source fields into Target Query.
(4) Drag and drop the Table_comparison from Local Object Library >> Transform >> Data_Integrator.
a. Select the table name from Target Data Store- Cust_dim_scd2 into which the corrected entries needs to be updated.
b. Select the Generated key column as ‘Customer_id’ based on which the entry need to be updated.
c. Select customer_number from Schema In and drop it in “Input Primary key columns”.
d. Select last_name, address, city and country from Schema In and drop them in “Compare columns”.
Basically you letting the Job to “Compare the changes (in last_name, address, city and country) from source to target entries and record a change if an entry is found to be different.
Then letting the Job to pass the ‘change’ to next step for that Customer_number as input primary key column.
(5) Now it is the turn of dragging the Map_operation transformation from Local Object Library >> Transform >> Platform.
Here we can transform update to insert based on what the requirement is. However we will just use update to update.
(6) In the last step, just drag the target table into which the changes need to be updated.
Execute the Job:
Save all objects and execute to notice as in below.
You will notice an interesting fact, though you see two records for Customer_number = ‘C333’ only one entry is updated for last_name as “Polo” after comparing from source.
I understand it as “The latest record is updated”.
I would like to leave it to experts to comment after reading this Document.