Financial Management Blogs by SAP
Get financial management insights from blog posts by SAP experts. Find and share tips on how to increase efficiency, reduce risk, and optimize working capital.
cancel
Showing results for 
Search instead for 
Did you mean: 
William_Yu
Product and Topic Expert
Product and Topic Expert
18,134
Updated: June 2022:

Handling of Zero Elimination

Request Housekeeping




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.

  • 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:

  1. Update BW/4HANA code


  2. Enable zero elimination



 

In case you already followed instruction and performed request activation WITHOUT zero elimination, you can still eliminate those zero records as belwo:

  1. Implement below SAP notes:


  2. 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).


 

Request Housekeeping


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

    1. Add a step for RSPM housekeeping 

    2. Create a varient for this 

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

    1. Add a step with process type 'Execute ABAP program' 

    2. Create a process varient for this step, input program name RSPM_HOUSEKPEEING

    3. Create a program varient for this ABAP program, choose the ADSO, specify number of days and choose 'Execute Housekeeping'

    4. Save everything, the final process chain could look like this: 



39 Comments
former_member186338
Active Contributor
0 Kudos
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....
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
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
former_member186338
Active Contributor

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

William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
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
former_member186338
Active Contributor
0 Kudos

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

 

William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
Thanks Vadim for the input.
0 Kudos
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
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
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
hugh_gledhill
Participant
0 Kudos

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

William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
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
0 Kudos
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
0 Kudos
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.
former_member186338
Active Contributor
0 Kudos
I have asked about this functionality in October 2019, but still no answer...
0 Kudos


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


former_member186338
Active Contributor
Unable to understand how it's possible to recommend to upgrade BPC to 11 with this functionality missing...
former_member196060
Contributor
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
Thanks !
sayan_choudhury
Participant
0 Kudos
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
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
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

 
former_member186338
Active Contributor
And what do we have on BW/4HANA 2.0 SP04?
reinaldoabe1
Explorer
0 Kudos

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.

William_Yu
Product and Topic Expert
Product and Topic Expert
Hi reiabe

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

 

Best regards, William

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

 

lucdealethea
Participant
0 Kudos
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
former_member186338
Active Contributor
O, God! Why it's taking so much time to implement the feature used for years in BPC???
I am totally agreed with Vadim. I don't understand why it is taking so much time. william.yu , do you have any update?
romuald_colletaz
Product and Topic Expert
Product and Topic Expert
0 Kudos
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

 
hugh_gledhill
Participant
0 Kudos
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
mark_dean
Explorer
0 Kudos
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

 

 

 

 

 

 
0 Kudos
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?

 
lucdealethea
Participant
0 Kudos
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

 
0 Kudos
Thank you, Luc... I was overcomplicating things... 🙂
chandrakanthk01
Explorer
0 Kudos
Hi folks,

 

Any new update on Zero elimination optimization ?

 

Thanks,

Chandrakanth.K
JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi chandrakanthk01 , See William's latest updates.

Thanks,

John
0 Kudos
I tried this workaround too but it was not working either. ( This is recommended in the blog with SAP note 2950922 – ADSO: Activation with zero elimination )

My system is already BW/4HANA 2.0 SP 7.

Also, the program SAP_NULL_ELIMINATION is not available in our system so I have to manually activate the requests in BW cockpit and it's working fine both compressing and suppressing zero.

 

Sukanya
edwiromero_s
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi William,

In a BPC 11.1 Embedded is there any implication to do a housekeeping in an aDSO with Audit trail turned on (a compound with technical composite provider 1BW/####)

Is it possible to do housekeeping without losing any audit log data? (see this blog as a context https://blogs.sap.com/2018/03/06/bpc-embedded-model-auditing-feature-in-adso-and-infocube/)

Regards
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi Edwin,

I'm sorry I'm aware of such possibility.

Best regards, William
0 Kudos
Hi, our BPC (NW10.1, standard version) has been upgraded to SAP BPC 2021 on a new test server that has been upgraded to BW/4HANA 2.0. We are in the testing phase and found that the Consolidation run through a DM package is running many times slower than on the NW version, it is putting the brakes on the entire upgrade. Does anyone have any input on this?

 

Many thanks

Colleen
William_Yu
Product and Topic Expert
Product and Topic Expert
0 Kudos
I would suggest you to create a ticket to SAP to investigate, there could be different reasons.