Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://go.sap.com/community.html
Thank you,
The SAP Community team.
The SAP Community wiki will be closed to new content submissions from December 7 6:00 p.m. CET to December 11 6:00 p.m. CET.
All content created during this period will not be available/restorable after December 11.
We apologize for the inconvenience, but we need to take this action to improve and maintain the SAP Community wiki performance.
Please plan your tasks accordingly.
Skip to end of metadata
Go to start of metadata

Author: Davide Cavallari
Submitted: 5/24/2007
Related Links:

Description

Functionalities for managing the data loading process of a target BW system from the source ECC system are described. Such functionalities include programs for triggering a data request, for starting a process chain and for deleting an outbound queue (AKA extract queue).

A function module is discussed which starts a process chain and periodically monitors its execution, until it has completed. In this context, the basic features of the RSPC API are also showed.

Introduction

Loading data from an OLTP system to a datawarehouse might be a highly resource consuming process. Moreover this activity is often very time consuming, as well. This also holds true when the two systems involved are SAP ECC and SAP NetWeaver BI respectively.

As the ETL processes should be carried out preferably at night-time, it's generally a good idea to automate them as far as possible. In the following I present some pieces of code which can be helpful in this regard.

Triggering a loading process on a BI system

The simpler method that can be used for achieving a better degree of automation is triggering a loading process on the BI system, by raising an event in the BI system from the ECC system, as soon as the extraction process has completed on the ECC side. The triggering event needs to have been registered on the BI system, with transaction SM62.

When the extraction job finishes, a subsequent job is started and a program executed, which calls a remote function on the BI system. This remote function raises an event that is caught by the BW scheduler and the loading process is started.

In the described scenario, two components are needed:

  1. A program in the ECC source system that calls the remote function on the BI system;
  2. A remote-enabled function module that is called by the triggering program.
Triggering program in the ECC system

Here is shown the first component's code. The program presents the user a selection screen asking for three inputs:

  • The name of the RFC logical destination identifying the BI system (rfcdest);
  • The name of the event to raise on the target BI system (eventid);
  • A confirmation checkbox to actually raise the event (p1).

If the confirmation flag (p1) has been set, the remote function ZBW_EVENT_RAISE is called on the target system (rfcdest) and the chosen event (eventid) is raised. We'll see how this function is defined in the next section.

The rest of the code is straightforward. Exception handling is performed and messages are output.

Triggering program in the ECC system
Remote-enabled function module on the BI system

Here's the definition of the remote function that get called by the ECC triggering program. It is defined in the BI system and basically calls another function, the standard module BP_EVENT_RAISE.

As the parameters EVENTPARM and TARGET_INSTANCE are not needed, they are not exported. Consequently, the interface of Z_BW_EVENT_RAISE is simpler than that of BP_EVENT_RAISE. It just imports the EVENTID and returns some exceptions, should anything go wrong (these are the same exceptions declared in the BP_EVENT_RAISE interface).

Thus Z_BW_EVENT_RAISE acts simply as a wrapper of the standard BP_EVENT_RAISE. So why is it needed at all? The reason is BP_EVENT_RAISE is not remote accessible, therefore a second function is needed in between.

Remote-enabled function module on the BI system

Starting a BI process chain from ECC

Although the technique suggested above is quite a basic one, in principle it could be used to start a process chain. Nevertheless it doesn't offer an option to get back information about the status of the loading process. As a consequence, ECC has no way to know whether e.g. the BI loading process is still active, or it has already finished, or it was interrupted. Thus this technique is more suitable for starting a simple InfoPackage loading via the scheduler, rather than a complex process chain.

In this second scenario it's more advisable to employ the RSPC API. Among others, it comprises a collection of remote-enabled functions that can be called specifically for triggering and monitoring a BI process chain. These function modules are defined in the target BI system, and get called by some loading program in the ECC system.

Here follows the code of a function (Z253_BI_RSPC_CHAIN_START_MONIT) that can be defined in the ECC system. It starts the remote process chain with the function RSPC_API_CHAIN_START defined in the target BI system, and monitors the process chain's status until its execution has completed, using the function RSPC_API_CHAIN_GET_STATUS.

This function Z253_BI_RSPC_CHAIN_START_MONIT accepts three input parameters:

  • The logical RFC destination's name of the BI system;
  • The technical name of the process chain to be started;
  • The polling time of the process chain's execution status (i.e. the time interval between a check of the execution status and the next check).

When a process chain is started, the BI system assigns an ID to that execution. This is called the LOGID, and identifies that specific execution of that process chain.

The output parameters of the function Z253_BI_RSPC_CHAIN_START_MONIT are:

  • The logid of the execution;
  • The final status of the execution (i.e. the status that is reached when the run has completed).

If the process chain is successfully started by RSPC_API_CHAIN_START, the status of the execution is checked every polling time interval, and that status is output. When the process chain's execution finishes, the final status is output and passed back to the caller.

Function module to start and monitor a BI process chain

Deleting an outbound queue

The two functionalities described so far are particulary useful when the extraction job on the source system is the setup tables' initialization of Logistic DataSources. Usually the initialization job is extremly time consuming, and has to be performed when the ECC system is locked to the users. Therefore it is often performed at night-time. To minimize system downtime, the loading process on the BI system should be started immediately after the setup job has finished.

All LO DataSources support the delta loading process, since large amount of data are involved in logistic applications. Three different update methods are provided in support of the delta process, whose features are explained in detail in the SAP Note 505700 - LBWE: New update methods as of PI 2002.1 (SMP login required):

  • Direct delta;
  • Queued delta;
  • Unserialized V3 update.

According to LOGISTIC COCKPIT DELTA MECHANISM - Episode three: the new update methods, the queued delta mechanism is probably the most used and popular. This update method relies on the so called outbound (or extract) queues, that are basically a buffer used to decouple the applications' tables from the DataSources.

As the outbound queue of the involved logistic application has to be deleted before the reconstruction of the setup tables begins, we need a functionality to programmaticaly delete this queue. In fact only a dialog procedure is provided by SAP to delete an outbound queue (transaction LBWQ), so the following code fragment may be used in order to achieve our aim.

This program accepts two input parameters:

  • The extraction queue's name, as reported in the extraction queue monitor (transaction LBWQ);
  • RFC logical destination (select NONE if you want to delete a local extration queue).
Program for outbound queue deletion

Putting all together

I have compiled an archive with the cofile and datafile of a change request – unfortunaly when I compiled this package I wasn't aware of SAPlink.. (sad) This CR has the programs and reports shown in this page, as well as some other utility and test procedures.

You can download the package here. Actually it's a ZIP file, so you may need to change its extension to .zip if different.

Once you've put the two request files in the appropriate directories (e.g. /usr/sap/trans/cofiles/ and /usr/sap/trans/data/), you can import the request with the transaction STMS. The request number is D10K910049.

Using SE80 you'll find two new development packages, Z253_BI_ALOAD and Z253_BI_RSPC. As described in the change request's documentation, the first is the main package, while the latter fullfils some dependincies of the first.

Z253_BI_ALOAD contains the two reports described above (Z253_BI_EXTRACTQUEUE_DELETE and Z253_BI_LOAD_TRIGGER) as well as a third report called Z253_BI_GO_CHAIN, which uses the function module Z253_BI_RSPC_CHAIN_START_MONIT shown above to start a process chain.

Now instead of working at night-time till late, when you need to initialize a delta process, you can simply schedule a job to run the program Z253_BI_EXTRACTQUEUE_DELETE, which starts a job that executes the statistical setup program (e.g. RMCVNEUF for the billing application), which in turn starts a job that executes the program Z253_BI_GO_CHAIN. Thus the process chain is started on the BI system as soon as the statistical setup has finished in the ECC system.