Performing Selective Data Deletion on BPC Models
This document will explain how to delete data selectively in a BPC model.
– SAP BPC 10.0 on SAP NW BW(7.31)
– EPM Addin – Version 10 SP21 .NET4
SAP BPC provides provision to clear data through data manager package or script logic. However, what each of these approaches do is that they zero out the existing records. Moreover, zero record elimination (compression/Light Optimization) cannot be performed selectively to get rid of those records. You can remove zero records from your template or report through Sheet Options but it could have an impact on other users if the same template is being used.
SAP BW provides a standard program to generate a program for selective deletion – RSDRD_DELETE_FACTS. We will leverage the same program to make it more easy to use for BPC Models.
BPC Models in essence are real-time InfoCubes and hence this program will treat them in a similar fashion as other regular InfoCubes. Here is how the initial screen of the program looks like:
When program is executed after the data target has been entered and option to generate selection program has been selected, the field “Name of report” is populated by an ID – which is generated program that can be used to perform selective deletion. It can also be transported as well.
Execute this generated program, it will direct you to the selection screen where all the fields are available for selection. You can make selections based on single values, ranges, exclusions and other operators. You can put selections and save as variant to be called in process chain.
We will be creating a custom program – ZBPC_DELETE_FACTS where you can make a selection for Environment and its corresponding model for which selective deletion program is required to be generated. We will create a selection screen for selecting BPC Environment and corresponding models in that Environment.
Once you enter the Environment, you will be required to select Model. The program restricts the list of models based on Environment.
When the program is executed successfully, it will display the name of the generated program.
Use this program to create your selection variant. Execute this generated program using SE38 and you will be presented with the selection screen:
After having selected your appropriate selections, you can save it as variant by clicking on “Save” icon.
There is an option to simulate your deletion process as well. It will not delete any records but will let you know how many records will be deleted. This could act as a preliminary check when setting up this process.
Now you have the program name and the variant and that is all that is needed to execute it via process chain.
Remember to turn the BPC Model to Load Mode before calling the program and turn it back to planning mode after selective deletion program has been executed. Now there are standard process types available in the process chain to do that but there you need to provide technical name.
It would be better to provide Environment and Model ID for selection as we did above for switching between load mode and planning mode. We can create two small programs whereby we can save variant and call that using process chain.
Your process chain will have three programs called in sequence:
– Program with variant to switch BPC Model to Load Mode
– Selective Deletion Program with variant to perform selective deletion of data in BPC Model
– Program with variant to switch BPC Model to Plan Mode
You can configure to trigger this process chain using data manager package too.
Since the program involves deletion of data, please be careful and liaise with your security and compliance team for any concerns. Use your judgement in using it. I cannot be held liable for any issues that it may cause.
Thank you very much for info..
here my question is when I execute the program its successfully generated the name of the report.
"it will direct you to the selection screen where all the fields are available for selection. You can make selections based on single values, ranges, exclusions and other operators."
can you please explain the above statement, I don't get your point here.
Thank you .
Saida Reddy Gogireddy
I have modified document to include additional screenshots for further explanation. Hope it provides the needed explanation.
Thank you very much for your reply, I don't understand what is the need of writing a custom program after generation report name
I just follow your document and generated the program name.
what are the next steps.. what is the use of generating the name of report here..?
Thank you very much ..
Saida Reddy Gogireddy
Let me try to explain. The generated program that starts with "GP" is the program that you will use for scheduling selective deletion. The first program ZBPC_DELETE_FACTS helps to get this generated program id that you will be using to perform selective deletion. ZBPC_DELETE_FACTS helps to execute program for BPC models only and there is no need to look for technical name of the Model which you will have to provide if you use RSDRD_DELETE_FACTS. There can be many reasons why we should not perform any activity in BPC using technical names and hence this program.
Once you execute ZBPC_DELETE_FACTS, it will provide you a generated program in the output (starting with GP). Execute this GP* program using SE38 and then save your variant. In the process chain, you will be calling GP* program and the variant created. Was I able to explain?
Interesting, but can you provide a business case to use this approach?
Here is what we faced during one of the assignments.
A little background- We have to create a forecast input template which will be used to capture forecast from on field representatives. The number was 900+. We had a dimension for representative ID in the model. The data records coming from the source had the field populated - based on their specific authorizations. The IDs were same as their NW ID. We kept the ID in the page axis and used override so that everytime, they refresh data, they will see data for their combinations only. We could not remove zero records because they will have to enter forecast for future horizons which could be coming all zeroes from BW.
Requirement- Every planning cycle, there were cases where instead of the assigned representative, those products were to be planned by their backup - in case someone is on vacation or sick. We used to move data records from one user ID to another user ID. After moving the records, the combinations continued to show up for earlier user ID even though there are no numbers and since there was no zero suppression in the template, the combinations can still be planned if that person logs in. To avoid this risk of duplication, we have to get rid of the records against that user and that is where we used it.
Hope I am able to explain it.
Zero elimination for light optimization can do the same job!
But you cannot do selective zero elimination. You have compress entire data request(s).
And what for I need selective zero elimination?
When records are moved from one User A to User B, all records for User A will now have a value of zero. We need to get rid of these records for User A so that even accidentally there is no plan entered.
Here is how screen looks for user A at the start - before data is moved to User B:
Here is how it will be for User A after move DM package is executed:
As you can see that if User A accidentally logs in, the forecast can still be input for same combination and can be saved and the User B might also input for same combination. This poses a risk of duplication.
We cannot compress or perform light optimization with zero elimination as it will flush out records for other users who are also planning.
So we performed the selective deletion for User A after its data is moved to User B.
We perform light optimization with zero elimination in this case. And yes, it will remove all zeros... but it's fine!
I agree that light optimization with zero elimination will take care of this case but it will create problems for other users who have empty forecast buckets as all zeroes and it will remove those rows too.
Another user before zero elimination:
After lite optimization with zero elimination is performed:
The future forecast periods are lost where user has to provide input. So lite optimization with zero eliminate solves the previous problem but can cause problems for all other users who might have empty forecast buckets as they will not be able to enter forecast anymore.
We assume a simple rule - zero and empty records are equivalents...