Purpose
This blog explains how the Cardinality of Association in ABAP CDS View works.
Association is used to link a view (or table) to the source view (or table) in CDS View. In Association, Cardinality is defined. Cardinality is the relationship between the source and associated view in the form of [ min .. max ] (only the target cardinality is stated). When Sales Order is the source and Sales Organization is the target, the cardinality is [0..1]. This blog explains how the internal behavior is changed by the setting of Cardinality in Association.
Summary:
- Joins and path expressions along associations can duplicate records of the result set of a query, if the join cardinality is >1.
- In the CDS View (A), a table (or CDS View) (B) is used as the source and a table (or CDS View) (C) is associated to B. In case [0..1] or [1..1] is used in the association, when no attributes of C are used in SELECT Statement, JOIN to C is not happening and C is not accessed internally. It could optimize the response time.
- If relationship between B and C is 1 : N, as JOIN does not happen, in the result set of the SELECT statement, records from B is not duplicated.
- In case [0..*] or [1..*] is used in the association, JOIN happens and C is accessed internally, even when no attributes of C are used in SELECT Statement. As a result, the runtime would be longer and records from B would be duplicated if the relationship between B and C is 1:N,
- When the relationship of the source and joined view (or table) is 1 : N, if LEFT OUTER JOIN is used in the CDS View, JOIN happens, but if LEFT OUTER TO ONE JOIN is used, JOIN does not happen. When the relationship is N:1, JOIN does not happen even if LEFT OUTER JOIN is used.
- When [0..*] or [1..*] is used in Association, a field of the associated view (or table) cannot be used in WHERE condition and cannot be added using Extend View either.
Cardinality of association:
Cardinality is the relationship between the source and associated CDS View (or table) , included in the definition of the association as [min..max].
To avoid undefined and platform-dependent behavior, the cardinality should always be defined to match the data in question.
The specified cardinality is evaluated by the syntax check for paths specified in the CDS DDL of CDS or in ABAP SQL. A non-matching cardinality usually produces a syntax check warning. For an association using the default foreign key (see below) the target cardinality must be [ 0..1 ] or [ 1..1].
The CDS compiler technically only distinguishes the following cardinalities:
◈ to-zero-or-one, which is specified as [ 0..1 ] or [ 1 ] or by omitting cardinality specification
◈ to-exactly-one, which is specified as [ 1..1 ]
◈ to-many, which is specified as [] or [ * ] or [ n..* ]
Providing more detailed information (like [ 2..3 ]) is possible, but the CDS compiler does not make use of it. It is simply a possibility for the developer to document specific knowledge about the relationship.
Normally association works like LEFT OUTER JOIN internally. If INNER JOIN should be used, [inner] should be used in the fields added, e.g. _VB[inner].DOCB. In this case, associated view is accessed in any cases.
Test cases:
Test1 Cardinality Test:
In the CDS View ZI_CDSVAB01, table ZDOCB is associated to the source table ZDOCA using association. Association key is DOCA.
In each test case, the cardinality of the association is changed and SELECT statement is executed to the CDS View ZI_CDSVAB01. In this SELECT statement, only the fields in ZDOCA are included.
Cardinality of association is changed in each case. 1) [0..1], 2) [1..1], 3) [0..*], 4) [1..*].
Source Table:
Table ZDOCA
@EndUserText.label : ‘Document A’
@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table zdoca {
key doca : abap.char(10) not null;
ca : abap.char(3);
ma1 : abap.dec(17,3);
}
Table ZDOCB
@EndUserText.label : ‘Document B’
@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table zdocb {
key docb : abap.char(10) not null;
key doca : abap.char(10) not null;
cb : abap.char(3);
mb1 : abap.dec(17,3);
cky : abap.cuky;
@Semantics.amount.currencyCode : ‘zdocb.cky2’
mb2 : abap.curr(17,2);
cky2 : abap.cuky;
}
CDS View: ZI_CDSVAB01
@AbapCatalog.sqlViewName: ‘ZICDSVAB01’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘TEST’
define view ZI_CDSVAB01 as select from zdoca as _VA
association [0..1] to zdocb as _VB on _VA.doca = _VB.doca
{
_VA.doca,
_VA.ca,
_VB.docb,
_VB.cb,
@DefaultAggregation: #SUM
_VA.ma1,
@DefaultAggregation: #SUM
_VB.mb1,
}
Data Preview of ZI_CDSVAB01:
SELECT statement:
SELECT
ZI_CDSVAB01~DOCA,
sum( ZI_CDSVAB01~MA1 ) as MA1
FROM
ZI_CDSVAB01
GROUP BY
ZI_CDSVAB01~DOCA
Test 2 LEFT OUTER JOIN Test:
In this test, tables are joined using LEFT OUTER JOIN instead of association. In case 1), ZDOCA is the source and ZDOCB is joined (ZI_CDSVAB02) , and in case 3), LEFT OUTER TO NE JOIN is used ((ZI_CDSVAB03). In case 3), ZDOCB is the source and ZDOCA is joined (ZI_CDSVAB04).
Source of 1) ZI_CDSVAB02:
@AbapCatalog.sqlViewName: ‘ZICDSVAB02’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘TEST’
define view ZI_CDSVAB02 as select from zdoca as _VA
left outer join zdocb as _VB on _VA.doca = _VB.doca
{
_VA.doca,
_VB.docb,
_VA.ca,
_VB.cb,
@DefaultAggregation: #SUM
_VA.ma1,
@DefaultAggregation: #SUM
_VB.mb1
}
SELECT Statement for ZI_CDSVAB02:
SELECT
ZI_CDSVAB02~DOCA,
sum( ZI_CDSVAB02~MA1 ) as MA1
FROM
ZI_CDSVAB02
GROUP BY
ZI_CDSVAB02~DOCA
Source of 2) ZI_CDSVAB03:
@AbapCatalog.sqlViewName: ‘ZICDSVAB03’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘TEST’
define view ZI_CDSVAB03 as select from zdoca as _VA
left outer to one join zdocb as _VB on _VA.doca = _VB.doca
{
_VA.doca,
_VB.docb,
_VA.ca,
_VB.cb,
@DefaultAggregation: #SUM
_VA.ma1,
@DefaultAggregation: #SUM
_VB.mb1
}
SELECT Statement for ZI_CDSVAB03:
SELECT
ZI_CDSVAB03~DOCA,
sum( ZI_CDSVAB03~MA1 ) as MA1
FROM
ZI_CDSVAB03
GROUP BY
ZI_CDSVAB03~DOCA
Source of 3) ZI_CDSVAB04:
@AbapCatalog.sqlViewName: ‘ZICDSVAB04’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘TEST’
define view ZI_CDSVAB04 as select from zdocb as _Vb
left outer join zdoca as _Va on _Va.doca = _Vb.doca
{
_Va.doca,
_Vb.docb,
_Va.ca,
_Vb.cb,
@DefaultAggregation: #SUM
_Va.ma1,
@DefaultAggregation: #SUM
_Vb.mb1
}
SELECT Statement for ZI_CDSVAB04:
SELECT
ZI_CDSVAB04~DOCB,
sum( ZI_CDSVAB04~MB1 ) as MB1
FROM
ZI_CDSVAB04
GROUP BY
ZI_CDSVAB04~DOCB
Environment:
AS ABAP 7.52 FPS00
HANA Release 2.00.021.00.15
Test results:
Test 1 Cardinality Test:
Case 1 association [0..1]
Executing SELECT statement:
Values are not duplicated.
Explain:
(Take SQL trace with Transaction ST05 and display the trace. After aggregating the statement with the menu > Trace > Structure-Identical Statement, select the SELECT statement to access the CDS View, and run Edit > Display Execution Plan > For Recorded Statement.)
Only ZDOCA is accessed.
Case 2 association [1..1]
Executing SELECT statement:
Values are not duplicated.
Explain:
Only ZDOCA is accessed.
Case3 association [0..*]
Executing SELECT statement:
Values are duplicated.
Explain:
ZDOCA and ZDOCB are accessed.
Case4 association [1..*]
Executing SELECT statement:
Values are duplicated.
Explain:
ZDOCA and ZDOCB are accessed.
Test 2 LEFT OUTER JOIN Test:
Case 1 Source : Target = N:1 / LEFT OUTER JOIN
Executing SELECT statement:
JOIN happens even when no attributes from the joined table ZDOCB are included in SELECT statement.
Values are duplicated.
Explain:
ZDOCA and ZDOCB are accessed.
Case 2 Source : Target = N:1 / LEFT OUTER TO ONE JOIN
Executing SELECT statement:
JOIN does NOT happen when no attributes from the joined table ZDOCB are included in SELECT statement.
Values are not duplicated.
Explain:
Only ZDOCA is accessed.
Case 3 Source : Target = 1:N / LEFT OUTER JOIN
Executing SELECT statement:
JOIN does NOT happen when no attributes are used from the joined table ZDOCA in SELECT statement.
Explain:
Only ZDOCB is accessed.
Others:
When cardinality is [n..*], it is not possible to use a field in the associated view (or table) in WHERE condition.
And it is not possible to add a field of associated view (or table) in Extend View.
Awesome post presented by you..your writing style is fabulous and keep update with your blogs.
ReplyDeleteMachine Learning Training in Hyderabad