TL;DR: In this Blogpost we will look at some data modeling methods to be used in ABAP programs. While other methods are more intuitive and easy to learn, the preferred method for experienced ABAP developers will usually be CDS Views.
Development in ABAP, a programming language used for ERP purposes, will unavoidably intersect with the task of data modeling. While the ABAP application itself contains the processing logic of the data, the modeling can be done in various ways, either within or outside the ABAP code. This article is aimed at new developers that have yet to see the many options for creating data models to be consumed by ABAP programs. It is not intended to provide a detailed explanation of each modeling method, but rather to provide a quick overview of their usage and capabilities.
Data modeling is the task of gathering data from various sources and combining and processing it into more concise data for the end user. As different data sources are often used in a similar context for different tasks, creating reusable models that can be consumed by different applications becomes a routine task. Reusable data modeling in ABAP-based programs is done using views. Like database tables, views are consumed in ABAP programs using Open SQL calls. In the following, we will be discussing the ABAP Dictionary View, the CDS View and the HANA exclusive Calculation View.
ABAP Dictionary View
The view most developers will be introduced to first is the ABAP Dictionary View. It is created with the ABAP Dictionary (SE11). It provides the option to choose several database tables (1) and collect them into a new table by choosing the desired columns (2). This is done by providing the shared columns (Join Conditions) of the underlying tables (3) and simple selection conditions (4). It does the bare minimum of data modeling; choosing which data should be displayed in a single table. The SQL equivalent of this is an inner JOIN. If your goal is to simply queue a few columns of related tables to be consumed by your ABAP code, the dictionary view is an easy way to do so.
While the ABAP Dictionary has some additional features like choosing the buffering type and inheriting field labels from its underlying data sources, any further processing of the data and supplementing technical information will have to be done in the ABAP program itself, either through lengthy SQL calls on the view or the ABAP List Viewer. The goal of data modeling is to reuse as much of the model as possible without having to code for each program individually. This makes the dictionary view the least efficient option but the easiest to understand and access since it is a simplified standard tool of the SAP GUI. It does not require any additional program or coding experience but has the fewest options.
CDS View
With the introduction of the powerful HANA databases, SAP developed views, designed for pushing computational tasks from the application layer (the ABAP program) to the database. These new views provide a lot more SQL features that not only improve performance but were also attractive for modeling purposes. But these Core Data Services (CDS) Views are not exclusive to HANA. A generic version, ABAP CDS, can be used database-independently and still provides most features of the HANA-specific CDS Views. They have a crucial role in modern SAP architecture. SAP S/4HANA uses models specifically built around CDS Views e.g., building entire Fiori Apps with the Restful ABAP Programming model. Even today, CDS is further evolving with quality-of-life improvements like CDS View Entities, a new, advanced CDS View type that provides more features and removed some redundancies of CDS Views.
Creating and maintaining CDS Views is only possible with the ABAP Development Tools (ADT) accessible through Eclipse, but viewing them is still possible with the SAP GUI. They can be consumed by any ABAP program with Open SQL.
Modeling and features
So, what are these features, and what does a CDS View look like? An example can be seen in the image below. The first noticeable difference to ABAP dictionary views is: It is code-based. Every detail about the view is written down in a new line of code instead of clicking through menus and buttons. This is not limited to just the content of the view but also its technical properties. Let us start by looking at the core of a basic CDS View. You provide the source tables, the columns to be displayed, optional aliases, and a filtering condition for the data to be retrieved. One might say that it is just an SQL code. Because it is.
As with SQL in ABAP programs, CDS Views include features like expressions and functions. The example below shows how a CDS View defines a new column (1), whose value (2) depends on a more complex logical case expression (3) using data (4) from the underlying source tables. Arithmetic expressions (additions, divisions, etc.) and functions (rounding, modulo function, etc.), string functions (substring, word length, etc.), aggregations, and many other SQL features are, of course, also possible.
Annotations
CDS features are not limited to the capabilities of SQL. The View can be enriched with semantic information using annotations. Annotations are simply expressions that define a property of the entire view or one of its elements, preceded by an “@“. Examples for both can be seen in the image below:
- Setting buffering options for the view.
- Specifying access control for when an ABAP program calls the view.
- Defining the relationship between a column containing a value and the column containing the corresponding unit or currency.
These make up only a very small fraction of all annotations. The possibilities range from general technical information to Framework-specific annotations e.g., how a User Interface should display an element of the view. They allow the Backend Developer to design entire SAP Fiori App UIs with relative ease. The image below showcases example annotations on the TravelID and AgencyID columns of the classical SAP ‘Travel Scenario’ to define some UI properties without any frontend coding:
@UI.lineItemto add the element to the list view of entries@UI.Identificationto add the element to the object page of a selected entry@UI.SelectionFieldto add a search field for the element to the UI
Some additional features
Listing all the capabilities of CDS views would take too long and is not in the scope of this simple introduction. But to finish off, here are a few further interesting capabilities of CDS:
- CDS Views can define input parameters that the consuming ABAP program can then provide.
- They allow you to define which data should be accessible to which user roles. Before, this had to be done within the ABAP program itself (statement ‘
AUTHORITY-CHECK’). - CDS Views can be enhanced. For example, a customer can create an Extention View (same structure and syntax) that adds further columns and semantic information to the view.
- CDS Views can use other CDS Views as data sources. This is not possible with ABAP dictionary views.
- Building views is not limited to just consuming database tables on the system. CDS Views can for example, queue data remotely through OData services.
- A new, advanced type of relationship between data sources, called ‘Associations’. Compared to Joins, they can provide further information, like the cardinality between data sources. Furthermore, they act like a ‘Join on demand’, meaning the view can just show the ‘main’ data source and only joins the additional sources, if they are requested.
Calculation View
As SAP HANA completely changed the capabilities of databases, the design principles for previous programs and services became obsolete and inefficient. The goal of HANA is to push as much workload as possible away from the application layer to the database. To do this, SAP created a dedicated view called HANA Views, the most prominent one being Calculation Views. Both CDS Views and Calculation Views are designed to create the data model and all its logic on the database and send the results to the consuming program. CDS HANA Views are handled on the application layer but generate the actual model on the database, while Calculation Views are handled directly on the database and then consumed by the program. A more important difference for the developer is the modeling.
Modeling
The most glaring difference is that it is graphical. For most purposes, modeling of calculation views can be done almost exclusively without any code. Like with dictionary views, you set properties by clicking through menus, but it comes with a more intuitive graphical interface. Calculation Views can only be created, edited, and viewed in HANA Studio (deprecated) and SAP Web IDE (new versions).
Each modeling step is done with a node. To get an overview of the modeling, let us look at the image below, which shows a simple case of joining two data sources. The first layer consists of two Projection nodes. Each of them selects columns from one data source to be projected to the next layer. On the second layer, a Join node combines the data sources. By navigating to the Join menu, the Join Condition and additional details like cardinality and Join type are set. On the next layer, the data is aggregated; only the sum, maximum, minimum, etc. of grouped entries is displayed by carefully defining the aggregation order and conditions. At the top level is always the Semantic node that is used to define the behaviour and meaning of each column of the final view to be consumed. Similar to annotations of CDS Views, the Semantic node can be used to provide a large variety of technical information to individual columns, like currency codes, labels, data types, data masking, and many more.
While modeling Calculation Views can take some time to learn simply due to their many features, an advantage over CDS Views is that they require no coding experience. Learning it can be more intuitive, making it especially attractive for data modelers who are not necessarily ABAP developers.
Another possible advantage of calculation views can be considered by looking at the image below. Convoluted models with many Join and processing steps could be confusing to work with. Having the entire model displayed in one diagram could be easier to follow than an equivalent script-based model. To further simplify this, calculation views allow the modeler to follow a column at higher node levels to track back to its origin.
Some last points
The features provided by Calculation Views are numerous. Like with CDS Views, entire books are written about their capabilities. This article is merely a short overview but some further noteworthy points regarding Calculation Views:
- Like with CDS Views, parameters can be defined.
- Calculation Views can use other Calculation Views.
- You can provide access control at the database level through so-called Analytic Privileges.
- More complex features, that are not covered in the graphical model, can be supplemented with Functions and Procedures, reusable blocks of SQLScript code, that can be consumed in any Calculation View and even be used directly in ABAP programs using special methods called ABAP managed Database Procedures (recommended) or by creating a proxy object for the procedure (outdated).
- HANA Views fully utilize the capabilities of SAP HANA. They are designed to get the best performance out of its in-memory technology but also have full access to the integrated Application Function Libraries, a series of C++ procedures designed to handle complex computations with great performance. Note that the usage of each library (Business Function Library, Predictive Analysis Library, Automated Predictive Library, Extended Machine Learning Library) requires its own license.
- During the early phases of HANA Views, Analytic Views and Attribute Views were used to handle the measures (quantities) and the corresponding dimensions (elements describing the measures like customers, products etc.), respectively. These were later integrated into Calculation Views and are now obsolete. Also deprecated is the fully scripted Calculation View.
A disadvantage of Calculation Views is that they cannot be used with Open SQL. For an ABAP program to consume them, it will require an intermediate step. Some of those are:
- A Native SQL interface like ABAP Database Connectivity (ADBC) must be called within the program.
- Creating a Proxy Object for the view, an External View, which can be consumed with Open SQL.
- Consume the Calculation View in a CDS View using a CDS Table Function, then consume the CDS View in the ABAP program.
- While Calculation Views can be used by ABAP programs, they are not managed by ABAP and thus require a separate lifecycle.
Summary
Let us summarize the advantages and disadvantages of each option:
- ABAP Dictionary Views are easy to understand and available as a standard tool in the SAP GUI, but provide very few options. Further modeling requirements will unavoidably have to be done in the ABAP program. Good for beginners who only want simple Joins.
- CDS Views have far more features that not only provide more capabilities but are also more comfortable to use once one is used to them. They are perfectly usable with ABAP but require ADT and knowledge of SQL-sublanguages (DDL, DCL and DML) to code them properly. Good for experienced data modelers that want to use the full scope of modeling features in ABAP programs.
- Calculation Views are a code-free alternative to CDS Views regarding its features, but they can be only used with HANA databases. It requires the use of the SAP Web IDE, and consumption in ABAP code needs an intermediate step. Good for data modelers who are not experienced in ABAP and CDS or simply prefer graphical over scripted modeling.
References:
1. Sap Learning Course S4D430
2. http://teachmehana.com/sap-hana-scripted-calculation-view-sql/
3. Sap Learning Course HA300
4. https://businessintelligist.com/2019/04/22/sap-hana-connector-tip-to-improve-performance/
