Defining Associations Between CDS Views

Objectives

After completing this lesson, you will be able to:

Define associations.
Expose associations.

Associations in CDS Views

In the previous lesson you learned, that foreign key dependencies express relationships between database tables. In a similar way, associations express relationships between CDS entities.

Associations are more flexible than foreign key dependencies. A foreign key dependency always links a foreign key table to a check table. An association can link any association source to any association target. In particular, there are usually two associations between the CDS entities where there is one foreign key dependency between two database tables. Let us have a look at out example:

The definition of database table EMPLOYEE contains a foreign key dependency for the field DEPMENT_ID, using database table DEPMENT as the check table. R_Employee and R_Department are CDS views that read from these two database tables. On CDS level, the foreign key dependency corresponds to an association (relationship) with R_Employee as source and R_Department as target. It links each employee to their respective department. However, there can also be an association (relationship) in the reverse direction, linking one department to all employees that work in this department.

Associations are defined in the data definition of the CDS entity that serves as the association source. In our example, this is the definition of CDS view R_Employee. The association is defined in the FROM clause, after the data source of the view. One CDS view definition can contain any number of associations.

Note

The data model of SAP S/4HANA contains views with over one hundred associations.

One association definition consists of the following parts:

Association target: The association to keyword is followed by the name of the association target, in our example, this is CDS view R_Department. The association target should always be a CDS entity. It is possible, but not recommended, that associations in CDS views use database tables as targets.
Association Name: The association name comes after the AS addition and serves as an identifier for the association. It is recommended that an underscore (_) is used as the first character of the CDS association name. In the example, the association name is _Department.
Note
From a technical point of view, defining an association name is optional. Without the association name, the name of the association target is used as identifier for the association. This can make it difficult to read the code and makes it impossible to have more than one association for the same target. Therefore, it is strongly recommended to define an association name for all associations.
Association condition: The association condition starts with the keyword ON. Usually, it compares view elements of the association source to view elements of the association target, but other conditions are also possible. In the recommended format, the view elements of the association source are on the left, with $projection. as prefix, and the view elements of the association target are on the right, with the association name as prefix.
Note
As it is not necessary to include the client field in the element list of the CDS views, the client field does also not appear in the association condition.

Hint

When you define an association, make use of code completion (Strg + Space), to insert the keywords, the name of the association target and the view elements.

Cardinality of Associations

The cardinality of an association specifies the number of possible data sets in the association target for one data set in the association source.

In ABAP CDS, the cardinality is specified in a pair of square brackets immediately after the keyword association.

The cardinality is specified as [min..max] or just [max]. If min is omitted, the value min = 0 is used.

Allowed values for min are 0 and all positive integer numbers. The most common values for min are 0 and 1.

Allowed values for max are all positive integer numbers and the asterisk sign (*) for an unrestricted maximum number. the most common values for max are 1 and *.

Note

If you do not specify a cardinality, the default cardinality [0..1] is used.

The syntax check performs a plausibility check for the maximum value of a cardinality. For this it compares the ON-condition to the key of the association target. If the ON-condition fully qualifies the primary key, cardinality0..1 or 1..1 is expected.

In the example, cardinality 0..* for association _Department leads to a syntax warning because the association target (R_Department) has only one key field (Id) which is qualified in the ON-condition.

This example shows the association in the opposite direction. Here, cardinality 1..1 for association _Employee leads to a syntax warning because the key field EmployeeID of the association target R_Employee is not qualified in the ON-condition.

Hint

In complex data models, it can happen that a cardinality is correct, even though it does not match the ON-condition. In such a situation, you can simply ignore the warning or suppress it using a pseudo-comment.

Exposed Associations

When you define a new association, the editor displays a warning that the association is not used. There are two ways to use an association: You can either define a path expression with this association (see later) or expose it.

You expose an association, by adding the association name to the element list of the CDS view. This makes the association visible for the consumer of the view.

Note

When you expose an association, it is mandatory that all elements of the association source, used in the ON-condition, are part of the field list. This prerequisite is automatically fulfilled, when you used the $projection prefix in the ON-condition.

Exposed Associations in Data Preview

If a CDS View definition contains exposed associations, you can evaluate these associations in the Data Preview tool as follows:

Open the CDS View in Data Preview.
Choose one of the displayed rows, then click on the arrow next to the view name in the header toolbar.
Hint
Alternatively, you can right-click the entry and choose Follow Association.
Choose an association from the list of available associations.

How to Define and Expose an Association

Play the video to see how to define and expose an Association.

Define a CDS View with Associations

You define a basic CDS view entity for departments and the associations between departments and employees.

Template:

/LRN/R_EMPLOYEE_ANN (Data Definition)

Solution:

/LRN/R_DEPARTMENT_REL (Data Definition)
/LRN/R_EMPLOYEE_REL (Data Definition)

Prerequisites

For this exercise, you need the CDS view entity for employee data which you created in a previous exercise (suggested name was: Z##_R_Employee, where ## is your group number). If you have not finished that exercise, create a copy of data definition /LRN/R_EMPLOYEE_ANN.

Task 1: Define a CDS View Entity for Department Data

Define a CDS view entity for department data (suggested name: Z##_R_Department, where ## is your group number). Let the new CDS view entity read all fields from the database table that you created in the previous exercise (suggested name was: Z##DEPMENT).

Note

If you have not finished the previous exercise, you can let your CDS view entity read the database table /LRN/DEPMENT_REL.

Steps

In your own package, create a new data definition (suggested name: Z##_R_DEPARTMENT, where ## is your group number). Specify your database table as Referenced Object and choose the Define View Entity template to generate the definition statement, some standard annotations and the element list.
1. In the Project Explorer view, right-click your database table definition Z##DEPMENT to open the context menu.
2. From the context menu, choose New Data Definition.
3. Confirm that the Package field contains the name of your package and that the Referenced Object field contains the name of your database table definition.
4. In the Name field, enter the name for the CDS view entity (Z##_R_Department, where ## is your group number).
5. Enter Department (Entity) in the Description field and choose Next.
6. Confirm the transport request and choose Next.
  Caution
  Make sure you don't choose Finish yet. If you do, you are not able to choose the template that you want to use.
7. From the list of Templates, choose Define View Entity, then choose Finish.
Apply source code formatting.
1. From the eclipse menu, choose Source Code→Format. Alternatively, choose Shift + F1.
Activate the data definition.
1. Press Ctrl + F3 to activate the development object.

Task 2: Define an Association

In your basic CDS view entity for employee data (suggested name was: Z##_R_Employee), define and expose an association using your new CDS view entity for department data as target (suggested association name: _Department). Choose a cardinality that matches the cardinality used in the foreign key relationship for the DEPARTMENT_ID table field.

Steps

Open the basic data definition for employee data (suggested name was: Z##_R_EMPLOYEE).
1. Press Ctrl + Shift + A.
2. Enter Z##_R_.
3. From the list of matching items, choose Z##_R_EMPLOYEE (Data Definition) and choose Next.
Define an association to your CDS view entity for department data with association name _Department.
1. Adjust the code as follows:
  Code Snippet
```
12345

define view entity Z##_R_Employee
  as select from z##employ
  association to Z##_R_Department as _Department
```
Define the ON-condition, using the department ID field in both view entities.
Hint
Make use of the $projection. prefix to address the element of the source view entity and the_Department. prefix to address the element of the target view entity.
1. Adjust the code as follows:
  Code Snippet
```
123456

define view entity Z##_R_Employee
  as select from z##employ
  association to Z##_R_Department as _Department
    on $projection.DepartmentId = _Department.Id
```
Add a cardinality that matches the cardinality of the foreign key relationship for the DEPARTMENT_ID table field.
Note
The cardinality of the foreign key relationship is 0..*,1. The first part specifies the number employees per department. The second part expresses the number of departments per employee.
1. Adjust the code as follows:
  Code Snippet
```
123456

define view entity Z##_R_Employee
  as select from z##employ
  association[1..1] to Z##_R_Department as _Department
    on $projection.DepartmentId = _Department.Id
```
Expose the association.
1. At the end of the element list, add a comma and the association name.
2. Adjust the code as follows:
  Code Snippet
```
12345

   last_changed_at       as LastChangedAt,
    _Department
}
```
Activate the CDS data definition.
1. Press Ctrl + F3 to activate the development object.

Task 3: Define More Associations

In your basic CDS view entity for department data, define and expose three associations: an association for all employees (suggested association name: _Employee), an association for the department head (suggested name: _Head), and an association for the department assistant (suggested name: _Assistant). Choose the cardinalities based on the corresponding foreign key relationships in the underlying database tables.

Steps

Return to the data definition for department data. Define association _Employee to address all employees that are assigned to this department.
Note
Take into account that a department can have any number of employees, including zero.
1. Adjust the code as follows:
  Code Snippet
```
1234567

define view entity Z##_R_Department
  as select from z##depment
  association [0..*] to Z##_R_Employee as _Employee
    on $projection.Id = _Employee.DepartmentId
```
Define an association to address the details of the department head. Set the cardinality based on the cardinality of the foreign key relationship between the underlying database tables.
Note
In our model, it is NOT necessary that a department has a department head.
1. Adjust the code as follows:
  Code Snippet
```
123456789

define view entity Z##_R_Department
  as select from z##depment
  association [0..*] to Z##_R_Employee as _Employee
    on $projection.Id = _Employee.DepartmentId
  association [0..1] to Z##_R_Employee as _Head
    on $projection.HeadId = _Head.EmployeeId
```

Define an association to address the details of the department assistant. Set the cardinality based on the cardinality of the foreign key relationship between the underlying database tables.

Note

In our model, a department cannot exist without a department assistant.

Adjust the code as follows:

Code Snippet

1234567891011

define view entity Z##_R_Department
  as select from z##depment
  association [0..*] to Z##_R_Employee as _Employee
    on $projection.Id = _Employee.DepartmentId
  association [0..1] to Z##_R_Employee as _Head
    on $projection.HeadId = _Head.EmployeeId
  association [1..1] to Z##_R_Employee as _Assistant
    on $projection.AssistantId = _Assistant.EmployeeId

Expose the association.
1. At the end of the element list, add a comma and the three association names (comma-separated).
2. Adjust the code as follows:
  Code Snippet
```
1234567

   last_changed_at       as LastChangedAt,
    _Employee,
    _Head,
    _Assistant
}
```
Activate the CDS data definition.
1. Press Ctrl + F3 to activate the development object.

Task 4: Analyze and Use the Associations

Analyze the technical implementation of your CDS view for department data and use the associations for navigation in the Data Preview tool.

Steps

Display the SQL statement with which the technical representation of the view entity is created in the database.
1. Right-click anywhere in the source code of the data definition and choose Show SQL CREATE Statement.
Open the Data Preview for your CDS view for department data.
1. Right-click anywhere in the source code of the data definition and choose Open With→Data Preview. Alternatively, place the cursor anywhere in the source code of the data definition and press F8.
Display all employees that work for the Sales and Distribution department.
1. Right-click anywhere in the row with value SALE in the Id column and choose Follow Association. Alternatively, place the cursor anywhere in the row and press Ctrl + G.
2. Choose _Employee → Z##_R_Employee [0..*].
Return to the department list and display the assistant of the Administration department.
1. On the navigation path that is displayed above the Data Preview tab (sometimes referred to as "Breadcrumb naviation"), choose Z##_R_DEPARTMENT to return to the list of departments.
2. Right-click anywhere in the row with value ADMIN in the Id column and choose Follow Association. Alternatively, place the cursor anywhere in the row and press Ctrl + G.
3. Choose _Assistant → Z##_R_Employee [1..1].
Without returning to the list of departments, navigate to the department, this person works for and from there to the department head.
1. Right-click anywhere in the single row that is displayed and choose Follow Association.
2. Choose _Department → Z##_R_Department [1..1].
3. Right-click anywhere in the single row that is displayed and choose Follow Association.
4. Choose _Head → Z##_R_Employee [0..1].

Next lesson