Skip to Content
Technical Articles
Author's profile photo William Yu

How to Optimize BPC Model in SAP BPC 11, version for SAP BW/4HANA

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.

  1. 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

 

 

  1. 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.

       

  1. 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.

 

NOTE:

  • You need to create a process chain for each model you want to compress.
  • Process type ADSOREM today can only compress records (per key combination), zero elimination is still NOT possible at the moment.
  • 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.

Assigned tags

      32 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      Do you think that in some SP standard chain LITE_OPTIMIZE will be corrected? And what about zero elimination? In some scenarios zero elimination is a required functionality....

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Vadim,

      Just a quick question to you around zero elimination. Can you name few scenario which require it? And usually how often it's scheduled?

      Best regards, Willliam

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      There are a lot of cases where zero records are generated:

      1. Clear data using CLEAR DM package (example: clearing forecast/budget category to reuse it, clearing incorrect data load, etc…)
      2. Clearing data using script logic *REC(EXPRESSION=0) for some business logic calculations.
      3. Clearing data using script logic *RUNALLOCATION
      4. Clearing data as a result of rules execution
      5. Clearing data on data load with option to clear records

      After clearing data we still have a record with zero amount

      Result:

      1. Impossible to distinguish between no record (empty cell in report) and zero result – especially for parent members (sample - you are looking on some parent - if the result is empty then all dependents are also empty; if the result is zero then it can be +1000 -1000)
      2. Zero records will be processed in script logic – performance issues
      3. Queries will also process zero records – report performance.
      4. Impossible to delete dimension member if some data was posted on this member (in previous BPC versions you delete data and perform compression with zero elimination)
      5. Export data will contain useless zero records
      6. etc

      It’s a good idea to schedule compression with zero elimination over night. But it can be launched manually after some significant cleanup

       

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Thanks Vadim for the input.

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Vadim,

      No, Standard chain LITE_OPTIMIZE will NOT be back.

      Fully understand the requirement of zero elimination, but need to check with BW/4HANA team on this.

      Best regards, William

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      OK, may be not standard LITE_OPTIMIZE but something like ADSO_OPTIMIZE as a new standard BPC11+ chain with model selection!

      Author's profile photo peter edge
      peter edge

      Please do William,

      I just had an SAP advisor tell me on a incident that ADSOREM (recommended by SAP in note 2669682) is not BPC relevant.  It may historically be BW namespace but when you recommend it as replacement for a BPC process your BPC guys need to know.  Also it doesn't like large volumes of both records and requests and is effectively slower than the old Lite Optimize which I understood performed more tasks?

      Best Regards

      Peter

      Author's profile photo peter edge
      peter edge

      HI William,

       

      Re  "You need to create a process chain for each model you want to compress.".  Not strictly true, I have a chain that runs for multiple models.  You can even use wildcards in the variants to select all BPC objects of selected type ie /CPMB/*.

       

      Best Regards

      Peter

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Peter,

      You are not wrong. But here, we are triggering the compress from data manager usually is model specific. Of course you can create a process chain processing multiple aDSOs, but it's not suitable to be triggered from DM conceptionally.

      Best regards, William

      Author's profile photo Hugh Gledhill
      Hugh Gledhill

      Hi William Yu

      Is it necessary to compress BPC aDSOs for performance purposes, like we used to with light optimizes? We have followed the guidance you set out in your blog and created process chains to clean up old requests in aDSOs, but we are finding that the process can be very slow due to the way that aDSOs manage requests. Per Gregor Dieckmann in a question posted to the community here, a new request is opened on the aDSO each time data is saved. This means that we get a high number of requests for the number of records, which is then much slower to compress.

      For example, over the course of a day, our users generated ~420,000 records, which were captured in ~20,000 requests, i.e. ~21 records per request. As a result, the compress took 83 minutes.

      Thanks,

      Hugh

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Hugh,

      As a non-expert on HANA, I assume it might be case by case.  I would expect at least compressing aDSO will release some HANA memory(not sure how much) especially when doing a lot of simulation and iteration.   Maybe you can also obverse from your side around performance and memory.

      On the other hand, I'm not sure if the time spent on compression is normal. Maybe it worth a further check with BW team. If the ROI of aDSO compression is low, probably you only need to do it once in a while.

      Best regards, William

      Author's profile photo Alejandro Munoz
      Alejandro Munoz

      Hi William,

      My system is SAP BPC 11.1 NW, Standard Model.

      I need to delete the zeros because I need to remove the master data.

      We have a problem with a machine, and I need to create a clean environment of master data and transactional data.

      ¿Any suggestion to delete zeros?

      Best regards, Alejandro.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      I have asked about this functionality in October 2019, but still no answer...

      Author's profile photo Alejandro Munoz
      Alejandro Munoz

      I remember Vadim, I opened a note in July 2019: no solution ?

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      Unable to understand how it's possible to recommend to upgrade BPC to 11 with this functionality missing...

      Author's profile photo Hengye Zhu
      Hengye Zhu

      Please wait for BW/4 2.0 SP04 per the following link https://help.sap.com/viewer/b3701cd3826440618ef938d74dc93c51/2.0.4/en-US/4236011840444a459a18aafa0eeca3e2.html?q=zero%20elimination

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Thanks !

      Author's profile photo Swakshar Choudhury
      Swakshar Choudhury

      Hi William,

      We have just upgraded to BPC 11.0 & BW/4HANA 1.0 SP15 last week. Now we did not get an answer from SAP on any alternate solution for performing zero elimination, the suggestion was to upgrade to BW/4HANA 2.0 SP04. What is your recommendation for zero elimination? Selective deletion from the ADSO?

      Looking forward to your response!

      Regards,

      Swakshar Choudhury

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Swaklshar,

      I'm afraid that this feature won't be down-ported to BW/4HANA 1.0 at the moment. Probably you need to check with BW expert on viable options. I assume there is no solution from BPC side.

      Best regards, William

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      And what do we have on BW/4HANA 2.0 SP04?

      Author's profile photo Reinaldo Abe
      Reinaldo Abe

      Hi William,

      I’m also keen to hear any updates on zero elimination for BPC11 on BW4HANA 2.0 SP04 before assessing an upgrade. I’m working on version BPC11 on BW4HANA SP01.

       

      Is there any plan on releasing a note to be applied separately?

       

      Cheers.

      Author's profile photo William Yu
      William Yu
      Blog Post Author

      Hi Reinaldo Abe

      As far as I know there is no note to be applied for this. You need to upgrade to SP04.

       

      Best regards, William

      Author's profile photo Luc Naessens
      Luc Naessens

      Hi William,

      we are on BW2.0 SP04, and I tried to perform a zero compression on last request of a BPC 11 model (supposedly collecting backwards already activated requests).

      BPC4HANA 200 SP0003 / DW4CORE 200 SP0004

      The log indicates that Zero Elimination has been performed but the BPC Model still holds zero values…

      Correction: after a new test generating zero balance on full key, it did compress and delete the zero value from the last new requests.

      But, still remains the zero values from previously activated requests (without having flagged them with zero elimination) they don´t seem to be captured and eliminated on last request activation.

      Oss 2669682 Version 9 updated yesterday (01.09.2020) still states the same as previously: not supported…

      Hopefully this will be enhanced soon, since I found two dumps related to in ST22 (ASSERTION_FAILED CL_BW4_MANAGE_PROCESS=========CP)

       

      Best Regards,

      Luc

       

      Author's profile photo Luc VANROBAYS
      Luc VANROBAYS

      According to OSS 2669682 Version 9 dd 01.09.2020 Zero Elimination it is not supported in BPC11. This must interpreted in a sense that Model Optimization cannot yet be set up with Data Manager Package as described in present blog. And hopefully, it will in the future.

      "For data mart Data Store objects, you can now select zero elimination when activating requests.
      When zero elimination is applied, all records in the active table for which the total for every key figure is zero are deleted."

      Cfr.

      DataMart ADSO for Planning Zero Elimination

      This means that it can be manually done for each BPC Model thru BW/4 Manage Data Store Cockpit with or without zero elimination.

      First, switch the BPC Model Adso into Load Mode.

      Please beware that if requests are activated without zero elimination, because one forgot to flag accordingly it won´t be possible to clean them up afterwards in one go: then it will be required first to insert non-zero values on the key still holding zero values, then in a second step clear them or zero update is done, finally activating the last request with flag zero elimination. Then switch back the BPC Model Adso into Planning Mode.

       

      Best Regards,

      Luc

      Author's profile photo Vadim Kalinin
      Vadim Kalinin

      O, God! Why it's taking so much time to implement the feature used for years in BPC???

      Author's profile photo Fernando Santos Gonzalez
      Fernando Santos Gonzalez

      I am totally agreed with Vadim. I don't understand why it is taking so much time. William Yu , do you have any update?

      Author's profile photo Cristina Bernardi
      Cristina Bernardi

      I've tried to implement the steps mentioned on note 2669682 but it's not working for me.

      The system in BW/4HANA 2.0 SP 6.  I wonder if I've correctly added the parameters in RSADMIN? the ADSO is /CPMB/CCIE4CK, so in RSADMIN I've created the following record:

      OBJECT = ADSO_NE__/CPMB/CCIE4CK

      VALUE = X

      Any ideas?

       

      Author's profile photo Luc VANROBAYS
      Luc VANROBAYS

      Hi Cristina,

      i don't think this note is relevant for BW/4HANA 2.0.

      Compression with zero suppression works fine in your configuration. It is just that there is no way to perform it via Data Manager package. You perform it thru BW Cockpit as I described above.

      But maybe you are behind something else.

       

      cheers

      Luc

       

      Author's profile photo Cristina Bernardi
      Cristina Bernardi

      Thank you, Luc... I was overcomplicating things... 🙂

      Author's profile photo Romuald COLLETAZ
      Romuald COLLETAZ

      Hi,

       

      we have implemented the process chain but the BPC reports don't retrieve data anymore when HANA NDX is enable (as soon as the report displays aggregated data). if we disactivate the HANA MDX option, the reports return data (we are using BPC 11.1 SP03). If we reactivate HANA MDX no data are retrieved. Any idea what we have missed ?

       

      thanks for your help,

      best regards,

      Romuald

       

      Author's profile photo Hugh Gledhill
      Hugh Gledhill

      Hi all,

       

      Just to let you know that SAP have released note 2950922 to make zero elimination available on BW/4HANA 1.0. You can upgrade to SP18, or implement the note manually on older service packs. We've tested this in our development system and it appears to work correctly. As with the zero elimination available in BW/4HANA 2.0, it only applies zero elimination going forwards - you can't use it to retrospectively remove zeros where requests have already been activated.

       

      A further update from our side is that since I posted above 1 year ago, we have still been unable to find a workable solution to running compression on BPC 11.0. We are now at around 1.5m unactivated requests in our main model. SAP have suggested every note going, but nothing that actually addresses the fact the BPC 11 / BW4 generates far more requests for the same usage than previous versions of BPC / BW, and therefore takes far longer to compress. Even if we manage to catch up on the backlog of requests to activate, we're still looking at multiple hours downtime each day to keep up with compression (since users cannot save data while the process chains described in this post are running).

       

      Despite all this, we haven't yet seen a significant performance hit due to the unactivated requests - if this were a previous version of BPC / BW, we would have ground to standstill months ago. It's not clear whether the lack of performance degradation is because compression is no longer really required, or because the "power" of HANA is masking the issue. SAP don't seem to know or be able to advise on it either.

       

      Will update if we ever get to a practical solution.

       

      Thanks,

       

      Hugh

      Author's profile photo Mark Dean
      Mark Dean

      Hi guys,

      We are running an hourly load of ERP GL Balance data (FIGL12) to a BPC 11.0 Model. Sometimes an error occurs when loading this transaction data, e.g. missing BPC master data, missing BPC conversion rule etc. and we run a Full Load Replace from the staging BW ADSO, (the delta records are set not to be kept for BPC), so as to get the data completely up to date in BPC.

      When the Full load works i.e. all possible BPC errors have been eliminated, e.g. now have latest master data and all conversion file rules have been setup, then the delta load is just re-enabled and this has been working fine.

      Recently, however the time to run the Full Load correction process has been taking too long to implement especially at busy month end.

      I have put this down to too many loads that need to be compressed (as you know when we do a BPC Replace load it posts the full selected data set with opposite signage so as to aggregate to zero and then adds the records to be added).

      Presumably SAP thought that with SAP HANA there was no need for Compression, and the necessary further zero elimination (e.g. need to delete master data for example or to get some database back !), but as the time to run the Full load correction has been getting longer and longer I can only put this down to the database getting larger.

      We already have in place the ADSOREM COMPRESS Process chain Process Type and have amended this to be run from a custom DMP where one can select whether or not zero amount deletion is required. This was achieved by an implicit ABAP class enhancement for CL_RSDSO_REMPROC, Method IF_RSPC_EXECUTE~EXECUTE. I.E. after a successful Compress, i.e. records are then place in the ADSO Active table from the Inbound table, a SQL delete statement removes those Compress records in the Active table where the amount is zero.

      This has been working fine for a number of customers. I still don't know why (as Vadim has previously mentioned) why SAP did not keep the Optimize with zero Deletion as in BPC 10.0, 10.1.

      SAP Note 2950922 does provide a way providing you are on at least release DW4CORE SP18 but some of our customers are not yet on that BW/4HANA release.

      Regards

      Mark