How to automatically backup/archive & restore your plan data

[JefB]

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.