After we evaluated the possible alternatives in the article "Two options for error handling with SAP BW and SQLScript", we found out that the way via the error stack is not always practicable. It only works starting with BW4HANA version 2.0. But even there, this scenario can lead to performance problems.
In the post "Automatically correct erroneous data records with SQLScript in SAP BW" we have already presented an alternative. Another approach would be to first write the incorrect data records into the target and mark them with a flag.
This way you can flag the erroneous data and then correct it via a suitable user interface. But you could also use this data in your reports and add or filter out the erroneous data via the flag.
I will explain this procedure below using an example. In this example, we want to mark as incorrect all data records where the company code is not contained in the master data. This way we can still post all data and then validate the master data.
Our master data table contains the following entries.
Increase the performance of your BW with SQLScript
Accordingly, only company codes 1000 and 2000 are considered as valid. With the following code, all other company codes that are not included in the master data are marked with the X flag.
METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING /BI0/MCOMP_CODE.
outTab =
SELECT comp_code,
CASE
WHEN comp_code NOT IN (SELECT comp_code FROM "/BI0/MCOMP_CODE") THEN 'X'
ELSE ''
END AS flag,
currency, '' as recordmode, amount, record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;
errorTab= SELECT * FROM :errorTab;
ENDMETHOD.In addition to company codes 1000 and 2000, our data also contains company code 3000, which is not included in the master data and is therefore marked.
All data records are posted to the target InfoProvider and are available for further processing.
Are you planning to migrate to SQLScript and need help in 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 advise you.
