Most modern, mid- to large-sized organizations running SAP employ either SAP Business Warehouse (SAP BW) or SAP BW/4HANA systems for business analytics.
These systems load transactional data containing vast amounts of master data from many different types of source systems.
During the transactional data load process, a prerequisite exists within the system to validate that all master data within the transactional records exists in their respective InfoObjects. If the master data is not found, it is automatically created within its respective InfoObject.
Many organizations have governance policies governing master data and prohibit the creation of master data on the fly for a variety of reasons. For example, master data and its attributes are used in further business logic, or master data is consumed in upstream applications (SAP Business Planning and Consolidation, SAP Power BI, etc.).
This blog post explores a creative solution to address this challenge by utilizing a combination of existing SAP BW toolsets and a bit of customization.
The main challenge is to explore and identify a solution which meets the following criteria:
The criteria listed above present many challenges, which can be addressed by many different solutions. After research and meticulous planning, we’ve created a comprehensive solution, described below.
The “update” tab of the DTP configuration provides the following settings for advanced DataStore Objects (advanced DSOs)
The first option allows for loading transactional data without master data being available in its respective InfoObjects. The second option ensures the presence of the requisite master data in InfoObjects used by advanced DSOs (or other InfoProviders) and will not process transaction data into targets.
The challenge with this configuration check is it reverts to “all” or “none,” meaning the setting is applicable to either master data for all of the InfoObjects (first figure below) or none (second figure).
Note: similar configuration settings are available in the “update” tab of the DTP for InfoCubes.
For simplicity (and this is the most widely used scenario), our solution focuses on advanced DSOs only as the target for the first option listed above, “further processing without master data.” This setting will allow all transaction data to be loaded even if master data is not available in constituent InfoObjects, and the referential integrity concept is used to prevent the creation of master data on the fly. It, however, has a limitation—delayed availability of erroneous transaction data in target InfoProviders until master data challenges are resolved.
SAP BW transformation configuration provides a standard functionality for ensuring referential integrity at field-level mapping. It can be turned on by adding a checkmark as shown in the figure below. This checkmark ensures that the master data value contained within the transactional data is present in the InfoObject used in the target InfoProvider. If this check fails, the following will happen depending on the DTP configuration settings:
For the referential integrity check to work, the DTP option (under the “update” tab) should be set to ‘further processing without master data’ for advanced DSOs. The other option (“no further processing without master data”) overrides the referential integrity check.
The configuration settings covered in the two prior steps will force the DTP to error out when referential integrity fails. The DTP failure must be handled by an additional configuration to ensure a successful load into its targets. DTP configuration offers error handling as standard configuration:
With the above settings, if the referential integrity check fails, erroneous transactional records get recorded into the Error Stack. The error messages get recorded in an error message log, and valid transactional data gets loaded into target InfoProvider.
The Error Stack is a transparent table within the SAP system. It has the same structure as its data source. The table stores transaction data temporarily until it gets processed to its target or is manually deleted from table. Below are the main steps of performing an Error Stack analysis.
*This blog post focuses on solution for this configuration.
The solution we have created uses configurations in the BW Process Chain that executes the error DTP, regular DTP, and an ABAP program with two parameters. When the process chain is executed, the following occurs:
Typically, SAP BW, SAP BW/4HANA, and the transactional data load process involves the extraction of data from the source system, transformation, and load into InfoProviders. While performing the data load, it is a pre-requisite to have master data available in relevant InfoObjects and the master data set is created if it doesn’t exist.
When organizational policies around master data doesn’t allow the creation of master data on the fly, different solutions have to be adopted to address the challenges. In this post, a user-friendly solution was presented by utilizing available toolsets in a creative manner, with a bit of customization.