Mapping and mapping task example with SAP BW BEx Query

1. 1Create an SAP BW BEx Query connection. Select SAP as the authentication method, select Application as the connection type, and enter the SAP connection properties.
The following image shows the configured SAP BW BEx Query connection:

2. 2Create a flat file connection to write data to the flat file.
3. 3Create an SAP BW BEx Query mapping in the Cloud Mapping Designer.
The following image shows the SAP BW BEx Query mapping:

4. 4Add a Source transformation, specify a name and description in the general properties.
5. 5On the Source tab, perform the following steps:
1. aIn the Connection field, select the configured SAP BW BEx Query connection to connect to SAP BW BEx Query.
2. bIn the Operation field, select query REP_20161212094150 as the operation to extract the financial planning report from SAP BW BEx Query.
The following image shows the queries from the list of read operations:

You can view the SAP BW BEx source and flat file target in the mapping.
3. cIn the Request Options section, configure the request message in the following XML format, specify the attributes in the message, and validate the message:
<!--1 or more repetitions:-->
<proc:REP_20161212094150_INPUT xmlns:proc="http://xml.schemas/infa/procedure/">
<!--Optional:-->
<REP_20161212094150>
<!--Optional:-->
<var_temp>$$Param_variable</var_temp>
</REP_20161212094150>
</proc:REP_20161212094150_INPUT>
4. The request message specifies the criteria to include the financial planning report query REP_20161212094150 and includes a parameter to filter a particular project plan within the financial planning report. You need to define the parameter $$Param_variable=PR00004446 entry in a parameter file, parameterfile_name.txt, and save the file in the following directory: <Secure Agent installation directory>/apps/Data_Integration_Server/data/userparameters. Later, enter the parameter file name on the Runtime Options and Advanced Options page of the Mapping Task wizard.
5. dIn the Advanced Properties section, set the tracing level to Normal, and use the default cache size of 1024.
6. 6On the Field Mapping tab, map all descendants from the incoming fields of the SAP BEx query response structure to the output fields.
The following image shows the response structure on the left pane and the output fields on the right pane:

7. 7Add a flat file Target transformation and specify the target object for the transformation. Perform the following steps:
1. aSelect a flat file connection for the target transformations to write data to the flat file.
2. bDraw a link to connect the previous transformation to the Target transformation.
3. cCreate a target object Bex_query.csv file to write data to the target.
4. dMap the fields that you want to link to the target fields.
8. 8Click Save and New Mapping Task to save and create a mapping task.
The following image shows the Mapping Task wizard with the applied mapping:

9. 9Under Advanced Options on the Runtime Options tab, specify the parameter file name parameterfile_name.txt in which you configured the filter parameter value for the request XML
10. 10When you save and run the mapping, the Secure Agent retrieves the specified project plan from SAP BW BEx Query and writes the data to the corresponding flat file.
The target flat file shows the following data extracted from the specified project plan after you run the task:
"Document_Number__CPD_FDOCNUM","Structure_Element__CPD_FPID","Profile__CPD_FPROFILE","Resource__CPD_FRES","Resource_Type__CPD_FRTYP","Client_0CLIENT",
"Calendar_Year_Month_0CALMONTH","UOM_Converted__CPD_FCUOM","UoM__CPD_FUOM","Margin_DB8FOYXR0W5E3AMR9959IG0ER","Quantity_DB8FOYXR0W5E3AMR9959IGJDF",
"Cost__Plan__DB8FOYXR0W5E3AMR9959IGD1V","Revenue__Plan__DB8FOYXR0W5E3AMR9959IG6QB","Cost__EAC__DB8FOYXR0W5E3AMR9959IGPOZ","Var_Temp"
,"800PR00004446",,"800ZMRS0MRS1429","800ZMRS0MRS",,,,,-12,0,1200,0,20000,"PR00004446"