In every reporting/data warehouse solution, a flat file load is an essential process. SAP BPC Embedded does offer this requirement. Recently I had a requirement where the flat file data content had to customized/modified and was looking out for a solution& was suprised to find an in-house solution available within SAP BPC-Embedded to fit in our requirement.
Since this approach is not discussed, I thought of blogging the same,so it may help out the community.
As the process of uploading the flat file into the AO, is widely discussed in the blogs, this article discusses about the customization of the flat file data into desired format and validation(&reviewing) of the data in the flat file content, before saving data into the aDSO, with the help of the pre-delivered BAdI.
This document will serve as an abstract on how to achieve the transformation (& validation), which is done on top of standard planning function(File Upload From AO). The planning function isn’t discussed in this document, as it widely discussed in SDN forums.
A financial sales planning scenario, where only a part of data(Product SubCategory & Sales ) comes as a flat file which needs to be uploaded into the aDSO for the planning process with all the other details as defined in the aggregation level.
Since the flat file, doesn’t have all the necessary information(Product Category information , BPC Category- Scenario, Year) and they would be filled during the upload process, as per the mappings maintained in a central dimension, file name and using the filter data, using the BAdI.
(An alternate possible scenario could be where a financial planning application data has only entity and the currency key could be possible derived through the mappings maintained in the Entity Dimension.)
The aggregation levels, planning filter and the corresponding BW Query/Workbook are already built as per the requirement.
AFO WorkBook and the variables that are being used.
|Parameter||Parameter Type||Type||Possible Scenario|
|I_SRVNM||Importing||RSPLF_SRVNM||Name of Planning Function which was triggered the BAdI|
|I_INFOPROV||Importing||RSINFOPROV||Aggregation Level on which the Planning Function was built|
|I_T_DATA_CHARSEL||Importing||RSPLF_T_CHARSEL||Contains the Planning Filter values which has been defined.
The filter could be used to pass on the filter parameters from the AFO report to the ABAP class, for dynamic derivations, etc. with the help of variables defined in the workbook.
In our scenario we utilize this parameter to populate the ZSCENRRIO value, passed from the work book.
|I_T_DATA_NODE_SEL||Importing||RSPLF_T_NODE||Node Information from active filter, defined in the planning function.|
|I_FILENAME||Importing||STRING||The relative file path along with the file name, could be used to deduce the values, in situations where the entity name is mentioned in the file name across the group, instead of individual workbook for each entity.
In our scenario, we will use the this to populate the ZYEAR value.
|C_TH_FILE||Changing||HASHED TABLE||File Data, in the structure defined as per the specified InfoObject mapping in the planning function.|
- Navigate to SE20 and give the enhancement spot as “BADI_RSPLFA_FILE_UPLOAD” and click on display. On the Enhancement Spot Details Tab, right click on BADI definition and click on Create BAdI Implementation as below.
- Key In the required details for the Enhancement implementation details/BAdI enhancement details&the implementing class details as required and proceed further.
- BADI_RSPLFA_FILE_UPLOAD is filter specific and it would be triggered only for the specified values of combinations we have provided in the enhancement screen as follows.
In the BAdI implementation screen, enter filter values specific to the Planning Function to which the customization needs to be made.
Activate the implementation.
- Navigate to the implementing class and to the inherited method IF_RSPLFA_FILE_UPLOAD~TRANSFORM_DATA, where the customizing needs to be done and code it with the required data and activate the class.
A sample snippet below, used is attached in the for reference.
- Open the AFO workbook, report and launch the planning function(clicking on the UploadData Button) and enter the file location.
To make it easy, please find the below from the screengrabs from debugger.
- The table contents from the flat file, as in the debugger(highlighted the empty fields)-Contents from the Flat File in C_TH_FILE parameter.
- File Name uploaded from the local desktop which is used to deduce the Period.
- Planning Sequence – Filter Selections defined captured in the parameter I_T_DATA_CHARSEL
- In the below example, the logic is written to populate the SEGMENT/Year/Scenario fields, which is empty from the source flat file, utilizing the available parameters. Contents as in the debugger, as below.Mappings populated into Product category, from mappings maintained table.
- The customized/derived fields could be seen here.
All the required validations/review could be done here, before saving.Upon saving, we get the saved as below.
method IF_RSPLFA_FILE_UPLOAD~TRANSFORM_DATA. TYPES: BEGIN OF ty_zsb_al07, ZPRO_CATG type /BIC/AZSB_PLAN2-/BIC/ZPRO_CATG, ZPRO_SCAT type /BIC/AZSB_PLAN2-/BIC/ZPRO_SCAT, ZSCENARIO type /BIC/AZSB_PLAN2-/BIC/ZSCENARIO, ZOR_SALES type /BIC/AZSB_PLAN2-/BIC/ZOR_SALES, ZYEAR type /BIC/AZSB_PLAN2-/BIC/ZYEAR, END OF ty_zsb_al07. DATA: wa_zsb_al07 type ty_zsb_al07, lt_zsb_al07 type HASHED TABLE OF ty_zsb_al07 with UNIQUE KEY ZPRO_CATG ZPRO_SCAT ZSCENARIO ZYEAR. DATA: I_S_DATA_CHARSEL like LINE OF I_T_DATA_CHARSEL. READ TABLE I_T_DATA_CHARSEL INTO I_S_DATA_CHARSEL WITH KEY IOBJNM = 'ZSCENARIO'. data lv_scat type /BIC/AZSB_PLAN2-/BIC/ZPRO_CATG. FIELD-SYMBOLS: <fs_tab> TYPE ANY TABLE. FIELD-SYMBOLS: <fs_str> TYPE any. FIELD-SYMBOLS: <fs_data> TYPE any. FIELD-SYMBOLS: <fs_data1> TYPE any. FIELD-SYMBOLS: <fs_data2> TYPE any. FIELD-SYMBOLS: <fs_data3> TYPE any. FIELD-SYMBOLS: <fs_data4> TYPE any. ASSIGN c_th_file TO <fs_tab>. LOOP AT <fs_tab> ASSIGNING <fs_str>. IF <fs_str> IS ASSIGNED. ASSIGN COMPONENT 'ZPRO_CATG' OF STRUCTURE <fs_str> TO <fs_data>. ASSIGN COMPONENT 'ZPRO_SCAT' OF STRUCTURE <fs_str> TO <fs_data1>. ASSIGN COMPONENT 'ZSCENARIO' OF STRUCTURE <fs_str> TO <fs_data2>. ASSIGN COMPONENT 'ZOR_SALES' OF STRUCTURE <fs_str> TO <fs_data3>. ASSIGN COMPONENT 'ZYEAR' OF STRUCTURE <fs_str> TO <fs_data4>. IF <fs_data> IS ASSIGNED AND <fs_data1> IS ASSIGNED AND <fs_data2> IS ASSIGNED AND <fs_data3> IS ASSIGNED. select /BIC/ZCONSMAP FROM /BIC/PZCONS INTO LV_SCAT WHERE /BIC/ZCONS = <fs_data1>. ENDSELECT. IF SY-SUBRC EQ 0. wa_zsb_al07-ZPRO_CATG = lv_scat. ELSE. wa_zsb_al07-ZPRO_CATG = <fs_data>. ENDIF. wa_zsb_al07-ZPRO_SCAT = <fs_data1>. wa_zsb_al07-ZSCENARIO = I_S_DATA_CHARSEL-LOW. wa_zsb_al07-ZOR_SALES = <fs_data3>. wa_zsb_al07-ZYEAR = I_FILENAME+51(4). COLLECT WA_ZSB_AL07 INTO LT_ZSB_AL07. UNASSIGN: <fs_data>,<fs_data1>,<fs_data3>,<fs_data2>,<fs_data4>. * clear LV_SCAT. ENDIF. ENDIF. ENDLOOP. * MOVE <fs_tab> TO C_TH_FILE. REFRESH C_TH_FILE. MOVE-CORRESPONDING LT_ZSB_AL07 TO C_TH_FILE. endmethod.
The above steps assists in the customization of the flat file data customization, wherein it helps in many scenarios. However, in some instances like, where there is need to convert the incoming data into desired format using the mappings, the above BAdI still doesn’t help, as the flat file data is first validated against the master data maintained(i.e the data in the should be maintained in the master data or should be either blank ) in the dimensions(InfoObjects) and then BAdI is triggered for transforming the data. Hopefully, in further releases SAP does provide a solution to these scenarios.