Business case
When using the SAP Analytics Cloud planning solution, in many cases, we would need to back up the planned data periodically with the ability the restore the data in the planning model to a specific point in time.
Solution
A possible solution is to create a backup planning model and copy data from the source model (main planning model) everyday and schedule it daily to run automatically. Additionally, we will create a manual restore backup action that can be used to restore data from our backup model to the source planning model.
We will accomplish the task by using data actions and advanced formulas. Please note that the import/export feature in SAC can be used to manually backup the whole model (only can be done in same versions or subsequent versions of SAC). However, this proposed solution is much more robust as we will copy only transaction data (not the whole model) and secondly, we will schedule it so that no manual intervention is needed.
Planning Model
For the sake of the demo solution, we will use a simple SAC planning model that has an account dimension and 2 main dimensions (apart from version and date), namely, regions and product group. Sample data has been loaded into the model using a simple excel file.
A copy of the “OpIncome Model“ was made called “OpIncome_Backup” model. This model will be used to store the backup data. After clearing the data in the backup model, a new dimension called “backup_date” of type date was added. The data from the source model will be periodically be copied to the backup model with the date of copy written to the backup date dimension.
Data Actions
Back Up Data
We will build 2 data actions, one to copy from source model to the backup model. A second one to restore a specific backup data (date) from the backup model to the source planning model.
The data action called “backup” has 2 steps, one to write data from the original source planning model to the backup model. We make sure to select the current date for the value of backup date dimension. Also make sure to select the correct version to copy.
The “memberset” line makes sure to set the “backup_date” dimension value to the current date (using the today() function). It is required to specify a range which we do using the “TO” operator.
By using the LINK() function, a cross model copy can be achieved. The 1st parameter specifies the source model followed by the filter dimensions. If public dimensions are used (which is not in the current case), these dimension filter parameters are not required.
The 2nd part of the data action cleans up the specific past backup data (which is no more needed).
Using the memberset formula we define a range of (Today – 45 days) to (Today – 30 days) so that only data of last 30 days is kept in the backup and everything else is deleted. The if-clause makes sure not to delete the data that was backed up on the 1st of any month. This condition is optional to revert to a monthly version if required. You can adapt it to suit any specific backup needs.
The next step is to schedule the data action. Navigate to the calendar in SAC, select the current date and add a data action task.
Name the data action task and specify a start date. Subsequently, click on add recurrence, and specify a recurrence pattern desired. The data action is now scheduled to run every day.
We will now create the restore data action to move data from the backup model to the planning model when a backup restore is required. This data action will obviously be done manually through a story.
Restore Data
The restore data action is created on the source model. We first delete the data in the source model and then using the LINK() function, we restore data into the source model from the back up model.
Two parameters are set up for the data action parameter named “backday” which is used as a prompt that will allow the user to choose a date of the backup that needs to be restored. A second parameter “TargetVersion” is fixed to the version that is being restored.
For the sake of demonstration and testing, build a story.
SAC Story
Create a story based on the source planning and backup models. Insert 2 Charts and 2 data actions.
The story was built with the left table based on the source model and the right table based on the backup model. Two data action buttons were also inserted, one to copy data from source to backup model (every day, scheduled; manual for demo); the other button to restore data from a backup model to the source planning model.
Final Result
Conclusion
A periodic backup solution was implemented in SAP Analytics Cloud. Two data actions were created, one to copy data periodically from the main planning model to the backup model by scheduling it in the SAC calendar. A second data action to restore data of a specific day from the backup model to the main planning model.