Two options for error handling with SAP BW and SQLScript

SAP BW loading processes are often terminated due to incorrect data records. To avoid these terminations, appropriate error handling is useful. Basically, there are two ways to address error handling in SAP BW HANA systems. In this article, I will explain the individual scenarios in detail.

So the first thing you can do is to use the DTP Error Handling. Another approach would be to automatically correct the incorrect data records. In the first scenario, you can benefit from the automatic processes of a SAP system. However, flexible further processing is not possible in this case. The second scenario involves more development effort, but you can handle the incorrect data records more flexibly. For example, you can include automatic checks within transformation routines and make corrections.

In the following, I will first go into DTP error handling and explain how the error stack works. Then I will show you how to perform automatic corrections with SQLScript.

First scenario - DTP error handling and manual correction in the error stack

In the first scenario, we use the mechanisms of the DTP error handler. Here, we write the incorrect records into the error stack, where they can be corrected manually afterwards. 

Note that by default each transformation is executed in two iterations. First, the incorrect records are filtered out. Then the actual logic of the routine is applied to the remaining, correct data. 

Semantic grouping plays an important role in determining incorrect records. Thus, you can use the Extraction grouped by setting in the Extraction tab of the DTP to define fields that serve as keys for error handling. After an erroneous record has been identified, all records with the same key value are also moved to the error stack.

In the following I explain the meaning of the semantic groups by means of an example. Let's consider the following table. This represents different orders, each of which contains several order items:

In the purchase order with the number 1338, the record type is missing in position 20, which means that this record is considered to be incorrect:

With the help of semantic grouping, the entire order can be marked as incorrect:

These records are filtered out and written to the error stack:

Then, in the second run, the actual logic of the routine is applied to the remaining records:

Error handling ensures that only the correct records are posted to the system. The inconsistent records can be corrected manually and posted using an error DTP.

The disadvantage of this scenario is that the data is processed twice, which can result in longer loading times. This is particularly significant for routines for individual fields, since the complete data is processed a second time. This means that the runtime is extended by a factor of n+2 for n field routines used. For this reason, you should dispense with field routines and use an end routine instead.

In addition, the error DTP is also only used with BW/4HANA 2.0. With BW/4HANA 1.0 and BW 7.5 on HANA, error handling cannot be used for the HANA runtime. If you already have a BW/4HANA 2.0 in use and want to use this solution, you should critically examine the use of this solution from a performance perspective. Alternatively, you can consider the second scenario, which we explain in the following paragraph.

Increase the performance of your BW with SQLScript

Second scenario - Automatic correction of incorrect records

This scenario is fundamentally different from the first method. Instead of collecting the erroneous records in the error stack, the aim is to be able to process the erroneous records flexibly.

For example, you can include a field in the target InfoProvider that serves as a status indicator. With this flag you can mark the incorrect data records. If necessary, you can also store the error type there to facilitate subsequent error handling.

Basically, all data records are updated, whereby the incorrect records are marked with a flag. This procedure has the advantage that reports can also include records with errors. You can use the Status indicator field to control whether the records with errors should be displayed or filtered out.

To correct the incorrect records, you can use a transformation that writes to the target InfoProvider itself and makes automatic corrections. Another option would be to post the data to another DataStore object, where you can edit the data using your own UI (such as NextTables). You can use the RSDRI_INFOPROV_READ and RSDSO_WRITE_API or RSDSO_DU_WRITE_API functions as APIs for this. Afterwards you can write the corrected data back into the actual target InfoProvider or process it in another way..

If you do not want to store the incorrect data records temporarily in a DSO, there is another alternative. You can correct the incorrect data records directly in the transformation, without storing them in a DSO. You will learn how this works with the help of SQLScript in another article that we will publish later in this series.

Error handling with SAP BW and SQLScript - Our Conclusion

Depending on the requirements profile, the first or the second scenario makes more sense.

In the first scenario, you ensure that only the consistent data records are updated. However, this scenario only comes into question as of version BW/4HANA 2.0. The deployment scenario should be tested carefully in terms of performance, as the data is processed twice. You can use SAP's standard functionality here, but you are also bound by its capabilities. For example, you can only correct the incorrect data records manually and then load them with an error DTP.

In our next article, which we will publish in this series, we will then go into detail about this scenario and provide helpful SQLScript coding snippets for implementation.

In the second scenario, you have greater flexibility - you have more options for processing the inconsistent data. Whether you first store the incorrect records in a DSO or correct them on-the-fly depends on the individual requirements. However, in doing so, you move away from the SAP standard and have to rely on your own developments. We will discuss both alternatives in detail in the following articles.

Are you planning to migrate to SQLScript and need help planning the right strategy? Or do you need experienced developers to implement your requirements? Please do not hesitate to contact us - we will be happy to assist you.