Automated DM Execution Based on Dynamic Selections
One of the biggest short coming when it comes to loading transaction data in BPC is lack of variable based selection. This document is an attempt to address this issue by automating the Data Manager execution using values as desired during run-time. There are certain discussions around the same but I thought of sharing a working solution that could be helpful for others as well.
The scope of this document is restricted to executing a Data Manager package based on a simple business scenario.
– SAP BPC 10.0 on SAP NW BW(7.31)
– EPM Addin – Version 10 SP21 .NET4
You are required to automate the transaction data load from BW to BPC. The planning cycle is monthly. Data Manager package must execute for current period without any manual intervention every month.
There are various ways to execute a Data Manager Package. Some of them are:
- Manual Execution
- Using VBA by generating local response file – Executing Data Manager Package Silently (No Pro… | SCN
- Using program UJD_TEST_PACKAGE by creating answer prompt text file – Steps to Automate Datamanager package in BPC NW 7.5 and few tips to help you through !
However, none of these approaches can enable a variable based execution of a DMP. We are considering a case where we have to load data every month for the current period without manual intervention. In BW data loads, we can select variables or write selection routines in DTP/InfoPackages to do that. BPC does not provide variable based selections for data load out-of-the-box. BPC also lacks in terms of selection options like “not equal to” or wild card.
What we will be trying is to write a program with a selection screen and the program itself will generate the answer prompt which can then be passed along with other values are required in the program UJD_TEST_PACKAGE.
Here is how the selection screen would look like:
For the purpose of explanation – Fiscal Year/Period has been included in the selection screen. It can be used behind the scenes as well. Just to make the program a little more flexible, an option for transformation file selection is also added. This will help to test different transformation files if required.
We will be loading data for current period in our planning model. Here is what the program is doing:
The following declaration will be required to generate answer prompt. This class contains horizontal tab separation as we have declared below.
CLASS cl_abap_char_utilities DEFINITION LOAD.
CONSTANTS: c_tab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab.
SELECTION-SCREEN BEGIN OF BLOCK blck1.
PARAMETERS: bpc_env TYPE uja_appl–appset_id OBLIGATORY LOWER CASE,
bpc_mod TYPE uja_appl–application_id OBLIGATORY LOWER CASE,
bw_iprov TYPE rsinfoprov OBLIGATORY LOWER CASE,
bpc_pak TYPE uj_package_id OBLIGATORY LOWER CASE,
bpc_trfl TYPE uj_string OBLIGATORY LOWER CASE,
bpc_tid TYPE /bi0/oifiscper OBLIGATORY.“Selection field
SELECTION-SCREEN END OF BLOCK blck1.
We are initializing the bpc_tid which is the variable for fiscal period with the current period.
CONCATENATE sy–datum+0(4) ‘0’ sy–datum+4(2) INTO bpc_tid.
Generating answer prompt:
The answer prompt as per thread mentioned earlier has to look like below:
We will define string variables for each of these lines and later concatenate them as a single answer prompt.
It would be better to ensure that only required fields are selected in the DMP so that it is easier to generate string.
Note – Low value for 0FISCPER – It is bpc_tid.
CONCATENATE ‘%InforProvide% ‘ c_tab bw_iprov cl_abap_char_utilities=>cr_lf INTO ip_sel .
CONCATENATE bpc_sel ‘<Selection Type=”Selection”><Attribute><ID>0FISCPER</ID><Operator>1</Operator><LowValue>’ bpc_tid INTO sel_fld.
CONCATENATE sel_fld ‘</LowValue><HighValue /></Attribute><Attribute><ID>0VERSION</ID><Operator>1</Operator><LowValue>001</LowValue><HighValue /></Attribute></Selection>’ INTO sel_fld.
CONCATENATE sel_fld ‘<Selection Type=”FieldList”><FieldID>0ACCT_TYPE</FieldID><FieldID>0CHRT_ACCTS</FieldID><FieldID>0COMP_CODE</FieldID><FieldID>0COSTCENTER</FieldID>’ INTO sel_fld.
CONCATENATE sel_fld ‘<FieldID>0CO_AREA</FieldID><FieldID>0DOC_CURRCY</FieldID><FieldID>0FISCPER</FieldID><FieldID>0FISCVARNT</FieldID><FieldID>0GL_ACCOUNT</FieldID>’ INTO sel_fld.
CONCATENATE sel_fld ‘<FieldID>0PCOMPANY</FieldID><FieldID>0PROFIT_CTR</FieldID><FieldID>0VERSION</FieldID></Selection></Selections>’ cl_abap_char_utilities=>cr_lf INTO sel_fld.
CONCATENATE ‘%TRANSFORMATION%’ c_tab ‘\ROOT\WEBFOLDERS\ENVIRONMENT\MODEL\DATAMANAGER\TRANSFORMATIONFILES\’ bpc_trfl ‘.XLS’ cl_abap_char_utilities=>cr_lf INTO sel_trfl.
CONCATENATE ‘%TARGETMODE%’ c_tab ‘1’ cl_abap_char_utilities=>cr_lf INTO sel_tm.
CONCATENATE ‘%RUNLOGIC%’ c_tab ‘0’ cl_abap_char_utilities=>cr_lf INTO sel_rl.
CONCATENATE ‘%CHECKLCK%’ c_tab ‘0’ INTO sel_cl.
CONCATENATE ip_sel sel_fld sel_trfl sel_tm sel_rl sel_cl INTO aprompt.
Notice that each variable is separated by c_tab as we have declared earlier. Also, at the end of each line of answer prompt, we have cl_abap_char_utilities=>cr_lf. This is required as this is how lines are separated by SAP program.
Now that the answer prompt is ready, we have to feed the selection screen of UJD_TEST_PACKAGE.
WITH appl = bpc_mod
WITH appset = bpc_env
WITH group = bpc_group
WITH if_file = uj00_cs_bool–no
WITH if_msg = uj00_cs_bool–no
WITH if_sync = uj00_cs_bool–yes
WITH package = bpc_pak
WITH prompt = aprompt
WITH schedule = ‘<SCHEDULING><IMMEDIATE>Y</IMMEDIATE><STATE>0</STATE><PERIOD>N</PERIOD></SCHEDULING>’
WITH team = bpc_team
WITH user = sy–uname
EXPORTING LIST TO MEMORY AND RETURN.
You can incorporate more flexibility and options based on your ABAP expertise and knowledge. When you execute this program, it will trigger the DM package and all the selections will be taken from the selection screen.
I have attached the sample code. You will need to modify it based on your source InfoCube/DSO, selection parameters and anywhere else specific to your case.
Now you can schedule your DMP to load for the current period at the time of data load without any manual intervention.