Use Cross calculation to model BW like characteristic structure on Datasphere Analytic Models

[jaigupta]

Introduction

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.

Background

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.

         

Figure 2: SAC Restricted Cross Calculation

          This is similar to creating a selection within the BW Queries.

   

Figure 3: BW Selections

• Calculated Cross Calculation – To perform calculations like sum, multiplication etc. only on the cross-calculation elements.

Figure 4: Calculated Cross Calculation

          This is similar to creating a formulas within the BW Queries.

Figure 5: BW Formulas

• 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.

https://blogs.sap.com/2020/03/19/creating-cross-calculations-in-a-table/

https://blogs.sap.com/2020/06/14/types-of-calculations-in-cross-calculation/

Steps

1.  As a first step, I will create new story in optimized design mode.

Figure 6: Create 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.

Figure 7: Add new data to story

Figure 8: Select Existing Dataset/Model

3.  Next, I will select my test Analytic Model and add the Table widget to the story.

Figure 9: Select base Analytic Model from live DS connection

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.

Figure 11: Add Cross Calculation

5.  Now, I will click on the three dots icon and select Add calculation.

Figure 12: Create Cross Calculation Elements

6.  From the opened dialog, first, I will choose Restricted Cross Calculation.

Figure 13: Choose Restricted Cross Calculation from dropdown

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.

Figure 14: Restricted Cross Calculation Dialog

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.

Figure 15: Create Restricted Cross Calculation for EMEA

Figure 16: Create Restricted Cross Calculation for NA

Figure 17: Create Restricted Cross Calculation for AP

Figure 18: Create Restricted Cross Calculation for Total

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.

Figure 19: Create Calculated Cross Calculation Others

10. Once done, I will go to my cross-calculation and will rename it as Region, as shown below.

Figure 20: Rename Cross Calculation as Region

Figure 21: Cross Calculation Region Overview

11. The output in Table widget will look like below.

Figure 22: Cross Calculation output in Table widget

12. Further, I will add Dimension Controlling Area to do further drill down and the SAC output will change accordingly.

Figure 23: Add Controlling Area to Drill down

Figure 24: Cross Calculation output with controlling area drilldown

13. Further, I can also drag Region to section Columns, as shown below.

Figure 25: Cross Calculation in Columns

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.

Figure 26: Enable Cross Calculation chart add on

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.

Figure 27: Add Cross Calculation to chart

Figure 28: Cross Calculation elements selected for chart

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.

Figure 29: Add Cross Calculation to chart dimensions

Figure 30: Cross Calculation output in chart

Limitations

• 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

Summary

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.

Further Links

• Introducing the Analytic Model in SAP Datasphere


• Analytic Model Documentation in SAP Help Portal

Find more information and related blog posts on the topic page for SAP Data Warehouse Cloud.