Automation of the Data Load from SAP R/3 to BI

[Former Member]

This is my First Blog, hope this will be useful in your future projects.

This blog is meant about pulling data from R/3 System to BI System automatically from R/3 system without logging into the BI System with all the User Selections.

The Sap ECC User can easily load this data and getthe status of the data load without logging into BI System.

Scenario::

Caution: This is just an example not real scenario.

There is a company where the User wants to forecast the sales by viewing Actual sales. Then he post the data from ECC and load the data to BI where he can also send data to Application server through the open hub destination and then can check his data in SAP BPC (Business Planning and Consolidation) and after viewing this report if he wants to change the Forecast values he can do and load again as he wish.

I'll clearly explain this with an beautiful example provided with the ABAP program code.

Coming into the work, First, we have to create a Process chain on the BI side.

For example: Here i want to load data for a Cube from COPA datasource. So, i've created a Process chain from the Datasource toDSO and DSO to Cube. From here the Data is sent to BPC (to Application server) where the data is used for BPC through the Open Hub Destination.

Hope this process chain creation is not a tough job for you guys, anyways for simple cut-off discussion to small, we can create the process chain the RSPC (T-code).

After the creation of the Process chain, you can create a T-code with ABAP Program where the program code is given below for your reference.

You can make your own changes as per your requirement.

ABAP PROGRAM::

Here our program is the ZTEST_BI, this program has to create on R/3 side.

I'll clearly explain how this program works.

1. We need to make a selection screen for the Process chain, User parameters(here i took, Controlling area, Costcenter, Cost element, Fiscal Year/Period, Version. You can give your own parameters)

2. In order to get the Process chain list we need to use this Function Module: 'RFC_GET_TABLE_ENTRIES' from the table 'RSPCCHAIN' from BI System,here we need to provide the BI system client.

3. In order to embed these Process chain list with 'F4' functionality we need to create a FORM with the function module ''F4IF_INT_TABLE_VALUE_REQUEST'.

4. The User values (Parameters) from the selection screen can be uploaded through a remote function module to BI system.The function module here is 'ZIP_SEL_FM'. This function module send the user selection values to BI generic table. The function module code is given below.

5. To create a generic table we need to use SM30(t-code).

The above are the functions done by Program.

Here the Program name is "ZTEST_BI"

REPORT ZTEST_BI.

*Declaration of tables.
TABLES: bseg, mvop, coplnctrl, rspcchain, WRMA_EVALPER.

 

*declarations to get the list of process chain
DATA: BEGIN OF l_itab OCCURS 0,
chain_id LIKE rspcchain-chain_id,
END OF l_itab.

 

*declarations for the table to post to BI generic table.
DATA: BEGIN OF gt_itab OCCURS 0,
client     LIKE sy-mandt,
pchain   TYPE c length 25,
fnam     TYPE c length 30,
seq       TYPE n length 3,
sign      TYPE c length 1,
opt        TYPE c length 2,
low        TYPE c length 45,
high      TYPE c length 45,
END OF gt_itab.

 

DATA:  pc_itab    LIKE rspcchain occurs 0,
wa_pc_itab TYPE rspcchain.

 

DATA: e_logid   TYPE rspc_logid,
e_t_log   TYPE rspc_s_msg occurs 0 with header line.

 

DATA: t_results   TYPE TABLE OF ddshretval,
wa_results  TYPE ddshretval.

 

INITIALIZATION.

*To get the Process chain's.
CALL FUNCTION 'RFC_GET_TABLE_ENTRIES' DESTINATION 'DBDCLNT100'
EXPORTING
table_name = 'RSPCCHAIN'
TABLES
entries    = pc_itab.

*selection screen when program executed.
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS   : chain_id FOR rspcchain-chain_id no-EXTENSION NO INTERVALS OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK b1.

 

SELECTION-SCREEN : BEGIN OF BLOCK b2 WITH FRAME TITLE TEXT-002.
SELECT-OPTIONS   :   kokrs     FOR   bseg-kokrs,
kostl     FOR   bseg-kostl,
kstar     FOR   bseg-kstar,
fiscper   FOR   WRMA_EVALPER-PERIOD,
versn     FOR   coplnctrl-versn.
SELECTION-SCREEN : END OF BLOCK b2.

 

AT SELECTION-SCREEN ON VALUE-REQUEST FOR chain_id-low.
PERFORM value_request.

 

*to get the list of Process chains
LOOP AT pc_itab INTO wa_pc_itab
WHERE chain_id+0(1) = 'F' AND objvers = 'A' AND type = 'TRIGGER'.
APPEND wa_pc_itab TO l_itab.
ENDLOOP.

 

*to get the user parameters to BI
START-OF-SELECTION.

*For the Controlling Area.
gt_itab-client = sy-mandt.
gt_itab-pchain = chain_id-low.
gt_itab-fnam   = 'KOKRS'.
gt_itab-seq    = '1'.
gt_itab-sign   = kokrs-sign.
gt_itab-opt    = kokrs-option.
gt_itab-low    = kokrs-low.
gt_itab-high   = kokrs-high.
APPEND gt_itab.
CLEAR  gt_itab.

 

*For the Cost Center.
gt_itab-client = sy-mandt.
gt_itab-pchain = chain_id-low.
gt_itab-fnam   = 'KOSTL'.
gt_itab-seq    = '2'.
gt_itab-sign   = kostl-sign.
gt_itab-opt    = kostl-option.
gt_itab-low    = kostl-low.
gt_itab-high   = kostl-high.
APPEND gt_itab.
CLEAR  gt_itab.

*For the Fiscal Year/Period
gt_itab-client = sy-mandt.
gt_itab-pchain = chain_id-low.
gt_itab-fnam   = 'FISCPER'.
gt_itab-seq    = '4'.
gt_itab-sign   = fiscper-sign.
gt_itab-opt    = fiscper-option.
gt_itab-low    = fiscper-low.
gt_itab-high   = fiscper-high.
APPEND gt_itab.
CLEAR  gt_itab.

 

*For the Version
gt_itab-client = sy-mandt.
gt_itab-pchain = chain_id-low.
gt_itab-fnam   = 'VERSN'.
gt_itab-seq    = '5'.
gt_itab-sign   = versn-sign.
gt_itab-opt    = versn-option.
gt_itab-low    = versn-low.
gt_itab-high   = versn-high.
APPEND gt_itab.
CLEAR  gt_itab.

IF gt_itab[] IS NOT INITIAL.
CALL FUNCTION 'ZIP_SEL_FM' DESTINATION 'DBDCLNT100'
TABLES
gt_itab = gt_itab.
ENDIF.

*Start Process Chain if Updation is Okay.
CALL FUNCTION 'RSPC_CHAIN_START' DESTINATION 'DBDCLNT100'
EXPORTING
i_chain = chain_id-low
IMPORTING
e_logid = e_logid.
WRITE:/ e_logid, sy-subrc.

 

*To get the Process chain log.
CALL FUNCTION 'RSPC_API_CHAIN_GET_LOG' DESTINATION 'DBDCLNT100'
EXPORTING
I_CHAIN = chain_id-low
I_LOGID = e_logid
TABLES
E_T_LOG = E_T_LOG.
SKIP.
LOOP AT e_t_log.
WRITE:/ sy-uline+1(107).
WRITE:/ '|', E_T_LOG-MSGV1, '|',
E_T_LOG-MSGV4, '|'.
ENDLOOP.
WRITE:/ sy-uline+1(107).

 

*&---------------------------------------------------------------------*
*&      Form  VALUE_REQUEST
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*

*to get the Process chains with F4 functionality in the selection screen
FORM value_request .
CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'
EXPORTING
retfield        = 'RSPCCHAIN-CHAIN_ID'
value_org       = 'S'
TABLES
value_tab       = l_itab
return_tab      = t_results
EXCEPTIONS
parameter_error = 1
no_values_found = 2
OTHERS          = 3.
IF t_results[] IS NOT INITIAL.
READ TABLE t_results INTO wa_results INDEX 1.
chain_id-low = wa_results-fieldval.
ENDIF.
ENDFORM.                    " VALUE_REQUEST

We should Create a Remote Function module in order to save the user parameters in the BI System from R/3.

FM: ZIP_SEL_FM

FUNCTION ZIP_SEL_FM.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  TABLES
*"      GT_ITAB STRUCTURE  ZIP_SEL_TAB3
*"----------------------------------------------------------------------

 

TABLES: zip_sel_tab3.

 

SELECT * FROM zip_sel_tab3.

 

ENDSELECT.

 

MODIFY zip_sel_tab3 FROM TABLE gt_itab.

 

ENDFUNCTION.

Here ZIP_SEL_TAB3 is the Generic Table which is created from SM30(T-code). The user selections from R/3 are updated to this table.

This table is also used for the DTP restrictions within the ProcessChains.

The example code for the DTP restrictions::

TABLES: ZIP_SEL_TAB3. (This is in the Global declarations)

The code mentioned below is for picking up the User selections from the Generic table to the DTP selections.

data: l_idx like sy-tabix.

  read table l_t_range with key
fieldname = 'COSTCENTER'.
l_idx = sy-tabix.
data: l_DTP type RSBKDTPNM.
data: l_CHAIN type RSPC_CHAIN.
data: l_seltab type ZIP_SEL_TAB3.

 

l_DTP = I_R_REQUEST->GET_DTP( ).
SELECT single ACTUALCHAIN from RSBKDTPSTAT into l_chain
where DTP = l_DTP.

 

Select * from ZIP_SEL_TAB3 into l_seltab
where RSPC_CHAIN = l_chain
and   FNAM     = 'KOSTL'.

 

l_t_range-fieldname = 'COSTCENTER'.
l_t_range-SIGN = l_seltab-sign.
l_t_range-OPTION = l_seltab-opt.
l_t_range-LOW = l_seltab-LOW.
l_t_range-HIGH = l_seltab-HIGH.
if l_idx <> 0.
modify l_t_range index l_idx.
else.
append l_t_range.
endif.
ENDSELECT.
p_subrc = 0.

Based on the above steps and ABAP code the peculiar requirement from clients can be achieved.

Kindly provide your valuable feedback.

Other documents from my knowledge base:

 

A step by step guide for Invoicing Extraction (FI-CA)

Scheduling FI-CA related Delta Extraction

Triggering Process chain after completion of ECC Delta Extraction Job

Golden rules/Tips & Tricks for BW/BI Designing

Design Solutions for BI/BOBJ Reporting (Part 1)