Skip to Content

This blog comes to give you my return on experience on retraction. This section covers an original approach like a cookbook for retracting data to source system. During a project I have been asked to do the review on a delivered BI IP solution.

The team was facing an issue to retract data from project planning and investment management application more accurately the wbs elements to ECC.

After having read the good document titled Data Retraction in Business Intelligence Planning from Marc Bernard (a special thanks to Marc Bernard for the input he gave me) and Detlef Wassmuth we can understand many important and clearly explained points:

  • No new standard retractors are shipped

That means here that no new development from SAP will be realized in order to create standard functions or programs to retract data from BI IP.

  • You can use retractors that work according to the pull principle without changing them
  • You cannot directly use retractors that work according to the push principle

Here we clearly understand that retractors are working according to two principles : the push and the pull mode. For the pull mode we use the add-in in R3 and this works for CO-PA and PSM-FM-BCS for this kind of retractor no change is necessary, by the way there is a good How to on this topic make a search on the SDN web site or better here is the link How to Connect Between CO-PA and SAP BW for Data Retraction.

For the push mode we use functions in BW-BPS for the areas of CO-OM-CCA, PS/IM, FI-SL and EC-PCA.

  • You can use retractors that work according to the push principle by configuring them in BW-BPS

The retractors that works according to the push principle can be used if we customize BW-BPS. Meanning that we have to create planning area functions etc…

Having all these elements in mind I started to dig in the area of Project System standard retractor. What I found after configuring BW-BPS for WBS retraction is that implementing the standard require the use BW-BPS. Therefore it seems to me a lot of work to deal and maintain two areas one in BW for BI-IP application and the other one in BW-BPS. So I started to think about an intelligent solution and a way to reduce maintaince effort for the customer.

The data retraction definition we can find on help.sap.com is the following :

“Program that returns data from the SAP Business Information Warehouse (SAP BW) back to a source system.

Retractors enable SAP BW data to be used in the source system. Whereas extractors are essential for data warehouse solutions, the operational applications in the source system can choose whether a retractor is offered or not. Retractors can be applied either in the source system (“pull” principle) or in SAP BW (“push” principle).

Example

Actual data that has been extracted from the operational system is used as reference data for generating new planning data in an application based on SAP BW. A retractor can be used to transfer this planning data back to the source system as target values for the planned period. “

After analysing the definition we could guess that in fact we can build our own generic program to retract back data to the source system.

Knowing how to read data from an infoprovider with a function I decided to go for a generic retractor. The function module RSDRI_INFOPROV_READ is really useful, it allows you to read data from different types of infoproviders like infocubes, DSO, multi-providers. Note that you can note read infosets and open hubs strucutures. I just had to read the coding of the standard function used in BW-BPS for retraction in my case this was the function UPB_CI_PUSHBACK_PR (you can find documention for this function in the menu). I discovered that the standard structure and function used for returning data to ECC were AISEM_PR_VAL (structure) and AIS1_PUSHBACK_PR_VAL (the function). Then the job was easy I just had to combine all these ingredients to make a good cook.

Please find below the coding of the program realized to retract data to ECC.

*&---------------------------------------------------------------------*
*& Report ZWBS_RETRACTION_TO_ECC
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zwbs_retraction_to_ecc.
.

TYPE-POOLS: rs, rsdrc.

TYPES:
BEGIN OF gt_s_data,
0wbs_elemt TYPE /bi0/oiwbs_elemt,
0version TYPE /bi0/oiversion,
0fiscyear TYPE /bi0/oifiscyear,
metype(6) TYPE c,
0vtype TYPE /bi0/oivtype,
0amount TYPE bapicurr_d,
coa_curr(5) TYPE c,
obj_curr(5) TYPE c,
0currency TYPE /bi0/oicurrency,
0curtype TYPE /bi0/oicurtype,
0fiscvarnt TYPE /bi0/oifiscvarnt,
zip_c003 TYPE /bic/oizip_c003,
END OF gt_s_data.

TYPES: BEGIN OF gtype_s_values_pr,
0wbs_elemt TYPE /bi0/oiwbs_elemt,
0version TYPE /bi0/oiversion,
0fiscyear TYPE /bi0/oifiscyear,
metype(6) TYPE n,
0vtype TYPE /bi0/oivtype,
0amount TYPE bapicurr_d,
coa_curr(5) TYPE c,
obj_curr(5) TYPE c,
0currency TYPE /bi0/oicurrency,
0curtype TYPE /bi0/oicurtype,
0fiscvarnt TYPE /bi0/oifiscvarnt,
END OF gtype_s_values_pr.

DATA : l_data TYPE gt_s_data.
DATA : it_final TYPE STANDARD TABLE OF gtype_s_values_pr
WITH HEADER LINE.

DATA:
g_s_data TYPE gt_s_data,

* G_T_DATA = an internal table that can hold the result set

g_t_data TYPE STANDARD TABLE OF gt_s_data
WITH DEFAULT KEY INITIAL SIZE 10,

* G_S_SFC = description of a characteristic or navigational attribute
* that is requested by a query
g_s_sfc TYPE rsdri_s_sfc,
g_th_sfc TYPE rsdri_th_sfc,

* G_S_SFK = description of a key figure that is requested by a query
g_s_sfk TYPE rsdri_s_sfk,
g_th_sfk TYPE rsdri_th_sfk,

* G_S_RANGE = description of a restriction on a characteristic or
* navigational attribute
g_s_range TYPE rsdri_s_range,
g_t_range TYPE rsdri_t_range.

* For the following characteristics / navigational attributes values
* should be returned:
* 0VTYPE, 0CURRENCY, 0FISCVARNT, 0FISCYEAR, 0VERSION, 0WBS_ELEMT

CLEAR g_th_sfc.

* 0VTYPE
CLEAR g_s_sfc.
* --- name of characteristic
g_s_sfc-chanm = '0VTYPE'.
* --- name of corresponding column in G_T_DATA
g_s_sfc-chaalias = '0VTYPE'.
* --- no ORDER-BY
g_s_sfc-orderby = 0.
* --- include into list of characteristics
INSERT g_s_sfc INTO TABLE g_th_sfc.

* 0CURRENCY
CLEAR g_s_sfc.
g_s_sfc-chanm = '0CURRENCY'.
g_s_sfc-chaalias = '0CURRENCY'.
g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

* 0CURTYPE
CLEAR g_s_sfc.
g_s_sfc-chanm = '0CURTYPE'.
g_s_sfc-chaalias = '0CURTYPE'.
g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

*'0FISCVARNT'
CLEAR g_s_sfc.
g_s_sfc-chanm = '0FISCVARNT'.
g_s_sfc-chaalias = '0FISCVARNT'.
g_s_sfc-orderby = 0.

INSERT g_s_sfc INTO TABLE g_th_sfc.

* 0FISCYEAR
CLEAR g_s_sfc.

g_s_sfc-chanm = '0FISCYEAR'.
g_s_sfc-chaalias = '0FISCYEAR'.
*g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

* ZIP_C003
CLEAR g_s_sfc.

g_s_sfc-chanm = 'ZIP_C003'.
g_s_sfc-chaalias = 'ZIP_C003'.
*g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

*0VERSION
CLEARg_s_sfc.
g_s_sfc-chanm = '0VERSION'.
g_s_sfc-chaalias = '0VERSION'.
g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

*0WBS_ELEMT
CLEAR g_s_sfc.
g_s_sfc-chanm = '0WBS_ELEMT'.
g_s_sfc-chaalias = '0WBS_ELEMT'.
g_s_sfc-orderby = 0.
INSERT g_s_sfc INTO TABLE g_th_sfc.

* For the following Key Figure should be returned : 0AMOUNT

CLEAR g_th_sfk.

* 0AMOUNT
CLEAR g_s_sfk.
* --- name of key figure
g_s_sfk-kyfnm = '0AMOUNT'.
* --- name of corresponding column in G_T_DATA
g_s_sfk-kyfalias = '0AMOUNT'.
* --- aggregation
g_s_sfk-aggr = 'SUM'.
* --- include into list of key figures
INSERT g_s_sfk INTO TABLE g_th_sfk.

*DATA P_WBS TYPE /BI0/PWBS_ELEMT.
*DATA : P_WBS TYPE /BI0/OIWBS_ELEMT.
PARAMETERS : p_packsi TYPE i DEFAULT 1000,
p_fiscye TYPE /bi0/oifiscyear OBLIGATORY.
*SELECT-OPTIONS S_WBS FOR P_WBS-WBS_ELEMT.

* These are the restrictions samples that could be applied at
*the moment of the retraction:
* 0FISCYEAR BETWEEN 190001 AND 202506
* For test purposes use the selection cirteria
* When going to production comment the 2 criterias
* * 0WBS_ELEMT BETWEEN 190001 AND 202506
* and
* '0FISCYEAR' = 2009

CLEAR g_t_range.

** 0WBS_ELEMT BETWEEN 190001 AND 202506
*CLEAR g_s_range.
** --- name of the characteristic
*g_s_range-chanm = '0WBS_ELEMT'.
** --- including or excluding condition ?
*g_s_range-sign = rs_c_range_sign-including.
** --- comparison operator
*g_s_range-compop = rs_c_range_opt-between.
** --- low value
*g_s_range-low = P_WBS.
** --- high value
*g_s_range-high = 'P_WBS'.
** --- include into list of restrictions
*APPEND g_s_range TO g_t_range.
**
**
* '0FISCYEAR' = 2009
CLEAR g_s_range.
g_s_range-chanm = '0FISCYEAR'.
g_s_range-sign = rs_c_range_sign-including.
g_s_range-compop = rs_c_range_opt-equal.
g_s_range-low = p_fiscye.
APPEND g_s_range TO g_t_range.

* Please let this
* '0CURRENCY' = 2009
CLEAR g_s_range.
g_s_range-chanm = '0CURRENCY'.
g_s_range-sign = rs_c_range_sign-including.
g_s_range-compop = rs_c_range_opt-equal.
g_s_range-low = 'EUR'.
APPEND g_s_range TO g_t_range.

DATA: g_end_of_data TYPE rs_bool,
g_first_call TYPE rs_bool.

g_end_of_data = rs_c_false.

g_first_call = rs_c_true.

WHILE g_end_of_data = rs_c_false.

CALL FUNCTION 'RSDRI_INFOPROV_READ'
EXPORTING
i_infoprov = 'ZIP_004'
i_th_sfc = g_th_sfc
i_th_sfk = g_th_sfk
i_t_range = g_t_range
i_reference_date = sy-datum
i_save_in_table = rs_c_false
i_save_in_file = rs_c_false
i_packagesize = p_packsi
i_authority_check = rsdrc_c_authchk-read
IMPORTING
e_t_data = g_t_data
e_end_of_data = g_end_of_data
CHANGING
c_first_call = g_first_call
EXCEPTIONS
illegal_input = 1
illegal_input_sfc = 2
illegal_input_sfk = 3
illegal_input_range = 4
illegal_input_tablesel = 5
no_authorization = 6
illegal_download = 8
illegal_tablename = 9
OTHERS = 11.

IF sy-subrc <> 0.

MESSAGE e398(00) WITH 'Problem on FM RSDRI_INFOPROV_READ - Subrc = '
sy-subrc
' - TREATMENT STOPED'.

ENDIF.

PERFORM wbs_retraction.

CLEAR : g_t_data.
FREE : g_t_data.
CLEAR : l_data.
FREE : l_data.

ENDWHILE.

*---------------------------------------------------------------------*
* FORM wbs_retraction
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
FORM wbs_retraction.

DATA: l_ecc_dest TYPE rslogsysdest-destination.

CONSTANTS: ecc_dest(10) TYPE c VALUE 'BWPCLNT100'.

DATA : id_rfcdest TYPE rfcdest,
id_sgtext TYPE upb_ci_itemtext.

DATA: lt_return
TYPE STANDARD TABLE OF bapiret2
WITH HEADER LINE.

DATA : ld_msg_text(200) TYPE c.

LOOP AT g_t_data INTO l_data.

IF l_data-zip_c003 NE l_data-0fiscyear AND l_data-0amount = 0.
CONTINUE.
ELSE.

MOVE-CORRESPONDING l_data TO it_final.

IF sy-subrc = 0.

APPEND it_final.

ENDIF.

SELECT SINGLE destination INTO l_ecc_dest
FROM rslogsysdest
WHERE destination = ecc_dest.
* WHERE logsys = ecc_dest.

IF sy-subrc = 0.

*-----------------------------------------------------------------
* Push back plan values on appropriation requests.
*-----------------------------------------------------------------
* destination id_rfcdest

*** RFC-Call: Write Values.
CALL FUNCTION 'AIS1_PUSHBACK_PR_VAL'
DESTINATION l_ecc_dest

EXPORTING
id_line_item_text = id_sgtext
id_post_as_budget = 'X'
* ID_POST_AS_SUP_RET = ' '
* ID_TEST_RUN = ' '
* ID_DEBUG_FLG = ' '
TABLES
it_ranges = g_t_range[]
ct_values_pr = it_final[]
et_return = lt_return[]
EXCEPTIONS
communication_failure = 1 MESSAGE ld_msg_text
system_failure = 2 MESSAGE ld_msg_text
error_occured = 10
OTHERS = 11.

LOOP AT lt_return.

WRITE : 'Message type' , lt_return-type , 'Message ID' , lt_return-id , 'Message number' , lt_return-number.
WRITE :/ lt_return-message , lt_return-message_v1.
WRITE :/ lt_return-message_v2 , lt_return-message_v3.
SKIP 2.

IF lt_return-type = 'E' OR lt_return-type = 'X' OR lt_return-type = 'A'.
MESSAGE e002(zretraction) WITH 'problem in the retraction please check the spool'.
ENDIF.
ENDLOOP.

ENDIF.
ENDIF.
ENDLOOP.

ENDFORM. "retraction

Before executing the program you have to close the yellow request that is used for ready input queries or workbooks.

Here is a sample program you can use to do this step.

PARAMETERS : lic_cube LIKE rsapoadm-infocube..

CALL FUNCTION 'RSAPO_CLOSE_TRANS_REQUEST'
  EXPORTING
    I_INFOCUBE               = lic_cube
EXCEPTIONS
   ILLEGAL_INPUT            = 1
   REQUEST_NOT_CLOSED       = 2
   OTHERS                   = 3.

Create a variant for this program with your real-time infocube.

Finally create a process chain that runs first the program to close the request and then that executes the program for retraction.

Enjoy your cooking !

sources :

sdn.sap.com

help.sap.com

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Dirk Herzog
    Your approach is truly original. But why did you do this so complicated? You reinvented everything from the scratch (which can lead to wrong values if you have the cube partially compressed which often happens in planning cubes).
    How to solve this problem?
    1. The easiest way would be to use the old BPS exits. There is a class CL_RSPLFC_BPS_EXITS that lets you use the old BPS function modules.
    2. Create your own exit functions by implementing the interface IF_RSPLFA_SRVTYPE_IMP_EXEC or IF_RSPLFA_SRVTYPE_IMP_EXEC_REF. There you can call the BApIs BAPI_COSTACTPLN_* directly depending on your requirements (which you might be doing in your ECC function module anyway).

    I would do everything I can to keep me from reading the data myself. There are so many changes during the lifetime that you will be constantly changing this programm – something you can often prevent by using the planning exits and letting SAP do the reading.

    If you have questions about implementing exits in IP I humbly point you to my blog I wrote today.

    Best regards
    Dirk

    (0) 
    1. Boujema Bouhazama Post author
      hi Dirk
      I thank you so much for your input, and your good blog I expect we also could get a french version of your next version.
      I fully agree that so much detail could be complicated. I also prevent myself going for specific as much as possible and go for standard when SAP provides it. But sometimes conditions make you having original approaches combining lots of factors. In fact I had to implement such an original approach because I had not much time (was sent for a few days in the south of Europe and had 2/3 days to implement the solution) and wanted the customer to avoid maintaining two areas one in BI IP the other in BPS (also because the lack of knowledge from the customer in this area but they had someone who has good abap knowledge). In fact it is possible and would have been easier to do it directly in BPS with all the standard functionnality as we can see it in the documentation of Marc Bernard.
      I know about the class you mention and honestly did not though about using them at that time. If I have the opportunity I will update this approach and try to implement this the way you advice.
      For the moment what I will do is to post the coding in the BI wiki (suggestion of Marc) and do a link with this blog which is my first one.
      Once again thanks for the input.

      With the kindest regards
      Boujema

      (0) 
      1. Dirk Herzog
        Maybe I sounded a bit too harsh. What I originally meant was that you don’t need another area in BPS because you can use the BPS function modules in IP too. But you’re right if there is a lot of ABAP know how it is a nice work around.

        Best regards
        Dirk

        (0) 

Leave a Reply