Use Cross calculation to model BW like characteristic structure on Datasphere Analytic Models
As part of this blog, I will be demonstrating the approach of “How a SAP BW like characteristic structure be created on top of SAP Datasphere Analytic Model using SAP Analytics Cloud(SAC) Cross Calculation feature”. For this, we will connect SAC to Datasphere via live Datasphere(DS) connectivity and utilize optimize design story mode on top of Analytic Model for SAC story.
For demo purpose, I will use the Country dimension from my test Analytic Model to create a SAC Cross Calculation named Region with the following elements:
- Restricted Cross Calculation elements – EMEA(Europe, Middle East and Africa), NA(North America), AP(Asia Pacific) and Total. As the name suggests these elements will be used to show data for various important regions and respective total of all countries within my dataset.
- Calculated Cross Calculation element – Others. This will show the data from the countries which are not present in any of the regions created above using Restricted Cross Calculations.
Note: This feature to create cross calculation on live connections with Datasphere in SAC is available from Q3 QRC SAC onwards.
Before we start with the use case, it is better to revisit what Cross Calculations and their functionality within SAC. On a high level, below elements can be created using SAC Cross Calculation feature:
- Restricted Cross Calculation – For restricting the dimensions to certain values or ranges.
This is similar to creating a selection within the BW Queries.
- Calculated Cross Calculation – To perform calculations like sum, multiplication etc. only on the cross-calculation elements.
This is similar to creating a formulas within the BW Queries.
- Aggregation – To change the default aggregation behavior like Exception Aggregation(currently not supported with DS live connection)
Further, following blogs, provides a quick refresher on the functionality, and can be used further to understand the features in detail.
1. As a first step, I will create new story in optimized design mode.
2. Next, I will select option Add New data connection for using DS live connection, as shown below, and then select option Data from an existing dataset or Model from the pop up.
3. Next, I will select my test Analytic Model and add the Table widget to the story.
Figure 10: Add table widget
4. In the section Rows within the Builder, I will select Add Dimensions and choose Cross Calculation. This will add a Cross Calculation to the rows.
5. Now, I will click on the three dots icon and select Add calculation.
6. From the opened dialog, first, I will choose Restricted Cross Calculation.
7. This will open the new dialog which is similar to standard Restricted Measure UI in SAC. The only difference here is we can’t choose a single measure within selection context and by default the restrictions applies to all the measures within SAC instead of single measure.
8. Next, I will create Restricted Cross Calculations’ for creating regions EMEA, NA and AP as shown below. Also, I will create another element Total to get the total from all the countries. For BW users, this is similar to creating Selection within a structure.
9. Further, I will create Calculated Cross Calculation for Others. Here, I will subtract the cross-calculation element Totals with the other elements EMEA,NA and AP, created in above step using Restricted Cross Calculation, to capture data from countries which are not present in any of these regions. For BW users, this is similar to creating a formula within a structure.
10. Once done, I will go to my cross-calculation and will rename it as Region, as shown below.
11. The output in Table widget will look like below.
12. Further, I will add Dimension Controlling Area to do further drill down and the SAC output will change accordingly.
Figure 24: Cross Calculation output with controlling area drilldown
13. Further, I can also drag Region to section Columns, as shown below.
14. Also, same Cross Calculation, Region, can be used across widgets within same story. As an example, I will add it to chart by enabling the Cross Calculation from chart add-ons, as shown below.
15. Next, I will click on the label + At least 1 Cross Calculation required and select the Cross Calculation elements of the Cross Calculation required in the chart widget.
16. After that, I will add the Cross Calculation to the dimensions by clicking on label Add Dimension to see the respective data in the chart.
- Formula collision is currently not there and by default Calculated Cross Calculation will be preferred over Calculated measure in the widget.
- Exception Aggregation scenarios are not present currently within Cross Calculations
The blog explains the way a cross calculation can be created on top of Analytic Model in Datasphere via live connection in SAC.
Further, it highlights the similarity and limitations cross calculation have with Characteristic structures within BW queries.
Find more information and related blog posts on the topic page for SAP Data Warehouse Cloud.