Design Better Custom Extractors for ODS Delta Loads

by Shreekant Shiralkar and Bharat Patel, BW Systems Managers, Bharat Petroleum, India Data

Extraction,R/3,Extractors

A better understanding of the role the "ROCANCEL" field plays in R/3 and the corresponding "0RECORDMODE" InfoObject in BW allows you to design better delta load solutions. The authors introduce "ROCANCEL" and "0RECORDMODE" and describe a custom extractor they created for processing these values.

When performing delta loads with an ODS, the ROCANCEL field in R/3 plays a vital role for record processing along with its BW counterpart, InfoObject 0RECORDMODE. Extractors delivered as part of the SAP R/3 Plug-In package convey the appropriate values to ROCANCEL, and these values are assigned to InfoObject 0RECORDMODE in the transfer rules for the data source. The ROCANCEL and 0RECORDMODE values identify records as new, changed, or deleted in the source system. Processing these values happens automatically, so you may not have been aware of it in the past. However, understanding the procedure is critical if no standard R/3 extractor is available and you must create a generic extractor with delta capabilities that passes the ROCANCEL values on its own. No generic extractor program linked to the R/3 application provides values for field ROCANCEL, so youll need to develop a custom workaround. Also, when data is coming from nonSAP systems, understanding how processing ROCANCEL values works will help you to design better BW solutions. We will provide you with an overview of what happens in the R/3 system when field ROCANCEL is populated with certain key values for a delta load. We will also introduce you to the corresponding InfoObject in BW (0RECORDMODE) and show you how it processes values assigned to the records in field ROCANCEL. Then well show you how we developed a solution based on a custom generic extractor and ODS that processes the ROCANCEL/ 0RECORDMODE values.

ROCANCEL and 0RECORDMODE

The transfer rules for most DataSources available with BW business content and especially those in the Logistics Extraction Cockpit (LO Cockpit) assign ROCANCEL values to the BW update mode InfoObject 0RECORDMODE. When data is transferred using a delta-capable data source, the ODS data manager determines how to process records with the ROCANCEL/ 0RECORDMODE values. Older BW systems such as BW 2.x assign these values to the cancellation/reversal indicator InfoObject 0STORNO, which is mapped to 0RECORDMODE in newer systems to support backward compatibility.

Note! Not all extractors deliver all values for the field ROCANCEL. Use transaction SE16 in the R/3 system and refer to tables ROOSOURCE/RODELTAM for
details about other extractors and possible field values for ROCANCEL.

Unlike an InfoCube, which offers only an addition mode, an ODS can be updated by overwriting as well as adding data. The Addition mode is used when key figures in an ODS, known technically as data fields, contain cumulative data such as sales quantities. The Overwrite mode is evoked when only the last value of a field is required for example, whether the delivery document status is open or closed. The value of InfoObject 0RECORDMODE determines whether the update rule for key figures supports addition or overwrite procedures. This InfoObject is required for delta loads and is added by the system if a data source is delta-capable. It is added to an ODS during the creation process. Records are updated during the delta process using a variety of ROCANCEL/0RECORDMODE values including N for a new record image, A for an additive image, and Y for an update record image used by ODS key figures processing with a minimum or maximum aggregation. We will limit our discussion here to the following four more commonly used values:

X indicates the before image of a record

D deletes the record

R denotes a reverse image

A blank character represents a records after image

When delta requests are processed by the ODS, the ROCANCEL values assigned in R/3 and maintained by InfoObject 0RECORDMODE accurately update the data target automatically in different ways depending on if the ODS update rules are configured for the Addition or Overwrite mode.

Out of R/3
Lets take a look at how ROCANCEL values are passed from R/3 to BW. For our example, a delivery document originates in the R/3 system for a single item with a 1,860 Kg net weight. When the data for that item is extracted into BW, the ROCANCEL value is mapped to InfoObject 0RECORDMODE and the record carries a blank character value into the PSA indicating an after image. For this delta load, were using the SAP DataSource 2LIS_12_VCITM (delivery item) mapped for all objects with Move in the transfer rules, including field ROCANCEL. Its corresponding InfoSource 2LIS_12_VCITM is used to update the ODS. There are three ODS update rule settings for key figures Addition, Overwrite, and No Update and two options for characteristic InfoObjects Overwrite and No Update. In our example, the update rule for the key figure is set to Addition (Figure 1), while characteristics are set to Overwrite.

Figure 1

Set the update rules for key figures to Addition

Before we examine this particular update, lets review the basic design of an ODS. It uses three tables during a delta load. The first is the new data table, or activation queue, where multiple requests can be stored. These requests must be activated to make them available for reporting and for data to be loaded into the data target. Once a request is activated, data from the new data table is deleted and moved to the second table, the active data table. The change log table is the third ODS table and is used to further update the activated request before it moves into the data target. Now lets look at our scenario. Data is loaded from the PSA into the ODS, and the new data table processes the record, which has a blank character value (Figure 2). When the first request for ODS data is activated, the active data table and the change log table the record is sent to and it is deleted from the new data table.

Figure 2

The first delta request is processed by the ODS in the Addition mode

The active data table and the change log table have R columns for values received from the reversal indicator InfoObject 0STORNO and U columns for values of the 0RECORDMODE InfoObject, also known as the update mode InfoObject. For the first request, the 0RECORDMODE value N is passed to the change log

indicating that the record delivers a new image. When the data is loaded into the final data target the InfoCube it contains a record for an item with a net weight of 1,860 Kg.

A Change in Value
The net weight of the item recorded in the R/3 system drops by half 1,860 Kg to 930 Kg and that change is extracted into BW. When a record changes, depending on the R/3 system, two views are generated. The before image is the record before changing, while the after image corresponds to the changed record. DataSource 2LIS_12_VCITM delivers both the before and after image, so the change generates two records. The before image has an X value for field ROCANCEL and a negative net weight of 1,860 Kg, negating the original amount. The other record, represented with a blank character for the ROCANCEL field, is the after image and has the present net weight of 930 Kg. Because the ODS update rules for this key figure are set to Addition, the net weight of the two records (1,860 Kg and +930 Kg) from the PSA are added and the resulting 930 Kg is entered in the ODS new data table (Figure 3). Prior to the second request being activated, the active data table retains the earlier 1,860 Kg data shown in Figure 2 on the previous page. On activating the request, the net weight in the new data table is added to the previous current net weight in the active table, resulting in 930 Kg.

Figure 3

Before and after images are passed to the ODS

On activation, the change log table is supplied with two records: one with a before image net weight of 1,860 Kg, which corresponds to the ROCANCEL/0RECORDMODE X value, and a blank-character value with a weight of 930 Kg. The record of the before-image value is then added to the afterimage value. When the delta load data is transferred from the ODS to the InfoCube, the new request provides a single record with a net weight of 930 Kg. The OLAP processor aggregates the key figures when a query is executed on the InfoCube. In this case, the original document in R/3 was reduced from a net weight of 1,860 Kg to 930 Kg, and the aggregation of the data from the two loads results in a final quantity of 930 Kg [1,860 + (930) = 930], so the report is accurate. When the delivery document is deleted from the R/3 system and the change is extracted to BW, it generates a single delta record containing a net weight of 930 Kg with an R ROCANCEL/ 0RECORDMODE reverse-image value (Figure 4). When loading the data from the PSA to the ODS, the new data table processes the record with the R value as the net weight 930 Kg. Activating the third request causes the record in the active table to be reversed, which deletes the entry entirely.

Figure 4

The third request reduces the quantity value to 0 Kg

The change log table is supplied with one record with 0RECORDMODE value of R, which has a net weight of930 Kg. During the delta load from the ODS to the InfoCube, the new request provides a single record with the net weight of 930. The aggregation of all three requests (namely Rq1, Rq2, and Rq3) results in a total quantity of zero [1,860 + (930) + (930) = 0], which equals the final amount in R/3.

Update Rule: Overwrite

Now, lets look at how data is routed through an ODS when the 0RECORDMODE update rules are set to Overwrite and see how the delta and data transformation process are treated differently. The conditions follow those of the first scenario; were using the data source (2LIS_12_VCITM) mapped for all InfoObjects with Move in the transfer rules including field ROCANCEL. This time, however, the update rules for the key figure are set to Overwrite along with all the characteristics, including the InfoObject 0RECORDMODE. The initial call for data is exactly the same in the Overwrite mode as it is with the Addition setting (Figure 5). One record is extracted into BW with a net weight of 1,860 Kg and it carries a blank-character ROCANCEL value to the ODS via the PSA. The new data table processes the record, and when the data request is activated, the active table receives the same values as in the first scenario. The change log table also enters N for the 0RECORDMODE value and the requested data is loaded into the InfoCube as a record containing an item weighing 1,860 Kg.

Figure 5

The first request in Overwrite mode is identical to Addition mode

The big difference between the two modes comes when the weight changes and two records are extracted (Figure 6). Like in the Addition mode, when the weight is reduced, the records are extracted, indicating a change in weight from 1,860 Kg to 930 Kg. The before image represents the status of a record before it changes in the source system, and the after image is the changed status of the record.

Figure 6

The new data table ignores the X value but it is recorded in the change log

In the Overwrite mode, the ROCANCEL value of X for the 1,860 Kg weight, which reproduced the before image, is ignored when data is loaded from the PSA. The ODS new data table simply enters the record of the blank-character ROCANCEL value with a weight of 930 Kg. While the active table overwrites the earlier 1,860 Kg entry with the new 930 Kg quantity, the change log table is supplied with both the before- and after-image records. Both of these records are required because the InfoCube does not overwrite data. The system processes the 0RECORDMODE value X record with the 1,860 Kg weight and the record with the blank character value and 930 Kg weight. The results are passed from the ODS to the InfoCube like in the Addition mode. The delta load from the ODS to the InfoCube provides a single record with the net weight 930 Kg, and aggregating the two requests (Rq1 and Rq2) yields the 930 Kg weight, which is equivalent to the weight in the R/3. When the delivery document is deleted from R/3, it generates a single R ROCANCEL value. Processing the deleted record of the delivery document with the R value is identical in the Overwrite mode as it is in the Addition mode so the results are the same.

Using the Values

This is how ROCANCEL/0RECORDMODE values are used in a real-life scenario regarding shipments: A client needed reports covering shipments that were in transit to account for any quantity reduced from a sending location, but were not received at its destination location. To support the reports, we had to create a generic extractor because SAP did not have a delta-capable data source for extracting shipment documents into BW. Our generic extractor accommodated shipment records from R/3 that were either newly created or changed, which allowed us to use the ROCANCEL/0RECORDMODE values we just discussed. Because of conditions well describe later, certain shipment records that had been extracted into BW previously needed to be deleted during the delta update. Generic extractors, however, deliver after-image records only, and no extractor program linked to R/3 provides values for InfoObject 0RECORDMODE. We developed an ABAP solution to deliver the D values to field ROCANCEL and accurately process shipment documents deleted in R/3. In addition, we created an ODS for the data staging process to deliver delta load data to the final data target, which is an InfoProvider.

Facing the Problem

We decided to focus our solution on the status of our clients shipments. R/3 provides six status levels for shipment documents. Status level 1 represents partially scheduled shipments and level 2 is for shipments that are scheduled. Levels 3 and 4 are for shipments that are partially loaded or completely loaded, respectively. Status level 5 is for shipments that are missing or are partially confirmed, and level 6 is for shipments that are delivered and confirmed. In general, status levels 1, 3, and 5 reflect shipments that are in flux, while level 2, 4, and 6 records have completed specific parts of the shipment process. To meet our requirements, we focused on shipment documents with status levels 3, 4, and 5 only because these covered in-transit shipments. Shipment documents must be deleted from the ODS after they reach a status level 6 in R/3 to indicate the delivery is complete and the transaction confirmed. A shipment document already extracted to BW with status value of 3, 4, or 5 would need to be deleted in BW once it reached status level 6. Because of the system implementation, the status of a shipment document also could drop to a lower level; for example, from status 4 to status 1. This condition created another concern because such a change could remove a delivery document number from the shipment record in R/3. For instance, if a shipments status dropped from level 4 to level 1 it would no longer have a delivery document number because it was only partially scheduled.

The Custom Solution

To meet the above reporting requirements in BW, we designed a custom ODS with three key fields: shipment document number, delivery document number, and delivery item number. Because we relied on a generic extractor and needed to accommodate quantity changes that increased and decreased on R/3 side, we used the Overwrite mode in the update rules. We could not use the Addition mode because generic extractors only deliver after images. Without the beforeimage values, which are negative values that reflect changes as quantities are lowered, amounts would only increase in the Addition mode. To delete shipment records from the custom ODS that reach status level 6, we wrote a start routine into its update rules. It assigns all shipment records with level 6 status a D 0RECORDMODE value to delete those records previously extracted into the ODS. The start routine also handles those shipment records in the ODS that had their status reduced to level 1 but had been extracted to BW earlier with a level 4 or 5 status. It provides all key field values (shipment document number, delivery document number, and delivery item number) for records to be deleted. All records with a status level 1 are identified in the start routine using the shipment document numbers. The delivery document and delivery item number are read in the active table of ODS. Once the delivery document number and delivery item numbers are filled for those records that have dropped to status level 1, the 0RECORDMODE value D is assigned. This provides the complete key to delete a shipment record extracted earlier in the ODS. We have included the coding used in the start routine of the update rules in Figure 7. OI_SSTSF represents the shipment document status and OI_shnum is the shipment document number code.

Figure 7

Custom start routine added to the update rules

As you can see, ROCANCEL/0RECORDMODE values can be useful when you need to create solutions for specific scenarios. For more information, refer to SAP notes 399739, 335427, 320863, and 333492.