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:
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.
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.
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
