What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Map_Operation Transform as PDF --


Note on Map_Operation: It allows you to change the opcodes on your data. Before discussing this we should know about opcodes precisely

In DI we have 5 opcodes, Normal, Insert, Update, Delete and Discard(you’ll see this option in Map_Operaion only)

Normal:  Indeed, it creates a new row in the target. The data which is coming from the source, is usually flagged as normal opcode

Insert:  It does the same thing, it creates a new row in the target and the rows will be flagged as ‘I’ – Insert

Update:  If the rows are flagged as ‘U’ , it overwrites an existing row in the target.

Delete:  If the rows are flagged as ‘D’, those rows will be deleted from the target

Discard:  If you select this option, those rows will not be loaded into the target.

Understanding Opcodes: Here is an example, in the below figure I’m using (normal to normal, normal to insert, normal to update, normal to delete) opcodes. Here i have taken normal opcode mainly because, query transform always takes normal rows as input and produces normal rows as output. However, will share the remaining opcodes in Table comparison and History Preserving transforms.

map_op_opcodes

  • In the first flow, i.e., MO_Normal-> i have selected Normal as Normal and discarded rest all opcodes.
mo_normal
  • This flow inserts all records in to the target which are coming from the source
  • In the second flow, MO_Insert-> i have selected normal as insert and discarded rest all opcodes.
Mo_Insert
  • It does the same thing, inserts all records in to target.
  • Have a glance on both the data sets before loading in to the target. You will see no opcode for Normal as Normal rows(1st flow), but you can see Insert opcode indicated as ‘I’ for Normal as Insert (2nd flow).
normal_insert
  • In the third flow, i want to update few records in the target .
    • Let’s say i want to update all the records whose deptno = 20.
    • Now, I have selected normal as update in the map_operation and discarded rest all.
mo_update

  • Check the data, you can see the updated rows flagged as ‘U’
normal_update
  • In the fourth flow, I want to delete some records from the target.
    • Let’s say i want to delete rows whose deptno = 30, in the map_operation transform i have selected normal as delete and discarded rest all
    • In the qry transform i have filtered out few records, where i want to delete those from the target.
mo_delete
  • and you can see the data after the map_operation dataset along with delete opcode ‘D’.
normal_delete

  • In the target data set, the above records will be deleted. Check the target data
deleted_data
  • Now here in the sub-flow, i have inserted these deleted records in to another table. For this i have added one more Map_Operation and selected row type delete as insert.

mo_del_ins

del_ins

  • Now in the last and final flow, i have discarded all the opcodes. I do not want to load the data in to the target.
mo_discad
  • Check the data
normal_discard
Will post more information on opcodes
What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

24 comments on “Map_Operation Transform

  1. Hi
    Thanks for the link. But how can i update only some of the columns and the rest of the columns can have the same values as before?
    When I am doing this, the columns which do not have a new value gets updated as null
    Thanks

  2. Can you please explain what is difference between Normal and Inset opcodes in map opertaion. since both opcodes will insert a row, then what is the difference.

    • I used query trasnform to illustrate the example, I think I mentioned one point that query transform always takes normal rows as input and produces normal row as output, where table comparision/history preservation transform produces insert/update/delete opcodes.

      Hope this helps! Let me know if you need more clarity on this.

      Cheers,
      Sudheer

  3. Very clear explaination ,liked the part where you talk about capturing the delete records into another table.
    Also never noticed that op-codes are actually displayed in the datasets whether the opcode is Insert ,update or delete.And of course the pictures are very clear.
    Need something on NRDM (the use of NRDM and also which scenarios are handled by NRDM).
    Thanks.
    Dev

  4. Hi sudheer,
    Thanks for the great information and the data you provided is awesome. I have a doubt in delete , what i need to make in the query transform here actually and i was unable to see the operation codes in the target table

    • Appreciate your comments. You wont find any opcodes explicitly in target table options/elsewhere. All you have to do is, have a Map_Operation transform before your target table and instruct it to what opcode you want to send to target. You can select Delete/Update/Discard, (by default output rows from query are normal and will be inserted automatically into target).

      IF you want to delete, Src–> Qry–> Map_Op(Set normal opcode as delete)–>Tgt

      Let me know if you need more help on this.

      Cheers,
      Sudheer Sharma

  5. Hi Sudheer,
    Thanks for providing such a great information,I am new to BODS tool.What are the pre-requisites to learn dis tool.Can u explain briefly about functions. Also ,pls send any material/documents to my mail.

    thanks in adv.

    • Thanks for your kind words Sam (I aprreciate that), the pre-requisite to learn the tool -> you should know the basic SQL and PL/SQL (or T-SQL) and DW concepts. You can go thru my blog content to get an idea of DW concepts, Database concepts and SAP BODS information. I have organized them in a understandable way for beginners.

      Hope this helps!

      Do let me know if you require more information.

      Cheers,
      Sudheer Sharma

    • Hi Thangavel,

      Its actually one and the same. Normal and Insert opcodes does inserts, Table_comparision, History_Preserve transformations sends insert opcode where as Query transform sends normal opcode.

      Hope this helps!

      Cheers,
      Sudheer Sharma

  6. hi,
    thanks for the scenarios as show in the above scenario ur always making normal as insert , update ,normal

    my question is cant we make opcodes like update as insert , insert as delete.
    thanks in adv
    regards;
    Sravan

    • Yes you can. My example shows how to play with map_operation transform. In the demo I use query transform, it always takes and gives only normal opcode.

      Do let me know if you’re not clear.

      Cheers,
      Sudheer Sharma

  7. Hi Raja,

    Discard will never load data from source to target.
    Delete will delete the records from target which was loaded from source earlier

  8. Hi,
    I just have a basic question, how to view the data between the transformation. I am not able to get the icon which is visible between the two transformations.
    Also, in the table, how to view the column having values as I, U or D assigned for the rows.

    Thanks,
    Krishna

    • Dear Krishna,

      Firstly many thanks for visiting my blog.

      point one – on how to view the data between the transformation: you need to run the job in debug mode, then you should be able to see that
      point two – in the table, how to view the column having values as I, U or D assigned for the rows: These are the op-code values, which you would not find on target table. Those can visible in debugger mode itself if you view the map_operation transformation.

      Hope this helps, do let me know if you need more clarity

      Cheers,
      Sudheer Sharma

  9. Hi,
    I have a question based on the scenario I am working on. My target table is a hash table and and an identity column. I have to update a name column based on the ID which is an identity column. When I am using the flow as Source->query->TC->Map operation->target, the job fails saying can’t update the hash column. So in that case i tried using a query transform just after TC and then a map operation for NORMAL- Update. Can you please let me know if it will work fine?

    Thanks,
    Gaurav Sinha

Leave a Reply

Your email address will not be published. Required fields are marked *


*