Some companies require more than one SAP BW system for an R/3 instance to satisfy their organizational reporting requirements. You can imagine the logistical difficulties deploying and synchronizing data in multiple BW systems with a lone ERP source. There is also an ever-present risk that users will be forced to endure significant system outages to ensure that the BW systems remain in sync with the transactional system.

Organizations try to achieve a near-zero time gap between their R/3 and BW systems, regardless of the number of BW instances. This continues to be the goal as new BW systems are brought online. Data must be continuously updated in all instances of the BW data warehouse as it is added to, changed, or deleted in the R/3 system.

We’ll show you how BW systems through BW 3.x have been improved to better accommodate ETL procedures and make the process more efficient. We’ll also take a look at how you can use some of this enhanced functionality to add another BW system to your existing SAP landscape without losing access to the R/3 system. With our approach, tasks such as initialization and delta updates run in parallel with the transactional activity so the R/3 system is completely undisturbed.

Initialization and Delta Updates

Initialization is the process of preparing a BW system before performing delta update loads from the transactional system. Most organizations rely on delta extraction mechanisms to load data from R/3 into BW because a delta update streamlines the ETL procedure and eliminates much of the overhead required by a full data load. Delta updates allow current data in an InfoProvider to be subsequently refreshed without dumping all the historical data and starting from scratch.

Typically, initialization requires a large amount of historical data to be acquired during the first load process when bringing an additional BW system online. Despite all efforts to avoid it, reinitialization is also necessary occasionally during the BW system life cycle due to design and requirement changes and source data extraction failures. If, for example, a single delta request is lost, it might be impossible to recover the associated source data without a complete reload.

Regardless if it’s the first load or a reload, initialization requires a significant amount of processing time. It chews up resources in both the transactional (R/3) and BW systems. During the initialization process, other functions and jobs in both systems may experience diminishing performance or service interruptions.

The data initialization process always requires serious planning and this is especially true for Logistics (LO) applications in the R/3 system. LO extractions involve large amounts of data, and the extractors in the LO Extraction Cockpit are not always foolproof because historical data can be altered in the transactional system. Initialization is complicated further by the need to minimize (if not avoid completely) any disturbance to the transactional system.

Evolutionary Changes

As the BW system and related R/3 plug-in features, tools, and techniques have evolved, several options became available to can help you simplify the initialization and delta update process. Relative to the update functionality, perhaps the most important upgrade arrived with Plug-In 2002.1. With this plug-in, SAP delivered three new update methods: the direct delta and unserialized V3 update as well as the queued delta feature. We will take an in-depth look at the queued delta function later.

Updated extractors that feed R/3 data into the BW system now allow direct loads into ODS objects. ODS objects play multiple roles in the BW architecture such as functioning as the basic element of a data warehouse layer. In this role, an ODS is intended to receive data from the transactional source system and to filter duplicate records from the source. The ODS lets you extract the same record (or same sets of records) multiple times without causing problems related to key figures such as double counting.

Repair Full Requests

BW processes data requests as an initial load, a delta load, or a full data load. BW 3.x systems offer the repair full request feature (Figure 1). A repair full request is an unchecked request that allows a BW system to avoid the data integrity check applied to normal full loads. While it is a useful feature, this request poses a risk to data integrity, because the data warehouse skips the data integrity check during loads initiated by this option.

Figure 1

Choose the Repair Full Request option in the Scheduler menu and set a flag in the box as shown

A repair full request is helpful because it allows an ODS to be loaded prior to initialization with data and the ODS can continue to be used with future delta loads. When a traditional full load is applied to an ODS already receiving delta loads, it no longer will receive any further delta loads without re-extraction and reinitialization. This is how the system maintains data integrity. A complete reinitialization and reload corrects all the data in the ODS before further delta updates are run. This was a high-cost requirement in terms of effort and time prior to the availability of the repair full functionality.

In BW 3.x systems, repair full requests are scheduled in the InfoPackage creation screen, which is accessed via transaction code RSA1 from Administrator Workbench. Select the Repair Full Request option in the Scheduler menu and follow the steps displayed in Figure 1. The completed repair full load then appears in the Manage Data Targets screen for the ODS (Figure 2), which is also accessed from Administrator Workbench.

Figure 2

The repair full request appears in the Manage Data Targets screen for the ODS

Add Another BW System to R/3

Now that you understand how the technology has evolved, let’s look at what you can do with these enhancements in a sample scenario. In our example, one BW (BW1) system is in place and receiving delta loads from an R/3 transactional system (Figure 3). A second BW (BW2) system is connected to the landscape and the initialization process commences.

Figure 3

The original BW system (BW1) receives delta loads from a R/3 system

With a large reporting user population on the BW1 system, there is no margin for data errors when bringing BW2 online. You do not want to affect the BW1 system in such a way that data is missed and requires a reinitialization, which is not an option in our scenario. You also risk denying end users access to the R/3 system during extraction, resulting in a system outage that could last as long as several days!

The technique we describe allows you to bring the BW2 system online in a manner that addresses all these concerns. Our approach also can be applied to various other scenarios such as those related to data correction, and can even be used when you first implement a BW system in your landscape. It’s a great way to minimize any disruption of the OLTP source system.

Design Requirements

To avoid the source system disruptions associated with directly initializing an InfoCube, we used a BW data architecture designed with a layer of ODS InfoProviders configured to receive the data from the OLTP source. It is labeled as the Global Data Warehouse in Figure 3. From this ODS InfoProvider layer, subsequent loads within BW provide data to InfoCubes and other InfoProviders.

The target ODS has several design requirements. It must have a key correctly chosen to store data at the individual record level. In case of Sales Delivery Item, for example, the key fields of the ODS are Delivery (0DELIV_NUMB), and Delivery Item (0DELIV_ITEM) along with the source system ID if you have multiple source systems. These fields are sufficient to uniquely identify one delivery item from another.

In the update rules for the target ODS, the update mode must be set to Overwrite for all key figures to properly handle duplicate records sent from R/3. This is maintained in the update rules for each key figure in the ODS.

The 0RECORDMODE InfoObject in BW must be mapped to the R/3 source field ROCANCEL in the transfer rules of the InfoSource feeding the inbound ODS. The ROCANCEL field value is assigned by the R/3 extractor and controls how the ODS engine handles incoming delta records.

ROCANCEL carries a control parameter used by BW to identify a record as a before image, after image, etc. If this field is not mapped, the delta loads may incorrectly update the ODS, thereby improperly allowing records deleted in R/3 to remain visible in BW. The subsequent delta generated by the ODS in the change log will be wrong, and consequently the data targets updated from the ODS will receive incorrect data. For more details on how 0RECORDMODE and ROCANCEL function together, see the article, “Design Better Custom Extractors for ODS Delta Loads,” in the July/August issue of BW Expert.

The Four-Step Solution

As indicated earlier, our technique applies to queued delta updates only. It is critical that the data sources being initialized are activated and set up for queued delta updates or this technique will not work. Our approach allows the initialization of an attached BW system without requiring a transactional system outage and without affecting any other existing BW systems currently drawing data from the same transactional system.

Step 1: Initialize Data Sources

The first step is to initialize the appropriate data sources for the new BW system you are bringing online using the initialize without data transfer option. Use BW’s Administrator Workbench (RSA1) to set the Update Mode to Initialize Without Data Transfer via the Update tab (Figure 4).

Figure 4

Set the Update Mode to Initialize Without Data Transfer on the Update tab

After the initialization without data transfer function is executed, a new entry appears in the delta queue for the BW2 system’s data source. To see the new entry, use transaction RSA7 in the R/3 source system. After the initialization without data transfer option has been selected, all data from the update jobs will be captured in the delta queue (Figure 5).

Figure 5

All data from the update jobs is captured in the delta queue after setting the initialization without data transfer option

At this point, the delta queue in the R/3 source system reflects the initialization from BW2. Figure 3 shows that data source (2LIS_12_VCITM) is initialized for the two BW systems (BW1 and BW2). The delta queue entries for each BW system operate separately. Each system can now request data independent of the other.

Step 2: Fill the Extraction Setup Tables

The BW2 system is now ready to receive delta loads. In this step, you must delete the contents of the extraction setup tables in the R/3 system before filling them again.

Figure 6

Several setup jobs can be executed to accelerate the setup process

Because the delta queue is initialized for BW2, and the setup jobs pull all historic data up to the most current documents, there is no risk of missing any documents created by R/3 users. This is critical because you don’t need to block postings in the R/3 applications or to force an R/3 system outage on the R/3 users.

Step 3: Repair Full Loads into BW2

Once the initialization without data transfer process is complete, the delta queue begins collecting delta records for the BW2 system (Figure 7). Repair full loads can bring all historic data through the current records into the BW2 inbound target ODS objects.

Figure 7

The delta queue collects delta records for BW2 when the initialization without data transfer process is complete

Step 4: Begin Delta Updates into the BW2 System

At this point, all historic data has been loaded and activated in the BW2 inbound target ODS. All new or delta document records collecting in the R/3 system delta queue for BW2 are ready to be loaded. Assuming that update jobs are regularly scheduled, there will be several delta entries. The next step is to schedule your delta update InfoPackage for BW2 data sources (Figure 8). This is accomplished by running Delta extraction from the Update tab of the DataPackage.

Figure 8

Schedule a delta update InfoPackage for BW2 data sources

Once the BW2 delta update InfoPackage is successfully loaded into the inbound target ODS object, it will appear in the Manage screen (Figure 9).

Figure 9

The BW2 delta update InfoPackage appears in the Manage screen after it is loaded into the inbound target ODS object

That’s it! You may now proceed with the subsequently scheduled delta updates to this ODS object in the BW2 system without any negative effects on any other existing production BW systems or on the R/3 transactional users.

For the sake of clarity, we created Figure 10 to recap the steps for this procedure. The sequence of these steps is critical to making this process work properly.

Figure 10

Follow these steps to a delta with no impact on your R/3 system

Using the initialization without data transfer mode, the data sources for the new BW system are initialized in R/3 after the last V3 update job runs. Next, any data in the setup tables is deleted and the tables are refilled. A repair full load data transfer is used to extract the historic data from R/3 to the inbound target ODS in BW. After the historic data is loaded in the new BW system, an InfoPackage is created to schedule delta updates. The data is refreshed in the data sources and transferred to the BW system being brought online. At this point, the new BW instance is ready for use.