Author: Davide Cavallari
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.
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:
- A program in the ECC source system that calls the remote function on the BI system;
- 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.Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
REPORT Z253_BI_LOAD_TRIGGER . ************************************************************************ * * * The request is triggered raising an event on the target BW system * * * ************************************************************************ data mess(128). selection-screen begin of block b1 with frame title text-001. * Logical RFC destination of the BW system parameters: rfcdest like rfcdisplay-rfcdest DEFAULT 'D12' OBLIGATORY. * ID of the event to be raised on the target system parameters: eventid like tbtco-eventid default 'GO_N_LOAD' OBLIGATORY. * Security checkbox parameters: p1 as checkbox. selection-screen end of block b1. * Security check: only if the security checkbox is checked * is the loading event raised if not p1 is initial. call function 'ZBW_EVENT_RAISE' destination rfcdest exporting eventid = eventid EXCEPTIONS BAD_EVENTID = 1 EVENTID_DOES_NOT_EXIST = 2 EVENTID_MISSING = 3 RAISE_FAILED = 4 SYSTEM_FAILURE = 5 MESSAGE mess COMMUNICATION_FAILURE = 6 MESSAGE mess OTHERS = 7. if sy-subrc <> 0. data err_text type string. case sy-subrc. when 1. err_text = 'BAD_EVENTID'. when 2. err_text = 'EVENTID_DOES_NOT_EXIST'. when 3. err_text = 'EVENTID_MISSING'. when 4. err_text = 'RAISE_FAILED'. when 5. move mess to err_text. when 6. move mess to err_text. when 7. err_text = 'OTHERS'. endcase. * An exception occurred message e001(Z253_BI_ALOAD) with err_text. else. * the event has been successfully raised on the target system message s200(Z253_BI_ALOAD) with eventid rfcdest. endif. "if sy-subrc <> 0. else. * the security checkbox has not been selected message i100(Z253_BI_ALOAD). exit. endif. "if not p1 is initial.
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.Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
FUNCTION Z_BW_EVENT_RAISE. *"---------------------------------------------------------------------- *"*"Interfaccia locale: *" IMPORTING *" VALUE(EVENTID) LIKE TBTCO-EVENTID *"---------------------------------------------------------------------- call function 'BP_EVENT_RAISE' exporting eventid = EVENTID * EVENTPARM = ' ' * TARGET_INSTANCE = ' ' EXCEPTIONS BAD_EVENTID = 1 EVENTID_DOES_NOT_EXIST = 2 EVENTID_MISSING = 3 RAISE_FAILED = 4 OTHERS = 5 . if sy-subrc <> 0. MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. endif. ENDFUNCTION.
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.Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
FUNCTION Z253_BI_RSPC_CHAIN_START_MONIT. *"---------------------------------------------------------------------- *"*"Interfaccia locale: *" IMPORTING *" REFERENCE(RFCDEST) LIKE RFCDISPLAY-RFCDEST *" REFERENCE(CHAIN) TYPE CHAR25 *" REFERENCE(POLLTIME) TYPE CHAR3 *" EXPORTING *" REFERENCE(LOGID) TYPE CHAR25 *" REFERENCE(STATUS) TYPE CHAR1 *"---------------------------------------------------------------------- ************************************************************************ * * * Starts a process chain on a target BW system, calling the remote * * function RSPC_API_CHAIN_START * * * ************************************************************************ data: msg(128), nsec type i. CALL FUNCTION 'RSPC_API_CHAIN_START' DESTINATION rfcdest EXPORTING I_CHAIN = chain * I_T_VARIABLES = * I_SYNCHRONOUS = * I_SIMULATE = * I_NOPLAN = IMPORTING E_LOGID = logid EXCEPTIONS SYSTEM_FAILURE = 1 MESSAGE msg COMMUNICATION_FAILURE = 2 MESSAGE msg FAILED = 3 OTHERS = 4. IF SY-SUBRC <> 0. " error when calling remote function case sy-subrc. when 1. message e000(Z253_BI_RSPC) with msg. when 2. message e000(Z253_BI_RSPC) with msg. when 3. message e002(Z253_BI_RSPC) with chain rfcdest '(FAILED)'. when 4. message e002(Z253_BI_RSPC) with chain rfcdest '(OTHERS)'. endcase. ELSE. " ok * Process chain started successfully message s101(Z253_BI_RSPC) with chain rfcdest. * The logID is logged message i202(Z253_BI_RSPC) with logid . CALL FUNCTION 'RSPC_API_CHAIN_GET_STATUS' DESTINATION rfcdest EXPORTING I_CHAIN = chain I_LOGID = logid IMPORTING E_STATUS = status EXCEPTIONS SYSTEM_FAILURE = 1 MESSAGE msg COMMUNICATION_FAILURE = 2 MESSAGE msg. if sy-subrc <> 0. message e000(Z253_BI_RSPC) with msg. endif. while status eq 'A'. * Process chain running (active) message s102(Z253_BI_RSPC) with chain rfcdest. nsec = polltime * 60. " polling time in seconds wait up to nsec seconds. CALL FUNCTION 'RSPC_API_CHAIN_GET_STATUS' DESTINATION rfcdest EXPORTING I_CHAIN = chain I_LOGID = logid IMPORTING E_STATUS = status EXCEPTIONS SYSTEM_FAILURE = 1 MESSAGE msg COMMUNICATION_FAILURE = 2 MESSAGE msg. if sy-subrc <> 0. message e000(Z253_BI_RSPC) with msg. endif. endwhile. case status. when 'G'. * Process chain completed successfully message i103(Z253_BI_RSPC) with logid chain. when 'R'. * Process chain finished with errors message w003(Z253_BI_RSPC) with logid chain. when 'X'. * Process chain interrupted message w004(Z253_BI_RSPC) with logid chain. endcase. ENDIF. "if sy-subrc <> 0. ENDFUNCTION.
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).
REPORT Z253_BI_EXTRACTQUEUE_DELETE . PARAMETER: qname LIKE trfcqout-qname DEFAULT 'MCEX??', dest LIKE trfcqout-dest DEFAULT 'NONE'. DATA sl_entry(96). CONCATENATE sy-uname '&' qname ',' dest INTO sl_entry. CALL 'C_WRITE_SYSLOG_ENTRY' ID 'TYP' FIELD space ID 'KEY' FIELD 'Q22' ID 'DATA' FIELD sl_entry. CALL FUNCTION 'TRFC_QOUT_DELETE_QUEUE' EXPORTING qname = qname dest = dest. if sy-subrc = 0. message s201(Z253_BI_ALOAD) with qname. endif
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.. 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.