How to Analyze Errors of SAP BW and SAP BW/4HANA Data Loads

Flat files are used for uploading a variety of transactional business data into SAP business warehouses; typically, the loaded data is further processed, cleansed, and consumed in other systems running appropriate business logic.

The transactional data files are often prepared by functional business users, which sometimes accidentally add mistakes into the data. These mistakes can lead to bad data in the system and create subsequent challenges. These functionally inconsistent data sets can result in costly delays while IT teams are investigating root causes and eventual solutions.

This blog post explores a solution for organizations using SAP Business Warehouse (SAP BW) or SAP BW/4HANA that find they sometimes run into errors after loading flat files. The solution makes use of a combination of existing SAP BW toolsets and a bit of customization.

The Challenge

The challenge project teams face is to ensure that the uploaded transactional business data is functionally valid, utilizes the correct master data, and makes business sense. The most important challenges include:

1. Validating the data set contained in the flat file by using a set of rules before loading the data to the system.
2. Capturing erroneous data sets in a spreadsheet and sending it as an attachment to an email distribution list.
3. Storing the erroneous data set in a persistent layer (for example, a DataStore Object [DSO]) for consumption in further error analysis.
4. Reporting on errors to drive efficiency by training users on the most frequently occurring errors.

Below shows one of the sample flat files we used for building the custom error analysis solution. Multiple such flat files were used in testing and presenting this solution.

Solution

There are a lot of toolsets within the SAP ecosystem that can be used to build a solution for this problem. This toolset includes SAP BW InfoPackages, ABAP routines, standard email function modules, and configurable email services.

Assumptions

We are basing this solution off of the following assumptions:

1. Flat files are available for processing in the AL11 directory at a defined location.
2. VBA macro processing is enabled in Microsoft Excel and for the error analytics to be performed.
3. This solution has been developed and deployed on an SAP BW system with below specifications, but is also applicable to SAP BW/4HANA systems with a few changes, which we’ll show.

Process

First, create a new direct update DSO with the same structure as the flat file, which will enable the storage of the entire business transaction set. The DSO should have some additional fields via InfoObjects (for example, Message ID, Message Text, Date and Time, etc.) The first figure below shows what this looks like for SAP BW, and the second figure shows what it looks like for SAP BW/4HANA.

It is prudent to use new InfoObjects in an error DSO to capture all the error data in each InfoObject. This is to ensure that all other InfoObjects are not tainted by erroneous data or employees potentially creating reporting or other challenges.

Why direct update DSOs? This allows writing records directly into the active table of a DSO without needing a Surrogate ID (SID). Alternate options include writing optimized advanced DSOs, which needs all SIDs to be created before reporting on DSOs (auto SID generation is a challenge, however); and using standard advanced DSOs, which requires a regular data load process for the entire erroneous data set, necessitating much more work and a wider footprint. Direct update advanced DSOs (used for planning) are supported on SAP BW 7.5.

SAP BW provides standard user exits for executing ABAP routines within InfoPackages while loading flat files, as shown below.

For SAP BW:

And for SAP BW/4HANA:

The data validation routine is used for functional validations on data sets contained in the flat file and being processed through the InfoPackage.

The next step after identifying the erroneous data sets and error messages is to record the same error (and error message), as well as the erroneous data set, in the active table of the standard direct update DSO (ZERRDSO - created already at the beginning of the solution). It is important to do this in the same validation routine; see below.

Next, create a BEx report on error DSO with the appropriate selection variable (for example, date range, to limit the data set in the report). The BEx report can have all the fields of the DSO for quick reference, along with the error message(s).

Create a Microsoft Excel workbook on the BEx report, which will facilitate further analysis of erroneous data sets. The workbook should have these tabs:

• “Error Data” for displaying all the error data for a specific time period.
• “Error Analytics” for various analytical snippets created using Microsoft Excel functions and VBA macros like:
  • A macro to read the data from “Error Data” and paste to “Error Analytics” tab, as well as perform an error count.
  • A macro to remove duplicate data in applicable columns in “Error Analytics.”
  • A macro to sort applicable columns in “Error Analytics.”

Results

The implemented solution uses configurations in the SAP BW process chain that executes the InfoPackage with a data validation routine. The routine performs pre-defined functional validation of the entire data sets. Only successfully validated flat files will then be loaded into the system for further processing and consumption in upstream systems. Any erroneous data sets are directly recorded in the active table of the direct update DSO, along with an error message and all necessary fields (such as current data and time). The request is set to failure status. The process chain has a step to purge persistent staging layer (PSA) data when the request (InfoPackage) has failed in functional validation. This is to avoid accidental processing of the same request from the PSA.

The testing was performed using multiple flat files containing transaction data with multiple errors. All flat files were processed in quick succession and the data validation routine in the InfoPackage recorded all the erroneous business transactions, as shown below.

The same erroneous transaction data and errors, when viewed using the BEx report, are shown here.

Lastly, we present the workbook with error analysis performed multiple ways: for a specific time period (error frequency, error frequency by flat file name, and error frequency by date).

Conclusion

Organizations employing SAP BW or SAP BW/4HANA may run into errors when uploading flat files into the business warehouse. It’s important for these to be validated against functional errors to avoid subsequent upstream challenges. These functional errors—messages and respective transaction data sets—could be persistently stored to derive some error analytics that could be used in a variety of ways. In this post, we presented a solution that utilizes a combination of standard BW toolsets and some customized components.

Thanks to Snehal Mehare and Rajesh Hemnani for their assistance with this post.