How to Optimize BPC Model in SAP BPC 11, version for SAP BW/4HANA
Updated: June 2022:
Cube-like aDSO is generated as basis provider (part provider) SAP BPC 11, version for SAP BW/4HANA (BPC 11) for each BPC model. Together with this change, process chain /CPMB/BUILD_INDEX and /CPMB/FULL_OPTMIZE are now obsolete, as result, there is no out-of-box way to optimize BPC model (aDSO) either from BPC web admin or data manager.
By reading note 2669682, you may notice it’s possible to leverage the new BW process type ASDOREM in BPC 11 to optimize the model. In this blog, I will briefly describe you how to create a data manager package to achieve this. In the end, user could trigger this package no different to what he can do in older BPC release.
- Create a BW Process Chain for BPC Model
a) Goto tcode RSPC in SAPGui, and create a new Process Chain.
b) Create a step with process type ADSOREM
In process type navigation pane, find ‘Clean up Old Requests in DataStore Objects(advanced)’ (tech name ADSOREM)
i. Create new variant for this step
ii. Input/select the tech name of the aDSO (of the BPC model) in first column ‘DSO(advanced)’
iii. Select ‘Activate Requests’ for column ‘Clean-up action’
iv. Input number of requests you want to left for compress( Select All but the X latest request) in column ‘Reqs’, optionally you can also input the days in next column as ‘Select all requests older than X days). In my example, I input 0 to clean-up(compress) all requests.
c) Create two steps with switch mode of aDSO
It’s only possible to run clean-up when aDSO in load mode, so we have to switch aDSO to load mode first and then switch it back to Plan mode after execution.
In process type navigation pane, find ‘Switch DataStore Object (advanced) to Load Mode’ and ‘Switch DataStore Object (advanced) to Plan Mode’, and create two steps with them. Please input the same aDSO tech name you used in step b).
d) Create a Start process
In process type navigation pane, find ‘Start Process’ and create a step with it.
e) Define dependency between steps
Now you should have four steps in the process chain, we need to define dependency with it.
We start with ‘Start’ step, right click it and select ‘Connect to’ -> ‘Switch to Load Mode’; then you link ‘Switch Load Mode’ to ‘Clean Up DSO Request’ then to ‘Switch to Plan Mode’ in the same manner.
Please note that you when link to ‘Switch to Plan Mode’ please make sure that you select the third option ‘always’ as you need to switch back to plan mode no matter the previous step succeeded or not.
When it’s done, you should have something similar like this in network view. (Too see network view, go menu Setting -> Network display)
f) Check and Activate
- Create a BPC Data Manager Package to trigger BW Process Chain
Now it’s time to create a Data Manager Package in EPM plug-in to let end user to trigger this process chain when needed.
a) Log-in EPM plug-in to the BPC model and go to Data Manager Ribbon
b) Go to ‘Organize Package List’ and add a new package
c) Select process chain ‘/CPMB/TRIGGER_BW_CHAIN’, this chain is delivered by BPC to trigger any BW process chain.
d) Modify Script if you don’t want end user to select the BW process chain (created in previous step)
i. Select ‘PROMPT’ on left and de-select the checkbox of column ‘Activate’. This is will hide the prompt for BW process chain name input.
ii. Select ‘TASK’ on the left and input the name of created BW process chain in step 1 into column ‘Value’.
iii. Save to leave this popup.
e) Give a name/description and save this new package.
- Execute Data Manager Package
Run this package from Data Manager, once status is showing ‘Succeeded’, we can go and check the backend.
Goto BW Modeling Tool (Eclipse), find aDSO of BPC Model and select ‘Manage aDSO’ from the toolbar. There you can see that all the requests are activated.
- You need to create a process chain for each model you want to compress.
- Above BW process chain is just a simple(basic) example, in your real use case, you can add other process types to fulfil your user requirement in one execution.
Handling on zero elimination
Zero elimination is a very important step to keep the ADSO size as slim as possible, to do this, please follow below steps:
- Update BW/4HANA code
- Option 1: Upgrade to DW4CORE 200 SP04 or above
- Option 2: Implement below SAP Notes :
- Enable zero elimination
- Option 1: Set the RSADMIN Parameter as per the instruction on 2950922 – ADSO: Activation with zero elimination Note: If you are on BPC 11.0( BW/4HANA 1.0), you need to INSTALL this note as well.
- Option 2: Select “Zero Elimination” while activating the request as shown below (Editing Request Details | SAP Help Portal)
In case you already followed instruction and performed request activation WITHOUT zero elimination, you can still eliminate those zero records as belwo:
- Implement below SAP notes:
- Run “SAP_NULL_ELIMINATION” report to delete such records. However, the same prerequisites apply as for zero elimination in general (only data mart DataStore objects, no non-cumulative key figures, no key figures with MIN aggregation and MAX aggregation).
Though the planning requests are activated with above process chain/type, but those planning requests are NOT physically removed from RSPMREQUEST, If there are too many requests for one ADSO in this table, this will not only decrease performance but eventually lead to failing processes due to too large in-lists in some SQL statements.
In order to diminish the number of requests, we need RSPM housekeeping; this will delete from RSPMREQUEST those requests that are in status “moved” or “deleted” and belong to an RSPMPROCESS that has been finished at least X days ago. RSPM housekeeping does not delete requests that are still needed.
- Starting from BW/4HANA 2.0 SP7, a new process type ‘”BW Request Status Management: Housekeeping” has been introduced
- Add a step for RSPM housekeeping
- Create a varient for this
- The final process chain could look like this:
- For BW/4HANA relase before 2.0 SP7, program RSPM_HOUSEKEEPING could be included into process chain as a step in similar way
- Add a step with process type ‘Execute ABAP program’
- Create a process varient for this step, input program name RSPM_HOUSEKPEEING
- Create a program varient for this ABAP program, choose the ADSO, specify number of days and choose ‘Execute Housekeeping’
- Save everything, the final process chain could look like this: