Solved: UPMOD /0RECORDMODE - Datasource to DSO

Hello Experts,

I need information regarding the upmod field and 0recordmode infoobject .

1)Currently in the datasource(generic) - no field is added in extract structure (upmod) - Datasource is of AIE delta type

2)0recordmode in the transformation ( technical rule group) by default its set to constant.

Doubt :

when I run delta upload - below record will be uploaded.

DOC_NUM - 100

DOC_ITEM - ABC

I will delete this record in database.

when i run the delta load again- will it update with DSO with 0recordmode = 'D'?

If no, then how can i see the 0recordmode = D in changelog table.

2)Is there any mapping required for 0recordmode in TRFN in BI 7.0?

P.S : I have read the other threads related to 0recordmode, but my doubts didnt get clear.

Please provide your inputs.

Regards,

Ashwin.

SAP Managed Tags:
BW (SAP Business Warehouse)

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Answers (1)

Hi Ashwin,

1st doubt:

Generic datasource - It depends how you have made your Datasource. Read the document at the end of this post.

http://scn.sap.com/thread/1871072

2nd doubt: Mapping is required in your case I think. But only if you are getting the value of UPMOD field from your generic extractor for deleted records.

Please read the below post with patience and you will get your answers.

http://scn.sap.com/thread/627979

Regards,

Sujit.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello Sujit,

Thanks for the reply .

From the links you provided :

1) UPMOD field has to be added in the datasource

2) Still not clear about it....do we need to map UDMOD from source to 0RECORDMODE(Target DSO) in the transformation under technical rule group.

And most of the links I have read and those provide the information in BW 3.0 ( mapping 0STORNO in transfer rules)

But now I am using BI 7.0 and in our datasource there is no field like ROCANCEL /0STORNO.

Hi Ashwin,

Did you read the links that I provided. They don't talk about ROCANCEL /0STORNO. They only talk about 0Recordmode. The 2nd link, if you read until the end you will have a fair idea. Together these links really give you answers. But you might have to read a lot that is not necessary.

There is not much difference between 3.x and 7.x. However I will give you a summarized explanation here as it is not clearly mentioned elsewhere.

0RECORDMODE is filled either in new table (from source or our own routine) or gets calculated while activation.

If source sends a record with 0recordmode as "D",

then in your DSO new table you will get "D". In change log you will see a new row is created with negetive KFs and 0recordmode = "R". Active table will not have this record at all after Activation. Same will happen if "R" comes from source.

But if your source doesn't send any record at all then you need to find out which record has been deleted by using some ABAP program, either in source system or in BW. Once you find it use the same logic in your Data load and create a new record for the same Key fields in start routine. This new record only needs to have the key fields. Leave other fields blank. Just set 0Recordmode = "D" in your code for this new row. Append this new row to your source_package. All done and your records will be deleted. Then when you load data to your cube, change log will nullify the record in cube.

Make sure you don't mess with other records which are coming from source. Let system handle them. For them the 0recordmode will be blank in new table. As you have mentioned that your generic datasource doesn't have the field called UPDMOD. How will system act on these records is the question. System will check the key of each of these records in Active table. If not present then it will create new images "N" in change lon and " " blank in Active table.

If present but all the values of the fields are same then this record will not be noted in Change log and will not be updated to the cube.

If present but there is a change then change log will get a negative entry for this record "X" (before image). Change log will also get a " " entry (after image). Active table will get the modified record. Active table will have blank 0Recordmode.

Hope this is clear to you. If not then ask.

Regards,

Sujit.

Hello Sujit,

Thanks a lot for this helpful information.

1."But if your source doesn't send any record at all then you need to find out which record has been deleted by using some ABAP program, either in source system or in BW. Once you find it use the same logic in your Data load and create a new record for the same Key fields in start routine. This new record only needs to have the key fields. Leave other fields blank. Just set 0Recordmode = "D" in your code for this new row. Append this new row to your source_package. All done and your records will be deleted. Then when you load data to your cube, change log will nullify the record in cube "

--> Yes, we dont have any field in the source system to update the information (D or R)

-->i want to compare the entries of two DSO's

DSO1 and DSO2 in there transformation(Start or END)

DS01-->DS02

Check the entries of delta package of DS01 and if the entries(key field) are already available in DS02 ( loaded from some other TRFN) - mark that entry as D , set recordmode = D.

So, can i write this in Start routine or end routine? and what will be logic for this ?

My logic :

in Start routine

Select all entries of DS02 for all entries in Source_package

if the entry of DS02 -keyfield = source_package_keyfield

source_package-recordmode=D

then in result_package also that entry will be added as recordmode = D.

Will it work?

If not , please provide some logic.

Thanks,

Ashwin.

Hi Ashwin,

You got it right.

In start routine you need to modify this in most cases.

It will not matter much, In the end the aim is to put D version records in new table of DSO1. Use whatever routine you like.

But for complex business scenarios you have to select where is it good performance wise.

To me it is strange that a record which has been deleted in the source is coming as delta with new changes. And you have to manually delete it based on another DSO. Never mind there can be complex scenarios in BW.

Regards,

Sujit.

Hello Sujit,

No , we are not checking the record is deleted in database or not ..and according to me - it can be handle only by the datasource right ?(AIE delta type)

datasource-->DS0-->Ds01-->Ds02-->Cube

Ds011-->DS02

So DS02 gets data from two different source targets.

DS01--ds02 , i will check the source package , if the entry allready exists in ds02(loaded from ds11) then set the 0recordmode = D .

Hope the logic works

Thanks

Ashwin.

Hi Ashwin,

It can be handled by Standard Datasource, Generic Datasources by using Change Pointers and also in BW Routines by using some conditions (e.g. Shipment status). Total 3 ways to do this.

I understood your case. You don't want to bring delta for a record already present. Makes sense at times.

This logic will definitely work.

All the best.

Sujit.

Hello Sujay,

How are you ?

I have done with coding part ( END_ROUTINE). I have set 0RECORDMODE=D in the end_routine.

And the record is being deleted from the DSO.

But in changelog table the recordmode is set as "R" not as D .....how to handle this?

As I need to upload the data from DSO to another DSO1.

Is it the right behaviour?

Regards,

Ashwin.

Hi Ashwin,

Yes this is right behavior of the DSO. The "R" image will create appropriate entry for the next DSO. I.e. Reverse image. So record gets deleted from your first level DSO and gets nullified in upper level. I.e. DSO or cube (In your case 2nd DSO).

So when you run a report out of your 2nd DSO, you will not get this record in your report. Same happens with the cube as well.

Regards,

Sujit.

Hello Sujay,

OK....

Usecase:

Load the data --DS-->DSO-->DSO1--->DSO2.

Delete the entry from the customizing table ( Back end/source system).

E.g 2 records will be loaded at first time of loading

ABC X 100

XYZ Y 200

DSO and DSO1 and DSO2 ( contains these two entries)

Now i delete one entry in backend

so DS will extract

ABC X 100

Data load again

datasource-->DSO ( 1 entry deleted)

Changelog table

XYZ Y 200 R(recordmode)

now how about the next level of data load?

DSO--->DSO1-->DSO2? As i need to remove/nullify the entry from all the DSO's

Regards,

Ashwin.

Hi Ashwin,

My name is "Sujit" and not "Sujay".

Everything you mentioned above is correct except the below.

Changelog table

XYZ Y 200 R(recordmode)

It will be like this.

Changelog table

XYZ Y -200 R(recordmode)

Your DSO1 will have a record like

XYZ Y 200 in Active table

XYZ Y 200 N in change log

Now it will get one more record

XYZ Y -200 R

Then N+R becomes null. This will delete the entry from DSO1 active table.

When you load Delta from DSO1 to DSO2. Records will be loaded from change log table. R records when go to DSO2, the active table entry will be removed and R will be created in change log of DSO2.

Hope it is clear.

Regards,

Sujit.

Hello Sujit,

Sorry to change your name in the last two posts

Yes I got it .

But the problem/issue here is DSO2 ...which is WRITE OPTIMIZED DSO...

I tried loading from DSO1 --> DSO2

In the debug mode ( RESULT_PACKAGE consists of 1 record only) but changelog table consists of 2 records

XYZ Y 200 N

XYZ Y -200 R

And after the successful load- no change in data in DSO2.

Do i need to do any mapping in TRFN?

Any settings in DTP?

Thanks,

Ashwin.

Hi Ashwin,

Could you explain why DSO2 is WO. Ideally one would go for the lower level DSO as WO. But you must be having a reason for your design.

I would like to understand the whole flow. Is your reporting done on this WO DSO or there are more IPs involved ?

Regards,

Sujit.

Hello Sujit,

Our reporting is done on Infocube and MP's.

And there are no IP's involved.

DSO2 is WO and its lower level for the other DSO's and Infocube .

For eg DSO2(WO) -->DSO3(WO)--DS04(Standard) - this our data flow.

Is it not possible if the lower level DSO is WO?

Regards,

Ashwin.

Hi Ashwin,

By IP I meant InfoProvider not Integrated Planning.

It is very much possible if the lower level DSO is WO.

When you load from WO DSO to next dso, you find out that a record has been deleted in the source and you set it to "D". Then the rest of the flow should work as expected. Now the question is how will you find out if the record has been deleted. I hope you have a logic for it.

Please explain where do you face the challenge if WO DSO is at lowest level.

Regards,

Sujit.

Hello Sujit,

Ok, logic? again we have to write code between two DSO's

Standard DSO and WO DSO?

If yes, then I am not sure..what will be the code for it

DS01 ( Standard) and DS02 ( WO)

Logic has been written in endroutine of Datasource to DSO1 and its working fine as I told, the 0recordmode was set to "R" in changelog table.

Now my question is DS01 --> DS02(WO) if I run the DTP ( delta) - How the behavior will be?

the records with "R" 0recordmode will do the magic in WO DSO2 as well?

Regards,

Ashwin

Hi Ashwin,

You never gave your full dataflow in any of the past Posts. It is today that you have given a clear picture.

However I will explain how it works. In WO the keys are different, no change log. So system will not able able to decide delta. But you have multiple DSOs. So there must be some Key modifications in each layer I guess. Otherwise why would someone create a flow like that.

Setting Delta record mode in WO DSO doesn't make any sense. Especially where you have made it. What you should do is, move your code from DS-DSO to WO-DSO to Std DSO. This should be sufficient. No need to write code and delete records from WO DSO. WO DSOs should have all records possible.

Regards,

Sujit.

Hello Sujit,

I was not clear about the requirement as it was not finalised .....

I am confused here...."Move your code from DS-DSO to WO-DSO to Standard DSO"???

At present the code is written in the transformation between

datasource and 1st DSO ( Standard).

Now second level

DSO1 to DSO2 ( WO) - how to handle? or code to be written in the transformation between STD DSO and WO DSO.

Sorry, this is really confusing me

Regards,

Ashwin.

Hi Ashwin,

As your flow contains many dsos and of different type, I'd suggest a new and simple solution. Your worry is only to delete the deleted records from your reporting DSO.

So forget about your WO DSOs. Add your code to transformation of Standard DSO which is just beneath your cube. Let the deleted records be there in the WO DSOs. As no one is running a report on them.

Regards,

Sujit.

Hello Sujit,

Good morning

My data flow

Datasource-->SDSO-->WO DSO-->WO DSO-->SDSO-->SDSO-->Cube--> Query.

And datasource is not of AIE delta type, so manually have to track the deleted status.

But I need to delete entry from the lower level of DSO's as well...If i directly write the code in the TRFN of DSO which is beneath the cube...how about other DSO's?

Its bit complicated....and the datasource will retrieve data from some small table (less than 100 records).

Thanks for continuous inputs ....hope we will find a solution for it.

Regards,

Ashwin

Hi Ashwin,

If the data is of very small amount then why bother about delta. You can go with full loads regularly.

I asked to leave other dsos as there will be no reporting done on them. But that's not a good idea as there must be reason behind such design.

All the best...

Regards,

Sujit

Hello Sujit,

So now the plan is to write the code in TRFN of the last DSO?

But this is bit complicated ...and what logic to be used in the routine of that TRFN?

Hi Ashwin,

Why are you bothering about delta for such a small amount of data. Do full loads only. If required delete the content of your data targets and do full loads.

Hello Sujit,

Since in our dataflow- there are 6 transformations and still we are thinking what approach to be taken for this and we have two WO DSO's in the flow.

will get back to this thread -in case any issues/inputs.

Thanks

Ashwin

Hello Sujit,

Good Morning - and I am back

This time with WO DSO .....

Standard DSO ---TRFN---- WO DSO

End Routine :

Select data from WO DSO into IT_WO

Loop at IT_WO

Read the result_package based on the conditions

If the record is deleted from RESULT_PACKAGE and if the record exists in IT_WO

Set the 0record_mode = D

Append the entries to Result_package

Since the Target is WO DSO ( Active table) - I am not able to append the entries with 0recordmode = D ( Getting error as Duplicate data records - when i run the DTP).

I know WO DSO has only one table ...but how do we handle this scenario? Is ther any solution to delete the already loaded entry/record from WO DSO by comparing with Source DSO....

Regards,

Ashwin,

Hi Ashwin,

You mean to say if the record is not present in Result_package, then it should be deleted from WO DSO.

Then in your code you need to set new values for the new record to be appended.

REQUEST

DATAPAKID

RECORD

These three values have to be different from what you have already in the WO DSO.

Regards,

Sujit.

Hello Sujit,

Yes - it should be deleted from WO DSO .

Suppose I dont have any new record/entry to be loaded- only I need to delete the entry which is deleted from result_package/Source DSO.

Is it possible?

Hi Ashwin,

It is possible to delete an entry from any database table.

Just write a delete statement.

Have a look at the below link for syntax help.

http://help.sap.com/saphelp_46c/helpdata/en/fc/eb3aef358411d1829f0000e829fbfe/content.htm

Regards,

Sujit.

Hello Sujit,

How are you ?

I am back with some more questions for you

--we changed our data flow ( replaced the WO DSO with STD DSO's)

Data flow:

Datasource-->DSO1 ( STD)-->DSO2 (STD)--> DSO3(STD)

I delete an entry from backend and I load the data – the data is getting deleted from DS01 and DS02 ( Change log table- 0recordmode is “R”)

1. After DS01-->DS02 run …DS02 entries will be 0 in active table , but changelog table will have entries with 0recordmode – N, R

2. But when I run the DTP from DSO2--> DSO3 ( there is no change – 0 records added/deleted).

Why the data is not getting from DS03?

I have same key fields in DS02 and DS03 ....

Can you please suggest on this.

Thanks

Ashwin.

Hello Sujit,

How are you ? I am back again...almost fixed the solution of changelog and recordmode.

I have a question here ...

Data flow

DS01-->DS02-->DS03-->Cube

First run :

DS01-->DS02-->DS03-->Cube --> Report ( 10 records)

Before the second run- I have deleted a record/reference in the backend.

Second run:

DS01-->DS02-->DS03 = 9 Records ( the deleted record is marked as R in the changelog table)

But when I run the DTP from DS03-->Cube - there is no change ...!!!

Record Key1 Data1 Price1

Key 2 data 2 price 2

Key2 data2 price is set as R - but its still there in Infocube?

Why? can you please suggest here?

Regards,

Ashwin.

Hi Sujit,

I read the thread thanks for your time spent in giving good brief explanation

I've a query in my mind.

Is 0RECORDMODE is mandatory for all DSO's where ever we've UPMOD in datasource?

I've seen few DSO's in our landscape where UPMOD is mapped to 0RECORDMODE till infosource and from infosource to DSO, it's not mapped. But, if we open technical group in transformation's rule group, we could find it there. Any specific reason for such scenarios?

Also, If I try to find 0RECORDMODE from RSD1 transaction, it's not getting displayed.

And one more thing which I'm trying to understand is, if I click on where-used list of 0RECORDMODE, it's showing that it's been used in all the DSO's even though I've not added to the DSO. But it automatically gets added to technical group(in rule group) of transformation with constant assignment. Any specific reason for this?

Kindly help me to understand.

Regards,

Antony Jerald.

Ask a Question

Top Q&A Solution Author

User

Count

UPMOD /0RECORDMODE - Datasource to DSO

Accepted Solutions (1)

Accepted Solutions (1)

Answers (1)

Answers (1)

Re: HTTP Receiver adapter with Dynamic value in UR...

How to create a rolling forecast in SAC? I have an...

In SAP Hana Cloud, do packages exist as database o...

Re: Mass Update a Zfield in Std Table with Split &...

Re: Change BW Query Initial Variable Input at runt...