Map_Operation Transform
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.
- In the first flow, i.e., MO_Normal-> i have selected Normal as Normal and discarded rest all opcodes.
- 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.
- 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).
- 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.
- Check the data, you can see the updated rows flagged as ‘U’
- 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.
- and you can see the data after the map_operation dataset along with delete opcode ‘D’.
- In the target data set, the above records will be deleted. Check the target 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.
- 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.
- Check the data
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
Just select whatever columns you need to update in target and ignore/leave the remaining columns as is.
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
Hi Sudheer,
Can I explain more elaborately regading NORMAL and INSERT opcodes.
Gangadhar
Hi Sudheer,
Can you explain elaborately about NORMAL and INSERT opcodes ?Whats the main difference ?
Gangadhar
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
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
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