New Clean and Replace Feature for SAC End User File Upload

[gerd_schoeffl]

Why do we need a Clean and Replace Feature?

The end user centric file upload allows SAC users to simply upload (plan) data from a file. The new records uploaded from the file will either overwrite the existing records or will be added to them (controlled by a setting in the upload). But what about records that are already in the system and in the target scope of the file upload but have no corresponding entry in the upload file? Those records should either be kept or deleted from the system - depending on the setup of the planning process. So far there was no automatisms to purge the target data area.  Using a data action was difficult as the data action cannot know anything about the content of the file that will be uploaded.

So with QRC4.2025 we ship a new feature in the file upload that enables the system to purge the target data area before doing the upload. The logic applied is very similar to the logic used in the file import in the data acquisition framework.

It is possible to purge the entire target version of the file upload or the target scope can be determined from the content of the upload file. We will explain the feature in detail with a simple example.

How to use the Clean and Replace Feature

We assume we have a company planning the sales amount for some beverage products in various countries. The planning should be done by four different product groups: Juice, Water, Cola, and Ice Tea.

Let us assume there is already data in the system:

Now the end user wants to upload some new data. In the new plan that is captured in the file we only have data for two countries - Argentina and Brazil.

[Note: as in the current plan we do not care about product yet we will post the data to the product '#'.]

Let us configure a file upload for this file and model and add a starter to our story. As this is a well know functionality we do not describe in detail how to perform this task. You can find the details also in the following blog: 

New End User File Upload in SAP Analytics Cloud QRC3 2024 

Let us have a look at the result of the upload:

As we can see the data for Brazil has been overwritten whereas for Argentina we still see the two 'old' records (Cola and Ice Tea) that could not been overwritten by any records from the file.

Let us revert the data changes and go back to the modeling to use the new clean and replace feature. We change to the data management tab, find our file upload, and change the settings.

In the view of the upload details we open the settings screen.

Now we change the import settings to 'Clean and replace subset of data'.

If we do not select any dimension for scope, the system will delete all data in the target version of the upload. As this might be unexpected, the system shows the above information.

In our case we do NOT want to delete all data in the target version. There is data for other countries (like Canada or Mexico) that should not be clear as no data for these countries is uploaded. But we DO upload data for Argentina and Brazil and want to make sure that in the result we will only see the uploaded data for these two countries. Thus we choose 'Country' as a dimension for the replacement scope.

When we now run the upload the system will inspect the data contained in the file for the available values for the dimension country. As there are records for Argentina and Brazil, the system runs a delete action for all records with the countries Argentina and Brazil in the target version, and then loads the data from the file into the system. Data booked for other countries is not be affected.

Please keep in mind that you can choose multiple dimensions for the scope. In this case the system will collect the corresponding combinations and will clean the target area accordingly.

Please make sure to not just save the changed file upload job but move to the last screen of the setup and press 'Finish'.

Now we can open our story again and run the new file upload with the same file as above.

As we can see the two additional records for Argentina have been removed, the data for Brazil has been updated as before, and NO changes have been done to any data for Canada or other countries.