CDS views

Table of Contents

Table of Contents 2

OVERVIEW 4

Architecture overview 4

Motivation behind Core Data Services 6

Types of views 7
Basic 7
Interface 7
Consumption 7

Data categories 8
Facts 8
Dimensions 8
AGGREGATION LEVEL 8
Cube 8

Data class 9
Master data 9
Transactional data 9
Metadata 10

CDS Releases 11

Data types in CDS ABAP and casting 14

3 Objects from classic CDS views 14

Session variables 15

Syntax basics 16

Arithmetic and operator expressions 16

Standard CDS views 17

CASE Expressions 18

Syntax overview CDS Views 19

Associations 20

Expressions in the field list 20

2
Path expressions 21

NULL in CDS ABAP and ABAP 22

SQL functions for strings 23

CDS Table Functions 24

Numeric SQL functions 25

Date and time calculation 26

CDS InfoProvider Views 28

CDS Cube Views 28
CDS Dimension Views 29
CDS Text Views 29

Parameter 30

CDS Query Views 30

Annotations at view level 31
Annotations at field level 31

CDS Extractors 32

Change Data Caption (CDC) 32

Star Schema 33

Creation of CDS views 34

3
OVERVIEW

CDS is an infrastructure layer for defining semantically rich data models,

which are represented as CDS views. In a very basic way, CDS allows
developers to define entity types (such as orders, business partners, or
products) and the semantic relationships between them, which correspond
to foreign key relationships in traditional entity relationship (ER) models. CDS
is defined using a SQL-based data definition language (DDL) that is based on
standard SQL with some additional concepts, such as associations, which
define the relationships between CDS views, and annotations, which direct
the domain-specific use of CDS artifacts. Another example is expressions,
which can be used in scenarios in which certain CDS attributes are
considered as measures to be aggregated.

Before dive deep into CDS, let's, first, define what is a VIEW?

● A view is a virtual table, which does not have any physical existence
● It is created by combining the data of one or more tables
● A view does not occupy any storage space

Architecture overview

In ABAP Development Tools, the text-based DDL editor is used to write

source code in which you specify the data definition for a new CDS view. For
each CDS view that is defined in the DDL source, you will generate – using the

4
activation process – exactly one SQL view and the corresponding CDS entity
in the ABAP Dictionary.

“Define View” templates:

5
Motivation behind Core Data Services

● Semantically Rich data-models: Entity relationship model and is

declarative in nature, very close to conceptual concept.Domain specific
languages (DDL, QL, DCL). Declarative, close to conceptual thinking.
● CDS is completely based on SQL: Any ‘Standard SQL’ features are
directly available like joins, build-in functions,..
● Fully Compatible across any DB: CDS is generated into managed Open
SQL views and is integrated into SAP HANA layer. These views are
supported by all major DB.
● Support for Annotations: CDS syntax supports domain-specific
annotations that can be easily evaluated by other components, such as
the UI, analytics, and OData services.
○ @AnalyticsDetails.aggregrationBehaviour
○ SUM()
○ Substring() [SQL functions]
● Associations: Simplified definition of views on top of views. Path
expressions to navigate along relations.

6
● Extensibility: We can extend SAP-defined CDS views with fields that will
be automatically added to the CDS view along with its usage hierarchy.
○ On model level through extensions.
○ On meta-model level through annotations.

CDS entities and their metadata are extensible and optimally integrated into
the ABAP Data Dictionary and the ABAP language.

Types of views

Basic
These are used to bring data directly from the physical tables in the
database.

Interface
This type of view is used to link basic views together through associations
(like SQL joins between views). They are then used by the consumption views.

Consumption
These views allow you to create a specific query from a “composite” view. It
can be consumed for reporting virtual data model. It gives information to the
Analytic engine on how to interpret a CDS entity, it can be either (dimension,
fact, cube).

7
Data categories

Facts
Hold the values we can measure and are extracted based on the filters. They generally
represent transactional data.

Dimensions
These are the master data like date, customer, and person responsible. They are generally
the attributes of the master data.

AGGREGATION LEVEL

The view using data category as aggregation level indicates that CDS view is

of type projection. In this type of view associations are not allowed and

elements can be renamed.

Cube

When we connect all the FACT and DIMENSIONS, we come upon CUBES

which represent the factual data. They are used to combine data from

different basic views (join), and they are meant to be reused.

8
Data class

Master data
It’s the core data that is absolutely essential for running operations within a
business enterprise or unit. It is data about key business entities that
provides context for business transactions and operations.

It describes people (customers, employees, and suppliers), places (offices and

locations), and things (products and assets).

Transactional data
It’s the data that is generated by various applications while running or
supporting everyday business processes.

Transaction data are business documents which are created using master

data ie. sales orders.

● Data relating to the day-to-day transactions.

● Transactional Data is not constant and can be changed quite often.
● Example of Transaction data: production details, daily transactions etc.

The two data types are best exemplified in the following sentence:

9
Within this, the buyer and the product are comprised of master data, as they
are at the heart of the transaction; without them, the transaction process
would not exist. The secondary data generated as a result of this interaction
falls under transactional data (such as the amount, the date, the quantity
purchased, the invoice number, or tax identifiers).

Metadata

“A set of data that describes and gives information about other data.”

Metadata helps us understand the structure, nature, and context of the data.

The most common metadata fields are:

● Title
● Description
● Tags or categories
● Date of creation
● Who created it
● Source or origin
● Count and description of variables or attributes (e.g. rows*columns count for a
data table)

10
Here are some common metadata fields in a spreadsheet:

File name, Tab name, Column names, Number of rows, Number of columns,

Comments.

CDS Releases

Below is an overview of changes in CDS functionalities with the ABAP specific

releases.

11
12
13
Data types in CDS ABAP and casting

In the CDS entities you can refer to the data elements of the Data Dictionary
or directly to the ABAP data types. The data types must always fit exactly,
otherwise there will be error messages when activating the CDS ABAP object.
The SQL function CAST is used to convert the data types:

CAST( <expression> AS <datatype> [PRESERVING TYPE])

The ABAP data types are specified with abap.<datatype>, for example

● abap.int4
● abap.char(<length>)
● abap.dec(<length>, <decimal places>)

Depending on the data type, the <length> and possibly also the number of
<decimal places> must be specified in parentheses. If the data type of the
<expression> is technically identical to the specified data element, you can
specify the addition PRESERVING TYPE.

3 Objects from classic CDS views

For CDS views, three different objects are created in the object catalog (table
TADIR):

● DDL file - The source code file , which is created and edited with the
ADT in Eclipse. Only this object is in the object list of transport requests
(object type DDLS).

14
● CDS entity - The CDS object that represents all the properties of the
view, including annotations, associations, and permission checks. This
object is used in ABAP programs.
● SQL database view - A database view that is also displayed as a view
in DDic, but should not be used from an ABAP perspective.

The DDL file and the CDS entity usually have the same technical name. The
SQL database view must have a different name than the CDS entity, since
both are in the same namespace.

With Release 7.55, a new object type has been added to the classic CDS View,
the CDS View Entity, which has no SQL database view. It is recommended by
SAP to use these objects if you are on the appropriate system level.

Session variables

There are a handful of session variables that can be accessed in CDS ABAP.

$session.<variable_name>

So far there are the following variable names, which largely correspond to
components of the SY or SYST structure in ABAP:

● USER - The logged on ABAP user, equivalent to SY-UNAME.

● CLIENT - The client to be used for the query. Corresponds to
SY-MANDT, except for accesses with USING CLIENT or in AMDP with
AMDP OPTIONS CDS SESSION CLIENT
● SYSTEM_LANGUAGE - The logon language in internal ABAP format,
equivalent to SY-LANGU
● SYSTEM_DATE - Current system date, equivalent to SY-DATUM
● SYSTEM_TIMEZONE - Time zone of the user, equivalent to SY-ZONLO

15
● USER_DATE - Current date of the user, equivalent to SY-DATLO

Syntax basics

Keywords in CDS ABAP are either

● completely in CAPITAL LETTERS or

● completely in lowercase letters or
● the first letter capitalized and the rest lowercase

Identifiers of tables, views or columns are not case-sensitive. They may be

a maximum of 30 characters long and consist of letters, numbers,
underscore and slash.

Strings, as literals, are set in quotation marks in the source code. Numeric
literals without decimal separator are of type ABAP.INT1 up to ABAP.INT8,
depending on the value. Numeric literals with the dot as decimal separator
are of the data type ABAP.FLTP.

Comments can either start at the end of the line with a double slash // or be
placed as block comments between /* and */.

Whitespace doesn't matter much and is primarily used for formatting as

long as the semantics are clear.

Arithmetic and operator expressions

In CDS ABAP the arithmetic operators for addition (+), subtraction (-) and
multiplication (*) work as you would expect. However, the division differs
from this. Basically, a whitespace is required before and after the division
operator (/).

16
● For operator expressions with ABAP.FLTP both operands must have
this data type. This is especially disturbing since literals with decimal
places are of the type ABAP.FLTP. The only thing that helps here is a
CAST.
● Division with the division operator (/) is allowed in classic CDS views only
for floating point data types. Therefore, use the SQL function DIVISION(
<numerator>, <denumerator>, <decimal places> ), which rounds
automatically.
● For calculations that result in a decimal number, a check is made to
ensure that the result is convenient for the ABAP.DEC data type. This
means a maximum of 31 digits, 14 of which are after the decimal point.
If this is not the case, the operands must be reduced beforehand per
CAST.

Standard CDS views

SAP has provided standard CDS views for each SAP module

Note: If you choose to use a standard CDS view to meet your business
requirements, it is a best practice to duplicate it and make your
customizations in the copy. The standard views may undergo updates and
overwrite your code.

17
CASE Expressions

A CASE expression in CDS ABAP always returns exactly one value, depending
on the conditions. The simple CASE expression compares an expression to
several other expressions for equality:

The complex CASE expression (aka 'searched case') evaluates N

independent conditions. The first case to be evaluated to TRUE, returns the
result:

18
If no condition was evaluated to TRUE, either the value from the ELSE clause
or NULLis returned.

Syntax overview CDS Views

The syntax described here is largely identical for CDS Views and CDS View
Entities. There are two important differences in view entities:

● The annotation @AbapCatalog.sqlViewName is omitted

● Instead of DEFINE VIEW it is called DEFINE VIEW ENTITY

19
Associations

Associations can be used to describe the relationships between CDS entities.

They define a JOIN that is executed only, if the fields are retrieved from the
associated entity. These queries are created with so-called path expressions
and can run across multiple CDS entities.

The names of the associations always start with an underscore. They are
included in the field list and thus they are available to the users of the CDS
View.

The cardinality can be specified in square brackets: [Min..Max]. If only Max is

specified, the Min value is assumed to be 0. By default, it is [0..1]. Permitted
values are e.g. [1], [*], [1..1], [1..*] or [0..*].

Expressions in the field list

20
The field list consists of expressions. These are mostly field names of the
sources. However, there may be other elements in it as well. The following
are possible:

● Field names - If multiple tables are involved because of JOINs, the

source should always be named with a dot in front of it:
<source>.<fieldname>
● SQL Functions
● Literals
● Session variables
● Aggregate expressions
● Operator expressions
● CASE expressions
● Path expressions
● Parameter

The expressions can be nested within each other. For example, a parameter
can be used as a comparison value in a CASE expression.

The individual fields are separated by commas. If a field name is not referred
to, an alias name assigned with AS is mandatory for the field. The keyword
KEY marks the key value.

Path expressions

Path expressions can be used to include fields from associated sources in the
field list of the CDS view or query on the CDS entity. The path expressions can
also go over several levels. It is also possible to add attributes to the
evaluation of the associations, for example to filter or to define the join type:

21
In ABAP SQL

The path expressions can be used in ABAP SQL. However, the syntax is
slightly different. Before associations, and as separator between them, there
is always a backslash (\). The component is addressed (as usual in ABAP) with
a hyphen (-):

NULL in CDS ABAP and ABAP

In the database there is the pseudo-value NULL, which signalizes the absence
of a value. This is translated into an initial value in ABAP. This can lead to
unexpected situations when aggregating, since two groups are formed - one
for NULL and one for initial values in CDS ABAP - but both are initial in ABAP.

Replace NULL

You can catch NULL values with the SQL function COALESCE(<value1>,
<value2>), because then this function returns the 2nd value.

Filter with NULL

22
A normal comparison with NULL always results in the logical value
UNKNOWN. Thus, such a comparison is never valid in a WHERE condition.
That is why the predicate IS (NOT) NULL is needed.

SQL functions for strings

Be careful when using blanks at the beginning or at the end of strings (STR).
These are partially removed. A look at the documentation is essential here!

23
CDS Table Functions

CDS Table Functions are views programmed in SQLScript. They are based on
an AMDP function encapsulated by a CDS ABAP object.

24
The CDS table function defines the signature:

The correlating AMDP table function:

25
Numeric SQL functions

Abbreviations: number or numerator (<N>), denominator (<D>) and decimal

place (<DP>).

26
Date and time calculation

The functions in the CDS ABAP are the same as those available in the
OpenSQL in ABAP. The data types play a major role in the SQL functions,
which is why their abbreviation is always prefixed, e.g.:

● DATS is the popular ABAP data type, for the DB only CHAR(8)
● DATN is the SQL date format on HANA
● TIMS is the ABAP data type for time
● TIMN is the SQL time format on HANA
● TSTMP is the short timestamp, 15-digit decimal number
● TSTMPL is the long timestamp with 7 DP digits.
● UTCL corresponds to the ABAP data type UTCLONG, internally 8 bytes

The names of the CDS ABAP SQL functions start with the data type.

Conversions

● DATS_TO_DATN
● DATS_FROM_DATN
● TIMS_TO_TIMN
● TIMS_FROM_TIMN
● DATS_TIMS_TO_TSTMP
● TSTMP_TO_DATS
● TSTMP_TO_DST
● TSTMP_TO_TIMS
● TSTMPL_TO_UTCL

27
● TSTMPL_FROM_UTCL

Validity

Returns 1 if valid, otherwise 0.

● TSTMP_IS_VALID
● TIMS_IS_VALID
● DATS_IS_VALID

Addition

● DATN_ADD_DAYS
● DATN_ADD_MONTHS
● DATS_ADD_DAYS
● DATS_ADD_MONTHS
● TSTMP_ADD_SECONDS
● UTCL_ADD_SECONDS

Differences

● UTCL_SECONDS_BETWEEN
● TSTMP_SECONDS_BETWEEN
● DATS_DAYS_BETWEEN

28
● DATN_DAYS_BETWEEN

Current time

● UTCL_CURRENT
● TSTMP_CURRENT_UTCTIMESTAMP
● ABAP_SYSTEM_TIMEZONE
● ABAP_USER_TIMEZONE

CDS InfoProvider Views

CDS queries are based on CDS InfoProviders with the annotation

@Analytics.dataCateogory: [#CUBE | #DIMENSION]

They form a transient InfoProvider named 2C<SQLView Name>. They contain

the complete semantic information of the data model that the CDS queries
are to use later.

CDS Cube Views

CDS Cube Views are the basis of key figure reports. You can associate
additional CDS dimension views to enrich the attributes and texts of features.

29
CDS Dimension Views
The CDS Dimension Views provide attributes for a feature. These attributes
can be time-dependent.

CDS Text Views

The CDS Text Views provide the language-dependent texts for fields.

30
Parameter

Parameters can be defined for a CDS ABAP View, which are then used as an
expression, e.g. for calculation, as a comparison value or as a function
parameter.

Default values only work in a few frameworks:

● @Consumption.defaultValue - Constant value

● @Environment.systemField Session variables, e.g. system date

31
CDS Query Views

The source for a CDS Query View is always a CDS InfoProvider View. The CDS
ABAP Annotation @Analytics.query: true turns a view into a CDS Query View.

Annotations at view level

Annotations at field level

32
Filter with prompt

AnalyticDetails for characteristics

AnalyticDetails for key figures

CDS Extractors

33
Delta extraction with timestamp field

As with the generic delta, a delta can be formed using a timestamp field.
Optionally, a safety interval can also be specified.

Change Data Caption (CDC)

Automatic change recording is possible on systems from SAP S/4HANA 1909
FPS01. For more complex scenarios with JOINs, explicit mapping is required.
Details in the blog of Simon Kranig - a very good read.

Star Schema

34
The structure of the analytical model resembles a star. The cube or fact view

at its center is surrounded by and connected to various dimension views.

Example:Flight Booking Model

In the next example, we will implement a simple analytical model that lets us

analyze flight bookings based on the /DMO/Flight test database tables. We

will create a data model containing measures and dimensions.

Creation of CDS views

Defining the CDS view in the DDL editor

35
A CDS view is defined for existing database tables and views, or for other CDS
views in ABAP Dictionary, using the ABAP CDS statement DEFINE VIEW. A CDS
view serves to define the structure of an SQL view.

Activating CDS Views

When activating a CDS view, the following objects are created in ABAP

Dictionary:

● The actual CDS entity

● An SQL view

Note: SQL views and CDS entities are part of one and the same namespace.

Therefore, you must assign different names for an SQL view and the entity.

36
37