Recordmode Importance in SAP BI along with Delta Handling

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

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.

      

              

                  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:

              

              

                Fig 3 Active Table of DSO

  

             

               Fig 4 Change log Table of DSO

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

               

                     Fig 5 Cube Data

           

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

                     

                      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.

                 

                   

                    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.

         

                    

                     Fig 8 Change log Data after Modified Records

                 0RecordMode can have 7 different values as shown below:

                

                  

                   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:

               

                 Existing Records (before changes)
                 

                  

                 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.

            

                    

                    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.

                 

 

     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.

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:

  

   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 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.

          

          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:

            

            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.

            

             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:

          

              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 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 24 Compressing Cube with Zero Elimination

                       

                       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 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