ABAP CDS-Views and SAP Analytics Cloud (SAC) via Live Connection
I would like to share my experiences with CDS-Views as a data source for Models in SAP Analytics Cloud, because I didn’t find much information about this in the forums. In use we already had the SAP HANA Calculation Views, so I’ll compare them here and there.
About the technical environment: I work with the SAP Analytics Cloud (SAC) version 2020.1.1 with direct access to a SAP NetWeaver Business Warehouse SAP (NetWeaver BW) 7.50 Support Package 16. The connection is established via the Live Data Connection.
The use case is the following:
Data from SAP Real Estate Management (SAP RE) for buildings and contracts is extracted from several SAP S/4HANA systems (also using CDS views for extraction) into the SAP NetWeaver Business Warehouse (SAP NetWeaver BW) and consolidated there. The data is stored in 2 tables, one for the buildings and one for the contracts. The data is updated daily. A virtual data model based on CDS views has been created based on these two tables.
Here is the simplified data model:
Pic1 – Data Model
The virtual data model is designed as follows (also in simplified form):
Pic2 – Virtual Data Model (CDS-Model)
Set the ‘API state’ to ‘Released’
To be able to use the Consumption View in the SAP Analytics Cloud (SAC), the following setting is necessary, see also the following note:
2595552 – Cannot access Core Data Services (CDS) View from SAP S/4HANA in SAP Analytics Cloud (SAC)
Pic3 – Release CDS-View via Public API
Test CDS Virtual Data Model in Source System
The data model of the CDS view can be tested in the system (works in a SAP S/4HANA and in a SAP NetWeaver Business Warehouse) using transaction RSRT:
Pic4 – Test CDS-Model in RSRT (1)
Pic5 – Test CDS-Model in RSRT (2)
Pic6 – Test CDS-Model in RSRT (3)
Create SAP Analytics Cloud Model based on Live Connection
Once all these prerequisites have been met, the model can be created in the SAP Analytics Cloud based on the CDS view:
Pic7 – Create Model in SAP Analytics Cloud
Pic8 – Create Model in SAP Analytics Cloud, data from datasource
Pic9 – Create Model in SAP Analytics Cloud (Live Data Connection)
Pic10 – Define CDS-Model as Datasource
All dimensions and key figures of the virtual data model are now transferred into the SAP Analytics Cloud Model:
Pic11 – Key figures in SAP Analytics Cloud-Model
Pic12 – Dimensions in SAP Analytics Cloud-Model
The story can now be built on the basis of the model created and all defined dimensions and key figures can be used.
Pic13 – Key figures in SAP Analytics Cloud-Model
Pic14 – Example CDS-based Story in SAP Analytics Cloud
Additional Key Figures with Formulas cannot be created in CDS-based Models
Different from the Calculation View based model, not much can be added to a CDS based model. If you use a Calculation View, you can create your own key figures in the model and calculate them using a formula. This is not possible in the CDS model.
Pic15 – Formula in a Calculcation View based Model
Aggregations and exception aggregations can also be defined in the CalcView model:
Pic16 – exception aggregation in a Calculcation View based Model
This is also not possible in the CDS model, these functions must be defined in the CDS-view:
Pic17 – exception Aggregation in a CDS-View
Change field descriptions only in CDS-View
Be careful when changing the field names in the model. The field names in the CDS model are generated automatically, a back reference to the field in the CDS view can be difficult. You should always use the annotation @EndUserText.label to assign field names to the model:
Pic 18 – Field Description in CDS-based-Model
Pic19 – Field description in CDS-View
Always add new key figures at the end in CDS-View
You also have to be careful when defining new key figures in the CDS View. The field names of the key figures are assigned according to the sequence in the view. When new key figures are inserted, the field names of the existing subsequent key figures change in the model. This can lead to a shift in already created stories based on the model, since the field names and not the descriptions are decisive in the story:
Here is an example. The view contains the following sequence of key figures:
Pic20 – Key figures in CDS-View (1)
1 – ELTUIDCMP1: Area Total Base
2 – ELTUIDCMP2: Area Main Base
These first two fields are found in the model as ELTUIDCMP1 AND ELTUIDCMP2, that is, the sequence as defined in the CDS view.
Pic21 – Key Figures in Model
When a new key figure is inserted, the other fields all move to the rear. If these fields are already used in a story, the field assignment automatically changes and you don’t see the field that you inserted at this point.
New key figure at position 2 in CDS view -> area_total2
Pic22 – Insert new Key Figure in CDS-View
This causes all subsequent key figures to shift, but the use in the story is not automatically corrected.
New sequence after inserting a key figure:
1 – ELTUIDCMP1: Area Total Base
2 – ELTUIDCMP2: Area Total Base 2
3 – ELTUIDCMP3: Area Main Base
Pic23 – Shifting of the key figures by inserting new key figures
Therefore, new key figures should always be added at the end of the key figures in order not to influence the sequence in the model or in the story.
Not able to create a Location Dimension in SAP Analytics Cloud Model
What is also currently not possible in the CDS-Model is to define a location dimension that can be created in a Calculation View based model to represent the data in a map. In the CalcView model this currently looks like this:
Pic25 – Create Location Dimension in CalcView based Model
Pic26 – Create Location Data in CalcView-based Model (HANA Spatial Functions)
Using CDS-Input Parameter in SAP Analytics Cloud
Input parameters of the CDS view can be used in the story. But also here it has to be considered that no additions / extensions can be made in the model.
Pic27 – CDS Input Parameter in Model
The corresponding menu option cannot be used in model maintenance. However, the input parameter can be used in the story. When the story is started, the corresponding input parameter must be specified and can be changed at any time by using a story filter.
Pic28 – Input Parameter in Story
Pic29 – Define Input Parameter as a variable (Story Filter / Prompt)
In summary, the following can be said about the use of CDS views as a basis for models in comparison to the Calculation Views in the SAP Analytics Cloud:
- Different from the CalcViews, no separate users and authorizations in SAP HANA required.
- Simplified transport management by using Change and Transport System (BC-CTS)
- Easy integration of existing roles and profiles via access controls to cover authorization requirements.
- Please note: No enhancements possible in the CDS-based model in SAP Analytics Cloud
- All calculations, restricted key figures, formulas, and so on, are performed in the
CDS view. This improves performance in the SAP Analytics Cloud.
- Creating a location dimension in the CDS model is currently not possible.
- The integration of hierarchies is easier to implement with the Calculation Views.
- CDS-View has (in addition to the SAP Analytics Cloud) further application possibilities. You can also use the CDS based Virtual Data Model with ABAP List Viewer (ALV), with SAP Analysis for Microsoft Office, with SAP Lumira, for SAP FIORI, in ABAP-Reports etc….
Finally, I would say that the use of CDS-based virtual data models is a good way to process data from a SAP S/4HANA or a SAP NetWeaver Business Warehouse via the Live Connection in a SAP Analytics Cloud. If you can work with the current constraints (e.g. no extensions in the model), it is a good alternative to the Calculation Views.
But of course, there is the possibility to combine CDS and CalcView based models in one story. In my case this worked well, e.g. the Spatial Function of the SAP HANA can be integrated by using a Calculation View in a separate model.