Skip to Content

Although there are many documents present in SCN and other sites explaining the importance of 0RECORDMODE infoobject but here I am taking it to different level of understanding by considering a complete Scenario with respect to data flow as well as Query:

1) Importance of 0RECORDMODE field with respect to DSO.
2) How the data flows from DSO to Cube using Change Log Table.
3) How to handle the Deleted records using 0RECORDMODE infoobject when the data is coming from third Party or Generic Data Source created in SAP ECC or in other SAP systems.
4) How to handle these deleted records at Query Level.
5) Comparison with the Standard Data Source.

1)   Importance of 0RECORDMODE field with respect to DSO:

Here I am loading data from a flat file to DSO to understand what happens when the data is loaded with modified records.

a)    Data loaded from Generic Data Source to DSO: Generic Data Source is created within BW file Source System and the data is loaded to DSO to store the file data in BW. Customer Sales data is loaded.

b)  File data is as follows:

Fig 1 Excel Data.PNG

Fig 1 Excel Data

Data shows that different customers had placed different orders and an order can have multiple line items.

Here Deletion Indicator field is considered which will have only two values either X or D.

                  X –> Indicating Data which is not considered for Deletion.

                  D –> Indicating Data which is deleted.

         c)  Now the same data is loaded in DSO ZCUSTORD: Here we can see that data first goes to New Table /BIC/AZCUSTORD40.

      

               Screen2.PNG

                  Fig 2 New Data Table of DSO.

            

          d) Once we activate the data goes from New Table to Active and Change log Table as shown below:

              

               Fig3.PNG

                Fig 3 Active Table of DSO

  

              Fig4.PNG

               Fig 4 Change log Table of DSO

           e) Now data is loaded from DSO (ZCUSTORD) to Cube (ZSAL_DAT):

                Fig5.PNG

                     Fig 5 Cube Data

           

           f) For the Next Load file data is modified as shown below:

                      Fig6.PNG

                      Fig 6 Modified Data in Excel

                Note: These values were earlier present as 50 and 25 which have been modified as 90 and 60.

             

            

          g) Once we activate the above data in DSO the records will be overwritten as we have Customer, Sales Order and Order Item as key fields, so for same set of key it will overwrite the existing values.

                 

                    Fig7.PNG

                    Fig 7 Active Data of DSO after modified records

               

          h) Now we will see the Change Log Table data which is our main focus area.

         

                     Fig8.PNG

                     Fig 8 Change log Data after Modified Records

                 0RecordMode can have 7 different values as shown below:

                

                   Fig9.PNG

                   Fig 9 RecordMode different values

                Here our focus is on 5 types of values which we get most of the times.

             

              N –> Stands for New Value i.e. any record which is first time loaded in DSO.

                    X –> Stands for Before Image of the record (for same key what was the earlier value). Before Image will always result in negative value of the earlier record.

               ‘ ‘ –> Stands for After Image of a record (for same key the new modified value).

                D –> Stands for Deleted Records (if the Recordmode value is D it will delete the record for the same key)

                R –> Stands for Reverse Image (if the RecordMode value is R it will delete the record for the same key).

    

                Here we can see that corresponding to Modified records C1 S1 10 there are two entries in Change log table one with 0RecordMode X and other with 0RecordMode ‘ ‘ (space). This is how Delta records are handled within SAP BW.

           

            As explained above RecordMode with X will give us Before Image with negative value of the key figure for the records and RecordMode with ‘ ‘ will give After Image of the record.

       

               Hence we got four records for the two Existing Records:

                fig10.PNG

                 Existing Records (before changes)
                 

                   fig10.PNG

                 Fig 10 Change log modified records

     

             

               2) How the data flows from DSO to Cube using Change Log Table

                  a) Data goes from DSO to Cube: Since it’s a second delta load DTP will pick the data from Change log table.

            

                     Fig11.PNG

                    Fig 11 Cube data loaded from DSO

                 

              

Here we can see that corresponding to four entries in Change log table we get only two entries in cube i.e.

Customer

Sales Order

Item

Quantity

Existing Record

C1

S1

10

50

New Record

C1

S1

10

40

Existing Record

C3

S3

10

25

New Record

C3

S3

10

35

      

               Fig 12 Cube Data of modified records only

   

                  Hence when the data goes from Change log table of DSO to Cube it gets the difference of the after image and before image records.

                 

                

Active Table Data:

Customer

Sales Order

Item

Quantity

C1

S1

10

90

C3

S3

10

60

              Fig 13 DSO entries showing modified records

              

Corresponding Cube Entries:

Customer

Sales Order

Item

Quantity

C1

S1

10

50

C1

S1

10

40

C3

S3

10

25

C3

S3

10

35

              Fig 14 Cube entries showing modified records

                                  

         This is how 0RECORDMODE plays important role when the data is loaded in DSO and from DSO to Cube.

1)            3)  How to handle the Deleted records using 0RECORDMODE infoobject when the data is coming from third Party System or Generic Data Source created in SAP ECC or in other SAP systems:

Ta              a) Taking the case of Generic Data Source where a particular records get deleted, in our case we have Deletion Indicator field   which will hold values as ‘X’ or ‘D’.

                

                 X –> Stands for existing record

                 D –> Stands for deleted record

b_           b) Now we will set the deletion indicator for record as D marking it as deleted record.

fig13.PNG

                 

 

     Fig 15 Excel Entries for Deleted Data

c) Since we already have a record for the above deleted record in DSO. We need to handle this deleted record so that the record will not come in DSO and in Cube.

d) For handling this deleted record at DSO level we will write a small piece of code in End Routine.

fig15.PNG

Fig 16 End Routine Code for setting RecordMode to D

NOTE: Here we have mapped Deletion Indicator field of source to 0FLAG infoobject.

e) After data loading New Data Table of DSO will show records as shown below:

   fig17.PNG

   Fig 17 New Data Table after data load

f) Once we activate the data Active Data table will not have the record for which record mode is D in our case Customer C3 S3 10 is deleted from DSO.

 

fig 18.PNG

         Fig 18 Active Data Table: Record is deleted for which RecordMode is D.
    

       g) Change log Table Entries: RecordMode for record C3 S3 10 is marked as R instead of D.

           fig19.PNG

          Fig 19 Change log table entries RecordMode for deleted record is R.

       Here we can see that for C3 S3 10 data record we get the negative value of key figure quantity.

       h) Cube entries:

             fig19.PNG

            Fig 20 C3 S3 10 new entry with -60 quantity.

           Hence at cube level we will get C3 S3 10 0. Quantity will be zero (-60 + 60 records get cancel out).

  

1)            4) How to handle these deleted records at Query Level.

fd

                     a) Sometimes it becomes an issue that a record is deleted but still it comes as 0 in Report output.

             fig20.PNG

             Fig 21 Report output showing C3 S3 10 with 0 quantity

 

         b) We can change the default setting of Query in Query Designer as shown below:

           fig21.PNG

              Fig 22 Query Global Setting Suppress Zeroes for All Active Values.

         NOTE: This setting will not show any record for which all the key figures have zero values.

        c) We can also compress the Cube with zero elimination to delete all the records which have 0 value for all the key figures.

               fig 22.PNG

                Fig 23 Fact table of Cube before compression.

            We can see here that for same records there are multiple entries in the cube which also slows down the query output

                

                 fig 23.PNG

                 Fig 24 Compressing Cube with Zero Elimination

                        fig 25.PNG

                       Fig 25 E Table of Cube

    

              Once we compress the cube data moves from F table to E table since we have compressed the cube with with zero elimination Dimension Table 1 doesn’t have the DIM ID 7 which is having 0 as key figure value.
 

                     fig 25.PNG

                    Fig 26 Query Output after Cube Compression

               

              Hence the record with Deletion Indicator as D is not present in Cube nor in DSO.

             

5)            5) Comparison with the Standard Data Source.

             Hitherto what I have shown is with respect to Generic Data Source only.We can now compare how it works in standard data source.

             In any Logistic Data Sources (say 2lis_11_vaitm) how it works (although we cannot generalize it for all the data sources):

     

            a) There is one field ROCANCEL (Extractor builds the logic to populate this field) which usually contains values as ‘ ‘, X, R depending upon the Data Source behavior. This field is mapped to 0RECORDMODE.

             b) Data source supports different types of delta. You can check it in table ROOSOURCE (also RODELTAM) table for the type of delta supported by the Data Sources.

             Taking the case when the Data source is of type ABR (most commonly used LO Data Sources are of type ABR):

        

          Now based on the type of document ROCANCEL can have different values:

          

           Instance for NON return-type documents (standard):

               

           ‘ ‘ = After image with plus sign

‘X’ = Before image with minus sign

‘R’ = Remove (deletion record) with minus sign

The following sign logic applies for returns:

‘ ‘ = After image with minus sign

‘X’ = Before image with plus sign

‘R’ = Remove (deletion record) with plus sign

          To display the plus/minus logic for returns, more instances of the ROCANCEL field are used in the OLTP:

         

‘U’ -> becomes an after image with a minus sign

‘V’ -> becomes a remove (deletion record) with a plus sign

‘W’ -> becomes a before image with a plus sign

These three instances of the ROCANCEL field are only required for the internal conversion of key figures. You can display these instances in RSA7. The conversion occurs during the delta upload into BW.

Therefore these values should never appear in BW.

In General we have to be very careful when we are enhancing the Standard Data Sources with the key figures. There are many SAP notes available for enhancing the Standard Data Sources. Please go through the notes before enhancing the Data Sources.

For further details on Delta Method you can refer:

1)      MC11V_0ITM: Delta Update Without Activation of ABR Delta Method

2)    757487 – Plus/minus logic: Returns and mixed business transactions

       


                  

       

        

To report this post you need to login first.

13 Comments

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

  1. hemant patidar

    Extremely useful document, detailed explanation  of 0recordmode significance and multiple uses.

    Thanks for posting… waiting for more such kind of information from you .

    (0) 
    1. Mayank Jaiswal Post author

      Thanks Marcio,

      We do not have ROCANCEL field in generic extractor it’s a feature of Standard Extractor, where the logic is kept to populate the ROCANCEL field depending how the document undergoes change… accordingly ROCANCEL field is populated.

      Regards,

      Mayank

      (0) 

Leave a Reply