SAP HANA Calculation views for delivering logic
A real game changer for delivering logic that would traditionally be delivered mainly via BW Planning functions with special focus on the planning type = formula (FOX code) is the ability to use calculation views in the solution architecture.
When using calculation views make sure you limit the development to the graphical calculation views unless your HANA license allows for extended development options.
The use of calculation views has the following benefits and considerations:
- The calculation view is visible in the production system which makes debugging much simpler because data set results can be viewed at every node of the calculation view until the final result is produced
- The calculation view is extremely fast from a performance point-of-view
- The development of calculation views using the graphical interface is very quick and easy to use and intuitive to the developer
- Because calculation views can draw data from multiple info-providers it is possible, by using parameters, to develop logic that can execute for both actuals and planning data
- Calculation views do not persist data and can lead to less data staging in planning modelling
- Calculation views do not access the planning buffer so at the start of the process that utilises a calculation view all data to be used in the view must already be persisted. However, after the calculation view has been executed the results will be added to the planning buffer and downstream functions can use the results without having to save/persist the data.
- If parameters are added to a calculation view and then added to a composite provider you will not achieve HANA push-down and the planning function will execute on the application server. (This is a current restriction of the current version at the time of writing this article and may have been lifted by the time you read it so double-check when you implement)
- Although it is possible to achieve a balance carried forward calculation in a calculation view it is not the best tool for the job and I would recommend using another method to perform balance carried forward calculations.
- Using hierarchies in a calculation view is challenging but not impossible. As a rule-of-thumb it does not generally hamper the development process but it's good to remember when viewing results that data outside of the hierarchy may be contained in the result set before it is moved to the info-provider where the filter can be used to orchestrate hierarchy data i.e. filtering on hierarchy nodes.
Using calculation views to encompass logic for both actuals and planning
Business users frequently conceptualise the scenario where logic used during the closing of the actual month-end should not be required to be re-written for planning i.e. if the ACTUALS logic applies to a single fiscal period then that same logic should simply be applied to the multiple PLANNING periods. The big idea here is that although planning may utilise forward looking rates and assumptions the basic logic or ACTUALS and PLANNING is identical and the code base should only be required to be maintained in one spot and not replicated and this is often not what happens in practice.
In the early days of SAP planning with BPS (Business Planning and Simulation) the planning layouts could be "pointed" to any info-provider. So if you designed your info-provider in a way where ACTUALS and PLANNING had the same data model you could theoretically use the same planning layout with multiple info-providers, perhaps one containing ACTUAL data and one containing PLANNING data.
Using calculation views in the solution design can now easily realise this requirement. By adding a parameter to the calculation view you can redirect the calculation views source data to any input data set.
Let's use an example to assist in the explanation: let's assume there is a headcount calculation for FTE (Full time employees) which is exactly the same for the ACTUAL month-end reporting as it is for PLANNING, however, the planning data uses multiple periods and a completely different data model to store it's transaction data than that of ACTUALS. A calculation view using a parameter to select either ACTUAL or PLAN source data can use a join/union to branch to either set of data and the structure of the calculation view below the join/union can be radically different between ACTUAL and PLAN data. However, at the point of the join/union the data is harmonised and the core logic for the FTE calculation can then be applied.
This approach requires that overall solution is designed with this architecture in mind.
Calculation views effectively eliminate the need for data staging from BW
A common practice in planning solutions is to stage BW source data (typically into an aDSO) using transformations and DTP's (data transfer process) before moving the results into the main planning info-provider.
The use of calculation views effectively eliminates the need for this design. The calculation view can access the BW source data (potentially multiple sources at once), transform it into the desired format and write it directly into the planning info-provider.
This eliminates the need for using process chains and allows the end user to execute the data initialisation process - if desired.
Using calculation views with or without parameters / prompts / variables
It is a best practice to limit the calculation view data set with parameters / prompts / variables in a similar way a filter is applied to a planning sequence. This restricts the number of records returned in the result set and the amount of memory used to stage each node in the calculation view. In most cases it should lead to better performance.
However, having said this a certain level of professional judgement should be applied to that decision bearing in mind that HANA push-down will not be achieved if the calc view is then added to a composite provider and then used with a planning function. (Note: this does not apply to queries and reporting but to planning functions) You can check if the function will not push down by using SE38 and running RSPLS_PLANNING_ON_HDB_ANALYSIS.
In other words, by not using parameters in the calculation view and adding it to a composite provider the normal planning sequence filter will still be applied to the result set and filter out the unwanted data leaving you with the result set that you are looking for. This is a simple solution and often the difference in performance and impact on the calculation view without the parameters is negligible.
Moving calculation view results into the planning info-provider
There seems to be 2 scenarios required when moving calculation view results from the calculation view into the planning info-provider:
- The calculation view result is complete, no further processing is required and the result data set simply needs to be copied from the calculation view into the planning info-provider
- The calculation view result is not complete and further processing is required.
For scenario 1:
- If no parameters have been used then a composite provider can be used with an aggregation level and either a copy function or a formula function will easily move the data from the calculation view into the info-provider.
- If parameters have been used then an AMDP is the simplest way to move this data into the info-provider. In this case a simple composite provider can be used which only contains the planning info-provider i.e. do not add the calculation view to the composite provider. The AMDP can then select data from the calculation view directly and move it to the info-provider.
In both of these cases for scenario 1 you will notice that for any sizeable solution you will want to repeat this interface many times, once for each calculation view that you want to persist the results for. It therefore stands to reason that very quickly you will want to write a standard process which can transfer any calculation view result to the info-provider.
For option 1 you will add each calculation view to the composite provider and use the technical name as a variable input to the planning sequence filter to select the correct calculation view results to transfer.
For option 2 you will create a single calculation view which contains a UNION of all underlying calculation views which each contain a new column containing an identifier which can be used to identify that calculation views data set. The identifier can then be passed as a parameter to the AMDP in order to SELECT the correct data and move it to the info-provider.
For scenario 2:
Each calculation view result set that requires further processing will require either the composite provider approach (aggregation level and planning function) or an AMDP to further process it's results as it moves the data into the info-provider.
Closing thought
If your project is not greenfields and BPC Embedded Model has been selected as the solution architecture then it is best not to "lift-and-shift" legacy systems but to take a holistic approach to the solution because of the radical change in approach using SAP HANA as a foundation.
Historically we partitioned data in various ways (e.g. key figure models vs account models) to achieve certain outcomes such as performance and data archiving but it also impacted the way we delivered business logic to the solution and tended to also be highly partitioned - many steps and functions contributing to the final result.
Using calculation views it is theoretically possible to build all the system logic into a single view and re-calculate the entire solution result in one step. It may not be probably but it is theoretically possible and the closing thought is that so long as we have this picture in mind it should reframe the way we build BPC Embedded solutions.
