Skip to Content
Introduction

This document will explain how to delete data selectively in a BPC model.

Software Components

– SAP BPC 10.0 on SAP NW BW(7.31)

– EPM Addin – Version 10 SP21 .NET4

Requirement

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.

Approach

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:

BW Program Selection Screen.jpg

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.

BW Program Executed.jpg

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.

BPC Program Selection Screen.jpg

Once you enter the Environment, you will be required to select Model. The program restricts the list of models based on Environment.

Selections.jpg

When the program is executed successfully, it will display the name of the generated program.

Output.jpg

Use this program to create your selection variant. Execute this generated program using SE38 and you will be presented with the selection screen:

Make Selections.jpg

After having selected your appropriate selections, you can save it as variant by clicking on “Save” icon.

Variant.jpg

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.

Plan_Load Switch.jpg

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.

Note

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.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Saida Reddy Gogireddy

    Hello Gajendra,

    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

    (0) 
    1. Gajendra Moond Post author

      Hi Saida

      I have modified document to include additional screenshots for further explanation. Hope it provides the needed explanation.

      (0) 
      1. Saida Reddy Gogireddy

        Hello Gajendra,

        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.

        BW Program Executed.jpg

        what are the next steps.. what is the use of generating the name of report here..?

        Thank you very much ..

        Best Regards,

        Saida Reddy Gogireddy

        (0) 
        1. Gajendra Moond Post author

          Hi Saida

          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?

          (0) 
    1. Gajendra Moond Post author

      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.

      (0) 
            1. Gajendra Moond Post author

              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:

              Before Move.jpg

              Here is how it will be for User A after move DM package is executed:

              After Move.jpg

              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.

              (0) 
                1. Gajendra Moond Post author

                  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:

                  Before LTZE.jpg

                  After lite optimization with zero elimination is performed:

                  After LTZE.jpg

                  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.

                  (0) 

Leave a Reply