As more complex Extraction, Transformation and Loading (ETL) scenarios are developed to achieve the business requirements; ABAP code will be used in the transformation routines. This will require a new level of understanding because with more flexibility and control over the data comes the ability to corrupt it.
Do you load data out of a DataStore in delta mode? Yes, keep reading. This does not apply to data that is always guaranteed to be done using a full load. This discussion focuses on a transformation built using a DataStore as the DataProvider.
When coding in a transformation start routine or end routine, it is important to pay attention to the 0RECORDMODE InfoObject, also known as the RECORDMODE field. It is a meta-data record that is used to describe the fundamental nature of the current record, completely separate from the business data stored in the other fields of the record. You must make sure that your code honours and handles each record in accordance with the RECORDMODE value. There are five important values:
- A ” ” [space] identifies an After-Image record;
- A “N” identifies a New record;
- A “X” identifies a Before-Image record;
- A “D” identifies a Deleted record;
- A “R” identifies a Reversal record.
The simplest extractor is only capable of delivering After-Image ( ) records. This is usually without any RECORDMODE field in the extract structure because the extractor can only deliver a full load that is a snapshot of the current data. No delta extraction is possible. All records are assumed to be “The value as at time of extraction”.
An enhanced extractor will detect changes in source records. For new records added to the source table, an extractor record with RECORDMODE = New (N) will be delivered. For changed records in the source table, two extractor records will be delivered. The first with RECORDMODE = Before-Image (X) and the original data values. The second with RECORDMODE = After-Image ( ) and the new data values.
“the best way to understand 0RECORDMODE values
is by how they impact the processing of the record”
A comprehensive extractor will also be able to detect records that have disappeared, been removed or dematerialised (like an ensign in a transport buffer malfunction). For records deleted from the source table, an extractor record with RECORDMODE = Deleted (D) will be delivered. Some extractors will deliver a full copy of the record that disappeared while others may only deliver the minimum number of fields populated to identify the full unique key of the record. For deleted records, only the full unique primary key is of any importance.
A complete extractor will also consider business logic applied to the source tables. A field indicates the business logic has reversed or cleared the record. This can be represented in several ways. The original values could be left alone, they could be set back to zero (0) or they could be negated (multiplied by -1). Each extractor will have its own way of creating an extractor record to appropriately reflect this business logic.
As you can see, each extractor needs to be investigated to fully understand its capabilities. This in turn will define the requirements of each transformation routine to handle the RECORDMODE field and its possible values.
The RECORDMODE field is commonly used in four scenarios:
- By InfoSources that handle delta capable DataSets;
- By source system extractors to deliver data across to BW;
- By DataStores when loading, activating and creating change log records;
- By the transformation program loading into a Cube.
Are you using InfoSources in the BW ETL and the DataProvider has a RECORDMODE field? Yes. Then make sure you include the SAP BW 0RECORDMODE InfoObject in the InfoSource definition and map its flow from the DataProvider through to the DataTarget. If you do not do this then the program that is the transformation going into the DataTarget will treat all records as if they were After-Image ( ) records. The impact will be that New (N) records are ok but Before-Image (X), Deleted (D) and Reversal (R) records will corrupt the key figure values in the DataTarget.
You will also need to ensure the business logic is applied to the record with consideration of that record’s RECORDMODE value.
For Example, A Deleted (D) record going into a DataStore will only require the full unique primary key and all business logic can be ignored (not executed), saving valuable processing time of the DataPacket. However, a Deleted (D) record going into a Cube should have all fields on the record available and processed using the business logic because all Characteristics in a record are considered to be the full unique key (from the cubes point of view).
For Example: Are any of your key figures defined with MIN or MAX aggregation? Yes. Then you will need to pay closer attention to the key figure value in Before-Image (X), Deleted (D) and Reversal (R) records. They are not handled as true technical/mathematical representations like you would expect. Hence when you apply a business rule to multiply a key figure value by -1, you could be corrupting a Before-Image (X) record when you should only be modifying New (N) and After-Image records.
The best way to get a complete picture of how each RECORDMODE value should be handled is to ensure you do full developer testing of all data scenarios going into a DataStore and a Cube.
Given that DataStores are commonly used in data modelling scenarios, it becomes clear that a lot of the BW ETL will have the RECORDMODE field. This means you should add two more checks to your code review list before releasing the transport/solution from the development system:
- Do downstream InfoSources have the RECORDMODE field and its flow mapped?
- Are the transformation routines handling all known RECORDMODE values properly?
Are there other ABAP scenarios needed in the code review list?