Clariba website

View Original

How to use CDS Views to create custom queries with SAP Analytics Cloud for S/4HANA

Have you ever tried developing CDS Views? If you have, you may have realized that it requires some effort. Luckily for you, this post will teach you the basics of CDS Views and how to improve your abilities using this resource. Ready to begin? Then let’s start.

Since teaching from experience is a great way to simplify the learning process, we will share with you the challenges we faced during the recent Proofs of Concept (PoCs) with different customers. The difficulty that we encountered was how to implement Virtual Data Models over S/4HANA or BW/4 HANA systems, to consume the resultant data in SAP Analytics Cloud. Due to the differences in nature between HANA Calculation Views and ABAP CDS architecture, we had to innovate to find an adequate solution.

Every innovation requires a solid foundation, ours was the previous projects that we had successfully delivered in the past. We used a stacked model to implement our customers’ requirements in an easy, fast and reusable way.

Before explaining to you, step by step, how we made the execution, we need you to fully understand the basics of the CDS Views.

1. What’s a CDS view?

A CDS View is an infrastructure layer used to define semantically rich data models. Allows developers to define entity types and the relationships in-between them.

Fig. 1 CDS View characteristics

Its main features include:

  • Semantically rich data-models

  • Uses domain -specific languages

  • Completely based on SQL (has most of SQL functions)

  • Compatible with any DB

  • Native integration with SAP HANA

  • Built-in functions and code pushdown

2. CDS Annotations

Annotations are code snippets used to enable certain functions or to add component-specific metadata to a CDS view. Depending on their placement, annotations can be:

  • Header annotations (placed at the header region of the file and affect to the entire view)

  • Body annotations (placed before a line of code, and only affect that line).

Depending on which engine interprets them, annotations can be:

2.1  ABAP Annotations

This kind of annotations is evaluated by the ABAP runtime engine.  Commonly used ones include the following:

@AbapCatalog.sqlViewName: ‘Zxxx’ – Defines the SQL name of the view in the database. With this name, the table can be accessed through SE11 and SE16 transactions.

@AccessControl.AuthorizationCheck – Defines implicit access control for the CDS view.

@EndUserText.Label – Defines a short description for a field/view.

If you are interested in getting further information on ABAP annotations, you can find it here. [1]

Fig. 2 ABAP annotation example

2.2  Component Annotations

They are evaluated by a specific framework, depending on the kind of annotation (OData, Analytics, UI…). The most frequently used ones include:

@Analytics.DataCategory – Gives information to the Analytic engine on how to interpret a CDS entity.

@DefaultAggregation – Defines the aggregation method that would be used for a measure in a Cube.

@VDM.ViewType – Used for internal structuring and interpretation of CDS views.

Do you want to know more about this topic? You can find more information here. [2]

Fig. 3 Component annotation example

3. Stacked Model

We are going to show you how we structured our data model using the stacked model pattern. This pattern can be recognized by three main characteristics:

  • Reusability

  • Extendibility

  • Ease of understanding

Fig. 4 Stacked model diagram

3.1  Basic View

The basic element of the model, it is a 1:1 copy of a physical table or a view from the source system. Here we can apply filters to improve performance and rename fields for content standardization.

3.2  Cube View

They are used to combine data from different basic views (join), and they are meant to be reused. Data aggregation is performed in this layer, as well as calculations.

3.3  Query View

These are the components exposed to the application, each one specially designed with a purpose in mind. Only filters are applied to this layer.

Conclusions

Our particular case enabled us to conclude that the application of CDS Views is an easy way to implement analytic solutions using S4 and BW systems on HANA databases if you do not have Calculation Views available. By using the stacked model pattern combined with the flexibility CDS Views offer, we can provide a solution that can be extended without much effort and is sustainable in the long-term.


References

[1] https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abencds_annotations_abap.htm?file=abencds_annotations_abap.htm

[2] https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abencds_annotations_frmwrk.htm?file=abencds_annotations_frmwrk.htm

Photo by fabio on Unsplash