Data Modeling with CDS Views – Part 1: Basics
Introduction
This blog series contains the basics of data modeling with CDS Views. The first Part will provide some information about the Syntax of CDS views, Joins, Associations and Unions.
CDS Syntax
Just to give a very brief overview of the syntax of CDS, here I listed some basic rules:
- Names are not case-sensitive and may have a maximum number of 30 characters
- Some names are reserved and cannot be used e.g. ALL, AND, AS, ASSOCIATION, AVG, CASE, CAST, CROSS, COUNT, DISTINCT, EXISTS, EXTEND, FROM, FULL, GROUP, HAVING, INNER, JOIN, KEY, LEFT, MAX, MIN, NOT, NULL, ON, OR, ORDER, RIGHT, SELECT, SUM, UNION, VIEW, WHEN, WHERE
- Keywords must be all uppercase, all lowercase, or in lowercase with an uppercase initial letter. Other mixes not allowed.
- Comments start with // (line) or /* to */ (more lines)
Of course, there are some more syntax rules you must follow. These can be found easily on the SAP Help site.
Join
A join, like in other programming language, combines two (or more) tables. In ABAP CDS Views you can use the following join types:
- Inner Join
- Left outer [many to one] join
- Right outer [many to one] join
- Cross join
As a simple example we are going to make a left outer join between the VBAK (Sales Document Header) and the VBAP (Sales Document Position). We will add to the document number and company code from the header also the position and the material from the position table.
Within your CDS view you can do this with the following statement:
This code exists of 3 important parts.
- The statement “xxx join” à a join will be called
- The keyword “on” à the join criteria from table A to link table B (several join criteria can used of course)
- Consume the joined fields
When you execute this statement now, you receive an output where both tables are joined together.
Association
An association is a “join-on-demand”. When executing a statement, fields are only consumed when needed. Multiple associations to one base are possible.
The cardinality of an association describes the relationship from table A to table B. Determining how many records you can find in the target table minimum to maximum.
Therefore, you have various scenarios which will also lead to different results. If you are interested in this topic in detail, you can have a look at blog https://blogs.sap.com/2018/08/20/cardinality-of-association-in-cds-view/
As a simple example we are going to have a look on the sflight tables.
As you can see, this code is like a join. You need to define the key and then put your fields in the output. In the above snippet you can see that not a single field or several fields are selected – in this case the whole other table will be looked up when needed directly on the fly, as you can see in the screenshots below.
Consume “base” table:
Consume association:
Union
A union combines rows of tables to one result. The keyword to use it is UNION or UNION ALL. Without the keyword ALL the duplicates will be removed. Important when dealing with a UNION is that all selected fields of tables must match. The constant # can be assigned manually if needed.
Here you can see a simple example making a union over two Z-Tables:
Conclusion
In this blog the basic modelling concepts with ABAP CDS Views are explained. As mentioned in the introduction this blog is the start of a mini blog series. The next blog topic will be about Currency & Unit Conversion. How this can be achieved via ABAP CDS View, including an example of a date function.