How to – incrementing Versioning/Scenarios (in planning solutions using SAP BW)
Versioning concept
In many of my BW/SAC Planning projects, there are requirements for a versioning and scenario concept. Since there are several good ways to implement the Actual/Budget/Forecast and Time Component, there is often the requirement for an incrementing dimension. It can be called versions, scenario or “cases”. However, the goal is always to get the possibility to save the current data and continue in another Version/Scenario.
In this blog I will show you an approach to reach the requirements of incrementing data without getting confused by the different increments and keeping reporting easy. I used this concept in different SAP BW and SAC Planning solutions but since it is a general logic-concept, it is also applicable for other planning and reporting solutions (like Power-Bi, IBM Cognos, QlikView, Qlik sense etc.)
To reduce confusion with wording between Version and Scenario, I will call the incrementing solution further on „Snapshot-Version“.
Goal of the snapshot-version
The following requirements can be meet with the “Snapshot-Version” approach
- Incrementing and infinite stock of data
- Clear identification and allocation of each “Snapshot-Version”
- Creation timestamp and comment for each “Snapshot-Version”
- Easy and clear usage logic in reporting and planning
- Little administrative effort
- Seamless planning process without end users getting impacted by “Snapshot-Version” changes
- No need to change queries or parameters to display specific “Snapshot-Version”
Logic concept
As with any “incrementing” solution, the snapshot-versions are stored on a number. We start at 0 and can theoretically go to infinite numbers.
The main snapshot-version is “0”. All planning objects only work with this version. Therefore, we can fix the snapshot-version to “0” in all queries, planning functions and other objects, that work with data. We want to avoid the use of parameter tables to specify a snapshot-version and avoid the need to change queries to report current data. The concept of the leading snapshot-version “0” will do this and create a clear structure of data.
If we now want to save or change the data (normally create the next version), we do not change a parameter or something else but copy the current data-stock to a new snapshot-version and continue working in “0”. The users won’t even notice the difference and we have no technical interference like “closing current planning” to change snapshot-version and avoid open sessions on “old” parameters.
Example 1: Snapshot-version in an approval process
In many planning processes an approval or review of planned data is necessary. In this step the data will often get adjusted directly. Therefore, many customers want to continue in a “new scenario” for the data approval/review. With the snapshot-version, we can just “take a snapshot” of the current data (e.g. copy from “0” to “1”). This can be done by a key user/administrator in the background without the involved end user even noticing. If the saved data is required again, it can be copied back from “1” to “0” or be used in reporting for comparison.
Example 2: Snapshot-version in documentation approach
Sometimes planning deadlines define major milestones in a planning process (e.g. handover from bottom-up to top-down planning) and the data needs to be saved. With the snapshot-version, there are endless options to save data at any given time (e.g. at each deadline or milestone in the planning process, a snapshot of the current data is saved to a new snapshot-version). It can be necessary to keep all versions for later usage in an easy and comprehensible way.
In a sales-planning project I recently implemented, the process started with a bottom-up planning by salesman with an approval by regional sales-manager. Later in the process, the sales figures were adjusted from a top-down perspective to meet strategic sales goals from the board. Nevertheless, the original and approved sales-figures from the salesman where part of their target-agreement and therefore vital to be saved. This could also be done with separate characteristics to differentiate between bottom-up and top-down and further scenarios, but this would always require complexity in data handling, functions and comprehensibility.
Meeting requirements
To meet all requirements, you can use a number logic for ranges or single numbers and define a specific purpose to it. From my experience, it is always best to keep it as simple as possible and double-check all requirements if they are really needed. If there is the requirement for fixed versions/scenarios like “best case/ worst case”, you can work with fixed values:
“10” for best case, “20” for worst case. If several versions are required for this, a range can also be extended: “10-19” for the best case, “20-29” for worst case.
As in any other data model, this approach can be extended as required by making the numerical logic of master data more complex. However, it is recommended to limit the “fixed” values to as few as possible. The use in reporting can be used as a guide here:
If a certain snapshot-version (e.g. “best case” or “status before approval”) should always be reported, a fixed value can be used in the snapshot-version. This means that reporting can be restricted to this value and doesn’t need to be changed anymore.
For all other cases, a flexible description is recommended (see flexible description)
SAP BW implementation example
I will now describe a simple solution of a “snapshot-Version” characteristic with some additional logic besides the characteristic itself and copy functions.
We asume to include a „snapshot-version” characteristic in a data model. Besides that, we want master data and text for the characteristic to give the versions a meaning. Finaly, we want to track the creation time and implement some basic checks to prevent snapshot versions from being “overwritten”.
Characteristic
- Snapshot-Version: CHAR(3) with master data and tex
- Characteristic need to be included in relevant data models
Status aDSO for creation Timestamp
We want to be able to track the creation timestamp of each snapshot-version created. Therefore we want to use a status aDSO. To learn more about status aDSO see also the blog: How to – Custom Status management in SAP BW / SAP SAC with aDSOs (using BW-IP / BPC)
- We use an aDSO with the template “Planning – Planning on Direct Update”
- As key we use the main “Version”, “Snapshot-Version”, “Date” and “Time”
- As keyfigure we use the characteristic “Status” as keyfigure (“Use Characteristic used as Keyfigure”). The Composite Provider consume the aDSO 1:1 (for “Status” only the Keyfigure is used) and the Aggregation level is based on the Composite Provider 1:1
Planningfunctions
We mainly copy data from snapshot-version “0” to a target. Besides that, we need an additional planning function to save the creation timestamp. To avoid and allow overwriting of existing snapshot-versions, we provide a check and delete function. All functions can be combined in different planning sequences.
- Copy: Here we can use the standard copy function provided by SAP. We just change Snapshot-Version from source-variable to target-variable.
- Set Status: Here we need a separate Planning-function type to set the proper status (as we use characteristic as keyfigure). If you use a normal keyfigure, you can also use a simple formula function. In our case, we want so set the status keyfigure to “CREATED” and save it with current date and time in the status aDSO.
- Check Empty: Here we use a formula function. We set the used filter to the target snapshot-version and just raise an error message in the formula. (The function will only be executed if target snapshot-version is not empty)
- Delete: Here you can also use the standard SAP function (data should be deleted before it is overwritten – e.g. to restore snapshot-version 0, first delete it and then copy from a source snapshot-version).
Flexible descriptions
If even more flexibility is needed, the description per snapshot-version can be set up flexible. E.g. snapshot-version “1” should represent a specific key date in budget planning but another in forecast.
To do so, you can build your characteristic more flexibly and compound it to relevant info objects like Version. With this way you can create more master data with specific descriptions. This is useful if the used snapshot versions used are likely to be reused with the same description (e.g. in the next year)
To get the most flexibility, you can add a comment solution (document storage or comment-keyfigure) to the snapshot-version aDSO. With this solution you can describe every snapshot-version you create and even track history with timestamps if you use the same snapshot-version multiple times.
Snapshot-version implementation in SAC: Executing function integrated in Admin Workbook with status aDSO and comment key figure to track creation timestamp
Conclusion
The concept is just a simple version/scenario solution that fixates the version/scenario that is used in all objects instead of changing it with parameter tables, complex logic or even changes in queries/reporting. Besides the solution being that simple, it needs some explanation since it is contra-intuitive to keep the version/scenario constant while progressing the data.
The concept offers several advantages and can be easily implemented. Finaly, even complex requirements can be met with the addition of a status aDSO and the use of flexible descriptions.