How to Work Around SAP BW Flat File Data Load InfoPackage Failures

Many organizations employ SAP ERP and SAP Business Warehouse systems for business analytics on transactional data loaded from many different types of source systems.

They also utilize flat file (e.g. .CSV format) sources for uploading transactional data into their data warehousing systems. Typically, the loaded data is further processed, cleansed, and consumed in upstream systems with additional business logic.

The flat file data loads are typically automated by utilizing the process chain (PC) functionality. The process chain picks up the flat file from the designated AL11 directory and delivers it via InfoPackage for further upstream processing into BW layers. The BW PC processes one flat file at a time; however, during the financial close process or any other time-sensitive cycle, there could be multiple flat files containing transactional data from different business units or segments that need to be processed at regular and frequent intervals. This is easily achieved by scheduling process chains at the pre-defined frequency, e.g. every 30 minutes.

This works just fine as long as the flat file is present and ready to be processed in the designated AL11 directory. When they are not available for processing, the InfoPackage fails if not handled with an appropriate solution. This creates a failed empty request in the persistent staging area (PSA) layer. This situation could potentially create hundreds of failed process chain instances and failed requests in the PSA layer during a typical fiscal period.

This post will explore a creative solution to address this challenge by utilizing a combination of existing SAP BW toolsets along with a bit of customization.

The Challenge

The main challenge is to explore and identify a solution that meets the following criteria:

1. Successful execution of the process chain when flat files are not available in the designated AL11 directory. This essentially requires an intelligence built into the BW InfoPackage to identify that non-availability of flat files is a valid business scenario.
2. Purge erroneous PSA requests created because of the non-availability of flat files.

Solution

SAP provides a rich framework of toolsets within the SAP BW system that can be deployed to build a robust solution for a seamless user experience. The toolset includes process chains, function modules, tables (both standard and custom), and InfoPackages that contain an ABAP exit routine (shown below). This routine is executed while loading flat files to the PSA layer. It can also access BW objects which can be used for functional validations and temporary storage of flags or variable values.

Assumptions

Before getting started, there are a few assumptions that we worked with:

• The dummy file (more on this later) must have the same structure as the actual transactional data file. The file contains only the header record and no transactional data.
• The dummy flat file name and initial status is correctly configured in the control table.
• This workaround has been developed and deployed on an SAP BW system with the below specifications.

Note: While this solution was developed on an SAP BW system, it is applicable to SAP BW/4HANA systems as well with some modification. For example, the PSA layer is not available in SAP BW/4HANA.

Workaround Steps

Follow these steps to create the workaround.

1. Create a dummy flat file (e.g. “DUMMY_FILE.csv”) with just a header in it and no transactional data. The structure of the header should be the same as the real flat file structure. Place the dummy flat file at the designated location in the AL11 directory on the SAP BW server.
2. Create a custom table or use any existing table to store the name of the dummy file with some flag representing ‘YES’ or ‘NO’.

3. Create an InfoPackage with a data validation routine to process flat file data.
   1. This routine is used to get the list of flat file names being processed from the designated AL11 directory location (see figure below) and sort them in chronological order (latest first).
   2. When only the dummy file is available to process, it returns only one file in the list with its name.
   3. The routine also gets the dummy flat file name from the table (ZTABLE_TEST in this case).
   4. If the two file names from the previous two points match, the custom table is updated with the flag “YES.” If not, the flag is set to “NO.”

4. Create a custom class (e.g. ZCLASS_TEST) with a custom method Z_DF_GEN_STATUS. This method checks the custom table ZTABLE_TEST for the status of (“YES” or “NO”) against the dummy file name.
   1. The parameter i_variant (method import parameter) could be the flat file name or some other flat file identifying entry in the table (e.g. “ABC” in this example).

5. Create a custom BW process chain (such as PC ZBW_TEST shown in the figure below) with a custom decision process to check if the InfoPackage processed a dummy flat file or real flat file.

6. The custom decision process (“Is Flat File Type Dummy”) could be configured by double clicking for multiple options (see below). The figure shows two options but it can be configured for more than two options if required by the business logic. The last option is always configured as an error.

7. Configure the formula for each option by clicking on the pencil icon. It opens up a formula editor.
8. Configure the formula as shown in the two figures below for Option 1 and Option 2 respectively for checking if the flag set up in the control table (ZTABLE_TEST) is set to “YES” or “NO.”
   1. The input parameter (“ABC”) to the method call is the primary key of the record in the control table (ZTABLE_TEST) and it could represent process chain name or process itself.

Results

Here are the results that we come up with after performing the configuration.

1. The custom table ZTABLE_TEST was configured as shown below.

2. A dummy flat file named DUMMY_FILE.csv was placed in the designated AL11 directory.
3. The process chain was executed to process the flat file present in the AL11 directory. The process chain correctly determined that the InfoPackage has processed the dummy flat file and also processed the highlighted branch of data flow/purged the requests from PSA (see figure below). The solution was used in a real application where this type of process chain is scheduled to run every 30 minutes and all seven days a week. Each run successfully distinguishes between dummy and real flat files and keeps the PSA clean.

4. The custom table accurately reflects the status of the dummy file as “YES.”

Conclusion

Organizations utilizing SAP Business Warehouse most often use flat files for uploading a variety of transactional business data into their SAP BW systems. The flat files are processed by running a process chain which inherently processes one flat file at a time. When there are no flat files present in the AL11 directory to be processed, the BW InfoPackage fails and creates an empty request in PSA. A number of failures and empty requests created in PSA could soon spiral out of control.

This post presented a solution to address this challenge by utilizing InfoPackage routines in a creative way. In this approach, the routine sets a flag in a custom table to highlight whether it processed a real flat file or a dummy flat file. A custom decision process within the process chain checks this flag using a custom class and method. If indeed a dummy file was processed, the process chain ends the execution gracefully after purging erroneous PSA requests.

Thank you to Rajesh Hemnani, Snehal Mehare, and Venka Reddy Buchi for their assistance with this post.