Technical Articles
How to automatically backup/archive & restore your plan data
Introduction
In current release of SAP analytics cloud, only unpublished data of a user’s private version can be restored by using the Version History feature.
For published data in a public version, there is still no restore feature yet.
One idea could be to enable the Data Audit feature and download the Data Changes audit records, and then try to re-upload…. But this is a very time-consuming and very error-prone manual task with it’s limitations.
A far better option would be to periodically copy your published version data to a separate backup/archive model. But until recently (before Q1 2021), this could not be automated easily.
An automated backup/archive and restore
Here comes an easy automated way to backup/archive and restore your SAP Analytics Cloud Planning model (fact) data.
Step 1. Create new archive model
Create a model as a copy of your existing planning model and add one more additional Date type dimension that you can name “Date Archived” with ‘Day’ granularity.
Step 2. Create a new data action to Archive
Data Action Step: ARCHIVE_DATA
//-----------------------------------------------------------------------------
// Store new Data Archive on today's date of all values in the "Actual" version
//-----------------------------------------------------------------------------
MEMBERSET [d/DateArchived] = PERIOD(TODAY()) TO PERIOD (TODAY())
DATA() = LINK([MyPlanModel],[d/Version]="public.Actual",[d/Account]=[d/Account],[d/Product]=[d/Product])
Note that Version, Account and Product dimensions are local dimensions, which means you have to specify them in the LINK() formula. This not needed for Public dimensions.
Data Action Step: CLEANUP_OLD_ARCHIVED_DATA
//----------------------------------------------------------------------------------------------
// Delete Archived data older than 30 days, and not taken on the first day of the month
// What will remain is: daily backup (last 30 days) and monthly backup (on 1st day of the month)
//----------------------------------------------------------------------------------------------
MEMBERSET [d/DateArchived] = PREVIOUS(35,"DAY", TODAY())TO PREVIOUS(30,"DAY",TODAY())
IF [d/DateArchived].[p/DAY] != "1" THEN
DELETE()
ENDIF
Step 3. Create a new Data Action to Restore a backup/archive
To restore your data back from your archive model into your planning model
Make sure to add a parameter “ArchiveDay” to prompt for the Day you want to archive from:
Data Action Step: RESTORE
//----------------------------------------------------------
// Restore data from the archive model on a certain day
//----------------------------------------------------------
DELETE()
DATA() = LINK([MyPlanModel_Archive],[d/Version]="public.Actual",[d/DateArchived]=%ArchiveDay%,[d/Account]=[d/Account],[d/Product]=[d/Product])
Step 4. Create a story to test the Data Actions!
Conclusion
Until SAP Analytics cloud provides a better standardized solution, this automated daily backup/archive approach can help you in the mean time.
Limitations
- Tenant Memory
In case you have large amount of transaction data & relatively small SAC tenant memory (e.g. in your test tenant), the data action (version publishing step) could fail due to insufficient resources. Make sure you test the data actions with maximum amount of records in your backup model and foresee cleanup/housekeeping actions. - Version handling
You cannot run the backup job easily across all versions, and versions often need to be hard selected. If you require dynamic version creation & simulations to be backed up, you still have issues doing so. - Performance
Setting up this backup procedure for all your planning models might be cumbersome task & you don’t want a bunch of data actions to run in parallel at the same time. Consider using Multi-Actions to schedule data action execution one by one, and be wary that it might impact other planning activities using data actions. - Fact Data & data locks
You can only use this method to backup/restore your fact data. Master data changes, comments, model changes like data locks, and anything else is NOT possible.
Be wary that data locks in your model could fail your restore data action.
Update November 2022: added limitations.
impressive work Jef Baeyens
Intuitive alternative to data audit feature, well done!
Nice one Jef! At one of my projects I setup a similar concept after a "planning accident" where I indeed had to do a manual restore from the audits. In addition I used the calendar feature to schedule several data actions for automatic daily, weekly and monthly backup.
Cheers,
Xavier
Brilliant blog Jef! Thank you.
Thanks for writing. All of my dimensions copy over except the values?
Not sure what you mean, this is only about transaction data values.
Maybe you miss a filter in your report. You can check in your backup model (data foundation view) if you see the records copied over.
I should have been clearer. Apologies. When I used the data/link formula from my source model into the target model, all of the dimensions went over to that model but my values all showed up 0. It didn't copy the account dimension. I see all of the line items as expected but no values to any of the lines.
I have no idea what you mean with dimensions go over. But are you using a 'new' advanced model? Because there are still some weird limitations reg. Zero handling & null suppression with the new model. Maybe that has an effect. Did you check in the model data foundation as suggested?
It turned out, I was copying a model that was "classic" into a new model that is "new model". SAC has now disabled the ability to go from the old model to new to avoid these issues!
Very innovative idea, Jef!
Hello, tks for the post.
By the way, is there any process, standard our not, to make backup of stories, model structure, security and all other artefacts developed during as regular SAC planning project. (something similar to what transaction UJBR used to do for BPC ).
I am aware of Export/Import process, but I looking for something that could be handled at server level and be scheduled to run everyday.
Tks in advance,
Bruno
Unfortunately there´s no automatic backup for stories, model structures, roles, etc. You can create copies manually in the system or work as mentioned with manual export / import.
Regards, Thomas
Hi Thomas,
Thank you for this post, very useful.
Not sure that I understand correctly about exspot/import models... Is it possible to automate this procces?
Thank you.
Hi Iryna Gdulia, export and import of models is possible via Transport / Export and Import. Typically "Content Network Storage" is used instead of old "File Transport". Automating this process is not possible. Export / Import must be triggered manually.
With QRC3.2023 there is a new option to run transports via SAP Cloud Transport Management. I haven´t checked it until now and can´t answer if there is an option to automate export / import of models. BR Thomas
Thanks a lot, Thomas!
We will learn more about "SAP Cloud Transport Management", it seems it can help with this task.
I use the same idea to back up the data of a model. A not unimportant detail:
Jef Baeyens I'm sure you are aware of this 🙂
Hello Jef,
thanks for the great blog post!
Just 2 remarks for other users:
e.g.