Skip to Content

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.

Scenario:

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.

Planning:

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’.

Planning.png

To achieve the scenario, we need to build

SOURCE_TABLE > TABLE COMPARISON > MAP_OPERATION > TARGET_TABLE

/wp-content/uploads/2013/04/scenario_201855.png

Develop:

(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.

Query1.png

(4)   Drag and drop the Table_comparison  from Local Object Library >> Transform >> Data_Integrator.

Table_comparision.png

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.

/wp-content/uploads/2013/04/map_operation_201858.png

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.

execute job.png

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.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Pierre-Emmanuel Larrouturou

    Dear Suraj,

    I’m not sure I understand your example well. I’ve got questions about two points:

    – You’re using the Map_Operation transform in your data flow, but you leave it “as is” (row types are mapped 1:1). What is the interest of it then? For me it looks like a transform which does nothing.

    – I also don’t understand how the columns start_date / end_date / current_flag could be updated without the use of a History_Preserving transform.

    I’m a bit confused about these two points, so I would greatly appreciate your feedback!

    (0) 
    1. Suraj Pabbathi Post author

      Hi Pierre,

      Good question.

      We need to understand this based on two transformations, Table_Comparision and Map_operation.

      Table_Comparision — Watch the screenshot, based on primary column, it uses the columns specified in Compare columns. That is based on customer_number, it uses last_name, address, city and country to return whether row is existing or not and returns the appropriate input to the next transformation – Map_operation. Input may be insert, update etc..

      Map_operation — As per the input you may want to change the behaviour, may be insert to update or update to insert etc.. It normally maps the operations like insert, update etc… In this case, when row is existing in the target database for the specified primary key column, it simply updates. If Map_Operation is not available, it would have inserted a row, but we only want to update.

      Hope my explanation helped you understand the transformation.

      Thanks,
      Suraj Pabbathi

      (0) 
    2. Rv Awl

      Hi Pierre,

      I recently read the post and not sure if this would still interest you.

      I guess, for the stated example, result set could be achieved without using Map-Operation. To provide more flexibility of data set, map operation can be used..

      Regards,

      Rv

      (0) 
  2. Joel Gilbert

    The Generated Key Column in the Table Comparison transform is utilized to determine which row from comparison table should be compared in case of multiple occurrences of the same value for the natural key. Comparison is performed on the row with the highest value of generated (surrogate) key – presumably the most recent.

    This explains why the Last Name only changed on one of the C333 rows.

    (0) 
      1. Frank And

        Suraj,

        I also don’t understand how the columns start_date / end_date / current_flag could be updated ?  Should  the cust_dim_scd2 table need to have PK on the customer_id?

        (0) 
  3. leo dec

    Hi Suraj

    i have same issues with the above mentioned example.. could you please explain how current_flag, date_from and date_to changed without using HP transform…

    Regards

    (0) 
  4. Ravi Kashyap

    Hi

    Its related to SCD2 concept where we  are saving the history data by indicating the flag.

    So in the above scenario without history preservation where we are updating the flag ???

    (0) 
  5. Santhosh Sreshta

    Hi,

    In the History Preserving Transformation only, we are able to get the FLAG,Start and End Date details to be updated recursively right.??
    then, how are you able to get updated the FLAG,Start and End Date columns..? 😯 ?

    (0) 

Leave a Reply