How to flag the input and output rows in sap bods?

[former_member188628]

Hi All

       I was referencing the technical manuals, where i came across the concept of flagging the opcodes of the input data-sets. Also the transforms like the Table comparison and Map could produce or accept the flagged datasets. However, I am not getting the desired results:-

My current scenario:-

Desired Output:-

I don't understand how can i make those flags appear both the input and output fields Please help me out with this.

[former_member208402 · Accepted solution]

Hi Moumita,

All the records in source will be "Normal". hence you don't see any opcode in input.

only after table comparison can generate opcodes in DS (i.e; I-insert,U-update,D-delete).

If you take Map_operation without table comparison then all the records enter Map_operation as Normal and you change opcodes in Map_operation for Only Normal like

Normal--Normal

Normal--insert

Normal--update

Normal--delete

Thanks,

Ravi kiran.

[former_member188628 · Accepted solution]

Hi Ravi

         Thanks a lot for the response. I have added a table comparison transform onto my job. However it have not changed any output format

Regards

Moumita

[former_member188628 · Accepted solution]

Hi

  Got another question:-

    The output is not being shown as the settings in the map operations:-

Accordingly, if i make any changes in the database, then only the changed or inserted or deleted records should be inserted and the unchanged records should not be loaded.However, in my case all the records are being loaded

Please do correct me if I am wrong or missing something

Thanks

Regards

[former_member208402 · Accepted solution]

Hi Moumita,

Can you please post your mapping screenshot and screenshot of table comparison inside.

[former_member188628 · Accepted solution]

Hi

Following are the screenshots of the transforms:-

Table Comparison:-

Map Operation:-

[former_member234353 · Accepted solution]

Hi Moumita,

The opcodes that are being mentioned are the internal flags of the tool and they are not visible. Usually all the rows are normal when they are pulled form the source. Normal is the general state, but when passed through Table Comparison or History Preservation transforms, they get changed to either Insert, Update or Delete(if available).

You can certainly manipulate them using the map operation in BODS.

If you are explicitly looking for the opcodes, you wont be able to find in any output.

Hope this helps and please elaborate if you are looking for anything more specific.

Thanks,

AJ

[former_member234353 · Accepted solution]

Hi Moumita,

To load only the changed rows, you will need to use first the table comparison so that only the changed rows goes forward.

In this case you wont need to use a map_operation transform as the opcodes will be changed accordingly for Inserts,Updates and Deletes by the table comparison operation and the normal rows(here specifically, those rows that are unchanged or unaffected) will not go forward and hence won't get uploaded.

Hope it helps resolve the issue.

Thanks,

AJ

[former_member188628 · Accepted solution]

Hi Anirban

        Thanks a lot for the responses. The problem i am facing is :-I want to make  the "Normal Records" invisible in my target table, and hence marked as "Discard",however after running the job everything is getting loaded I want to understand where am i going wrong

Thanks

Regards

[former_member234353 · Accepted solution]

Hi Moumita,

You should need to use the following approach to build your job.

Inside the dataflow,

Source --- > Table comparison --- > Target.

If you are generating a surrogate key then use the generated key,

else use the PK as the Primary and rest of the columns in Compare Columns

You will find only the changed rows inserted in the target.

Please try this out and let me know

Thanks,

AJ

[former_member208402 · Accepted solution]

Hi Maumita,

I don't see GID column in MAP_TARGET and u r using GID in input primary key columns?

and also remove generated key column GID.

Also dont use Map_operation as table comparison itself does insert/update.

But if you want send deleted records as insert.. in this case use map operation.

[former_member188628 · Accepted solution]

Hi Ravi

          Now i have made the GId column appear on the target table.I gotta use that.

Regards

[former_member198401 · Accepted solution]

Moumita,

Its not that simple as we think. I checked in Debug mode but still the Opcodes were not seen.

Any normal records coming from the input will be treated as normal in output if Map operation  has normal in output schema. Any new records coming from the input will be inserted if insert selected in the Map operation output.

I then tried with the below approach. We can change the opcodes using Map operation transform. If we want only updates then we will use only Update>Update. Similarly for other DML operations Insert.

This all depends on your requirement.

Below logic shows how you can bring the opcode in your target table. I have create 4 different query

transforms which get input from a map operation. Each operation will have its own opcode.

So to summarize

Query_Normal query transform will only receive Normal rows(Set opcode as Normal>Normal)

Query_Update query transform will only receive Updated rows(Set opcode as Update>Update))

Query_Insert query transform will only receive Inserted rows(Set opcode as Insert>Insert)

I then combine the output of all using our Merge transform. The final result is dumped in to a target

table

Final Result

I dont have rights to modify the source table so could show you only Normal opcodes. You can try updating the Source table by modifying a row or by inserting a new record.

You also need to set the Auto Correct Load to Yes in the target table option.

NB: If you want to discard all the normal records from the source and only need to insert new records in target then just remove the Map_Operation_Normal and Query_Normal.

Let me know if this works.

Please correct me if I got anything wrong

Regards

Arun Sasi

[former_member188628 · Accepted solution]

Hi Arun

   Thanks a lot for such an indepth analysis and explaination. Gotta a doubt:Is Opcode a field here which we should keep manually for the db table?

As mentioned in the points under NB:-   to delete the MAP_NORMAL and Query_Normal to be remove for the desired operation, but how do I achieve that my simple job, or should i modify the job as described by you ??

Regards

Thanks

[former_member198401 · Accepted solution]

Opcode is just a column in the target table to understand the working of DML operations using Map operation

You can achieve achieve that using a single map operation

Insert>Insert

Update>Update

this will allow only inserts and updates

Regards

Arun Sasi

[former_member188628 · Accepted solution]

Hi All

     I am still wondering , but its showing up the expected data:-

I have given for "Normal--> Delete" ,and Delete data from table before loading:_unchecked.

Auto Load- No ,coz there are chances of records repetition, since same customers might be purchasing on different days different items.

But still, all the data is getting loaded..Normal data also, only delete->insert and Insert->insert, Update-> is working fine

[former_member234353 · Accepted solution]

Hi Moumita,

You can implement this logic using the table comparison itself.

You won't require the map_operation for the scenario that you have mentioned unless you are working on anything specific.

SOURCE -----> TABLE_COMPARISON ----> KEY_GENERATION -----> TARGET

Please try this out and let me know if you face any specific issue.

Thanks,

AJ

[former_member188628 · Accepted solution]

Hi Anirban

     The main requirement was checking for the opcodes using MAP operation and one more problem was using NORMAL-->Delete or Normal-->Discard is not working properly even(or i am going somewhere wrong).

Thanks

Regards

[former_member188628 · Accepted solution]

Hi All

     Finally feeling little relieved, got the Opcodes traced in Debug mode, opcodes are visible in case of Table transform and Map Operation:-

Thanks a lot to All

Regards

[Former Member]

Hello Moumita,

Please, check this, it will help you to understand how to manage changes in your data.

Delta Load Implementation - Enterprise Information Management - SCN Wiki

[former_member188628]

Hi Nestor

     Thanks for sharing the information links.

Regards

Moumita