Skip to Content
Technical Articles
Author's profile photo Jef Baeyens

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

New data action on your new archive model with 2 advanced formula steps.
(In parameters, I’ve hardcoded the Target Version to ‘Actual’ for simplicity reasons)

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.

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Iver van de Zand
      Iver van de Zand

      impressive work Jef Baeyens

      Author's profile photo Nick Verhoeven
      Nick Verhoeven

      Intuitive alternative to data audit feature, well done!

      Author's profile photo Xavier Hacking
      Xavier Hacking

      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

       

      Author's profile photo Kholisa Bika
      Kholisa Bika

      Brilliant blog Jef! Thank you.

      Author's profile photo Kelly Wheeler
      Kelly Wheeler

      Thanks for writing. All of my dimensions copy over except the values?

      Author's profile photo Jef Baeyens
      Jef Baeyens
      Blog Post Author

      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.

      Author's profile photo Kelly Wheeler
      Kelly Wheeler

      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.

      Author's profile photo Jef Baeyens
      Jef Baeyens
      Blog Post Author

      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?

      Author's profile photo Kelly Wheeler
      Kelly Wheeler

      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!

      Author's profile photo Mark P Burke
      Mark P Burke

      Very innovative idea, Jef!

      Author's profile photo BRUNO MARCOS
      BRUNO MARCOS

      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

      Author's profile photo Thomas Karner
      Thomas Karner

      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

      Author's profile photo Iryna Gdulia
      Iryna Gdulia

      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.

      Author's profile photo Thomas Karner
      Thomas Karner

      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

      Author's profile photo Iryna Gdulia
      Iryna Gdulia

      Thanks a lot, Thomas!

      We will learn more about "SAP Cloud Transport Management",  it seems it can help with this task.

       

      Author's profile photo Thomas Karner
      Thomas Karner

      I use the same idea to back up the data of a model. A not unimportant detail:

      • If model and archive model use the same public dimensions everything works fine
      • If model and archive model use their own private dimensions you have to make sure that the members in both dimensions are kept synchronous (manually or import), otherwise the LINK function in the data action will not backup all values.

      Jef Baeyens I'm sure you are aware of this 🙂

       

      Author's profile photo Michael Frieser
      Michael Frieser

      Hello Jef,

      thanks for the great blog post!

       

      Just 2 remarks for other users:

       

      1. If you use the new model and the new model contains more than 1 measure, you need to implement several lines per measure.
      2. With Multi Actions, it is also easier to backup multiple versions at once. Just define a SourceVersion Parameter and then run the Data Actions in your Multi Action (for each version, 1 Data Action). Of course, if you define a new version, you need to manually add the Data Action execution to your Multi Action).

      e.g.

      DATA([d/Measures]="Measure1") = LINK([PLANNING_MODEL],[d/Version]=%SourceVersion%, [d/Measures]="Measure1")
      DATA([d/Measures]="Measure2") = LINK([PLANNING_MODEL],[d/Version]=%SourceVersion%, [d/Measures]="Measure2")