- SAP Managed Tags:
General Situation
Last year we introduced the the end user file upload in SAC where end users can upload Excel or csv file into SAC (for details see New End User File Upload in SAP Analytics Cloud QRC3 2024. In this blog we want to show how the file upload can be used in a more dynamic way.
When using a file upload, it is a quite common situation that not all of the dimensions used in the model are drilled down in the rows of the file. We often see the situation that one dimension (typically a time dimension) is drilled across the columns of the file that is to be uploaded. The file upload in SAC offers a possibility to unpivot those columns.
In order to configure the unpivoting, the administrator who creates the end user file upload in modeling has to use a template file with the corresponding dimension in the rows and one dimension in the columns. In the wrangling the table can be flipped and the drilled dimension can be moved to the rows.
Unfortunately the wrangling behaves in a quite 'static' way. When the end user performs the upload the file has to:
- have the same number of columns as the template file that the administrator has used,
- the column headers in the uploaded file must be the same as in the template file.
Obviously, when a file upload is used in several planning cycles throughout a year, the dates for which plan data should be uploaded will vary. One option to adapt to the changing time values might be to adapt the template file, the wrangling, and thus the file upload job any time the planning horizon changes. Obviously, this might require a lot of work for the administrator/modeler.
We are fully aware of the fact that this behavior leaves room for improvement and we are considering changing this behavior in a second phase of the file upload feature. In the meantime, we would like to show a work around.
Description of the work around
Let us assume that we are planning monthly data for one year ahead (12 columns), always starting with the next upcoming month. So in January 2025, the end user wants to upload data for February 2025 until January 2026, in February 2025 the end user wants to upload data for March 2025 until February 2026 etc.
The administrator/modeler can now create an upload job using say 36 columns ranging from January 2025 to December 2027.
The end user can then use that file with 36 columns and only fill those columns that are used in the current planning cycle, leaving all other columns blank.
Unfortunately, we have to go a little into the details of the behavior of the file upload in order to explain why an additional step will be necessary.
Let us assume the end user has uploaded data for January 2025 already and now wants to plan data for February 2025 until January 2026. So the planner fills in the corresponding columns (starting with February 2025) and leaves all other columns empty, including the column for January 2025. Now the crucial question is what should happen with the plan data for January 2025 - should it be deleted as we have moved on with our planning cycle or should it be kept? And what WILL happen with the standard setup where we just do an unpivoting in wrangling?
With the given setup the planner will upload a file with 36 columns. The wrangling will unpivot the file and will generate the corresponding rows. For January 2025 there will be records with an empty measure. The data is then handed over to the data motive component to write the data into the desired version. All records with an empty measure value will be regarded as a DELETION of the existing data - so in a setup where you just use the unpivoting the data for January 2025 would be ERASED from the system. That might be the desired behaviour.
But how could the other behaviour where the existing January 2025 data stays untouched be reached? The solution is to make sure that all empty records are removed from the set of records BEFORE the data is handed over to data motive. This can be done by a simple transformation in wrangling (after the unpivoting step). As in this case data motive receives NO records for January 2025. Untouched data - be it that there is not record for a given combination or that a certain measure is not used in data handed over to data motive - will not be touched. Thus is our case the data for 2025 would not be deleted.
Step by step solution
We now want to describe step by step how the scenario explained above can be realized.
We start with a model containing using the month and a simple table:
The time is drilled in the rows and we already have plan data in January 2025.
We set up a file upload using an Excel/csv template file with the time in the columns.
First, we create a new file upload job using the above file as a template.
In the wrangling step we mark all columns containing a month in the header and press 'Unpivot'.
Press ok in the panel on the right side. The columns are now converted into rows. Please note that the column with the months is now called 'key' and the measure values are contained in a column called 'value'. If you want you can rename those columns.
Please be aware that the column containing the measure values might have the wrong format. Go to the properties of the column (in the pane on the right side) and make sure the type is a number. Otherwise you will get an error when mapping this column to a measure in the system.
Now comes the crucial step: as described above, any record that has an empty value should not be handed over to data motive. Thus we create a transformation that removes such records.
Click on the value field, click in the upper icon, and choose 'Create a Transform...'.
Now choose 'Filter'.
Edit the transformation rule in such a way that it filters all values in the column 'Value' that do not match "". Press return so the rule gets applied.
In order to test you transformation during the setup you might already use one empty column so you can verify the correctness.
You can now go ahead to the next page, map all columns to system dimensions and measure(s), proceed to the last step, and finalize your upload job by using 'Finish'. Please keep in mind that if you exit the job setup with the option 'Save and Exit' the upload job will not be in a finalized state and thus not executable.
You can now go ahead and create a starter for the file upload in your story.
In order to test the file upload use an Excel/csv file that has the same structure as the template file but where the value for January 2025 is empty.
When you run the upload the new plan data is written to the system but the data for January 2025 stay untouched - just as desired.
1 Comment
