- SAP Managed Tags:
1. Background Information
This How-To guide provides a solution to hardcode dimension members in the default “Copy” data package provided in SAP BPC. The non-essential dimension members will get hardcoded in the data package itself and the users will not be prompted to select the same when they run the data package. This will ensure that the data is not copied to a wrong data range.
2. Business Scenario
The user wants to copy data from 1 Category to another (say Version 1 to Version 2) or from 1 datasource (audittrail) to another for a particular product, cost center etc.
In such cases, the users would often like to select only the profit center & the time period.
The default ‘Copy’ package provided by SAP BPC, however, expects the users to select the dimension members for all the dimensions.
In the above screenshot, the user is expected to certain dimensions like Audit Trail, Rptcurrency etc. which if wrongly selected might disrupt the Data range saved into the server.
Users often request the ability to select the necessary dimensions only (with which he/she is generally concerned) and not all the dimensions. And rightly so, as quite often too many member selections might confuse the users and they might end up copying the data to a wrong data range.
3. Business Case Study:
The user wants to copy the data from Version (Category Dimension) “M3” to “M0” and from Audittrail “Input” to “Calc”. However, he/she would like to do so for account dimension member “Sales Quantity” only & only when the Plant dimension member is “NO_PLANT”. The user would like to specify via a prompt box, the profit centers and the time period for which he/she would like to run the package.
The user would be copying the above-mentioned data range quite frequently during the budgeting cycle. Considering the fact that this is a frequently run data package, the user doesn’t want to select the static dimension members (Sales Quantity & NO_PLANT in this case) for these dimensions (ACCOUNT & PLANT respectively) again and again, and would prefer if the same are hardcoded. (This will also help in ensuring that the data values are not erroneously posted to the wrong data range due to an incorrect selection.)
The user has suggested that he/she should be allowed to select only the profit center & the time period from the prompt in the data package. The other members for the other dimensions to be coded within the data package.
4. Step-by-step procedure
- Create a copy of the Copy Package to ensure the existing one is left untouched.
- Modify the newly copied package
- Rename the package as per your choice and then click on modify script
- Click on Advanced
- Remove the existing code in the right hand window and paste the following code (The code is pasted after the screenshot):
- Code to be entered in the Code Window:
'PROMPT(RADIOBUTTON,%CLEARDATA%,"Select the method for copying the data from the source to destination database",0,{"Merge data values (Copies specified records, leaving all remaining records in the destination intact)","Replace && clear data values(Copies specified records, and clears the data values for any records that mirror each entity/category/time/datasource combination defined in the source)"},{"0","1"})
'PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select whether to run default logic for stored values after copying",1,{"Yes","No"},{"1","0"})
'PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when importing data.",1,{"Yes, check for work status settings before importing","No, do not check work status settings"},{"1","0"})
'PROMPT(COPYMOVEINPUT,%SELECTION%,%TOSELECTION%,"Select the members to COPY and where to","%TIME_DIM%,%ENTITY_DIM%,%CATEGORY_DIM%")
PROMPT(SELECTINPUT,%SELECTION%,,,"%TIME_DIM%,%ENTITY_DIM%")
INFO(%SUFFIX1%,DIMENSION:AUDITTRAIL|INPUT|)
INFO(%SUFFIX2%,DIMENSION:AUDITTRAIL|CALC|)
INFO(%SUFFIX3%,DIMENSION:PLANT|NO_PLANT|)
INFO(%SUFFIX4%,DIMENSION:ACCOUNT_P|SALES_QTY|)
INFO(%SUFFIX5%,DIMENSION:VERSION|M3|)
INFO(%SUFFIX6%,DIMENSION:VERSION|M0|)
INFO(%TEMPNO1%,%INCREASENO%)
INFO(%ACTNO%,%INCREASENO%)
TASK(/CPMB/CM_CONVERT,OUTPUTNO,%TEMPNO1%)
TASK(/CPMB/CM_CONVERT,ACT_FILE_NO,%ACTNO%)
TASK(/CPMB/CM_CONVERT,SAPPSET,%APPSET%)
TASK(/CPMB/CM_CONVERT,SAPP,%APP%)
TASK(/CPMB/CM_CONVERT,SELECTION,%SELECTION%%SUFFIX1%%SUFFIX3%%SUFFIX4%%SUFFIX5%)
TASK(/CPMB/CM_CONVERT,TOSELECTION,%SELECTION%%SUFFIX2%%SUFFIX3%%SUFFIX4%%SUFFIX6%)
TASK(/CPMB/CM_CONVERT,CLEARDATA,%CLEARDATA%)
TASK(/CPMB/CLEAR_SOURCE_CUBE,CHECKLCK,%CHECKLCK%)
TASK(/CPMB/CLEAR_SOURCE_CUBE,SELECTION,%SELECTION%%SUFFIX2%%SUFFIX3%%SUFFIX4%%SUFFIX6%)
TASK(/CPMB/APPEND_LOAD,INPUTNO,%TEMPNO1%)
TASK(/CPMB/APPEND_LOAD,ACT_FILE_NO,%ACTNO%)
‘TASK(/CPMB/APPEND_LOAD,RUNLOGIC,%RUNLOGIC%)
‘TASK(/CPMB/APPEND_LOAD,CHECKLCK,%CHECKLCK%)
‘TASK(/CPMB/APPEND_LOAD,CLEARDATA,%CLEARDATA%)
TASK(/CPMB/APPEND_LOAD,RUNLOGIC,1)
TASK(/CPMB/APPEND_LOAD,CHECKLCK,1)
TASK(/CPMB/APPEND_LOAD,CLEARDATA,0)
Explanation of the Code:
- The prompts have been commented out using ‘. This will ensure that the user is not prompted for these statements. The value highlighted in green will determine whether the statement (running default logic) will be executed or not, without prompting the user, by making suitable modifications in the TASK statement.
For example, in
'PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select whether to run default logic for stored values after copying",1,{"Yes","No"},{"1","0"})
If default logic is to be run without the user being specifically asked regarding the same, instead of using
TASK(/CPMB/APPEND_LOAD,RUNLOGIC,%RUNLOGIC%),
we use ,
TASK(/CPMB/APPEND_LOAD,RUNLOGIC,1)
- 'PROMPT(COPYMOVEINPUT,%SELECTION%,%TOSELECTION%,"Select the members to COPY and where to","%TIME_DIM%,%ENTITY_DIM%,%CATEGORY_DIM%") – The Copy From & Copy To screen is commented since the destination is the same as the source. The next PROMPT statement takes care of that requirement
- The user will be prompted for the Profit Center & Time. The same has been assigned the variable %SELECTION%
- The INFO Statements provides the variable names for the dimension members to be hardcoded. The syntax for the INFO statement is
INFO(%SUFFIX3%,DIMENSION:PLANT|NO_PLANT|)
INFO(%ANY_VARIABLE_NAME%,DIMENSION:DIMENSION_NAME|DIMENSION_MEMBER|)
- TASK (CM_CONCERT) basically denotes what is to be sent where:
TASK(/CPMB/CM_CONVERT,SELECTION,%SELECTION%%SUFFIX1%%SUFFIX3%%SUFFIX4%%SUFFIX5%)
TASK(/CPMB/CM_CONVERT,TOSELECTION,%SELECTION%%SUFFIX2%%SUFFIX3%%SUFFIX4%%SUFFIX6%)
=
TASK(/CPMB/CLEAR_SOURCE_CUBE,SELECTION,%SELECTION%%SUFFIX2%%SUFFIX3%%SUFFIX4%%SUFFIX6%)
- The /CPMB/CM_CONVERT and /CPMB/CLEAR_SOURCE_CUBE statements should have same info variables, because the destination (i.e the TOSELECTION) range will be modified.
Tabulating the above:
SELECTION | TOSELECTION | From | To |
%SELECTION% | %SELECTION% | PC & Time selected | PC & Time selected |
%SUFFIX1% | %SUFFIX2% | INPUT | CALC |
%SUFFIX3% | %SUFFIX3% | NO_PLANT | NO_PLANT |
%SUFFIX4% | %SUFFIX4% | SALES_QTY | SALES_QTY |
%SUFFIX5% | %SUFFIX6% | M3 | M0 |
- Click on OK > Save > Save > Refresh & Close.
- Click on Run Package and now run the package
Instead of the complicated FROM & TO selection options, the user is greeted with a simple drop down for Profit Center and Time.
The user only needs to select the dimension members for profit center and time, click on Next and then run the package.
The data will be copied as:
Dimension | From | To |
Profit Center | PC selected | Same PC |
Audittrail | INPUT | CALC |
Plant | NO_PLANT | NO_PLANT |
Account | SALES_QTY | SALES_QTY |
Version | M3 | M0 |
Time | Time Selected | Same Time |
All other Dimensions | All | Same |
5. Points to Remember:
- This guide has been written for BPC 10 NW Version. It hasn’t been tested for the MS version or the prior versions of BPC NW. I am assuming it will work equally well for the prior versions of BPC (7,7.5) NW
- Ideally, if a member of a dimension is going to be hard-coded, it shouldn’t feature as a part of the data manager selection prompt.
- Perform detailed testing of the data package before releasing to the users
- A similar exercise can be done for other standard packages provided by SAP like “Clear”, “Move” etc.
Hope this helps.
Special thanks to Vadim Kalinin who assisted me with this at the SCN Forums.
Regards,
Rajesh Balakrishnan
48 Comments
