Skip to Content
Technical Articles
Author's profile photo Masaaki Arai

Cardinality of Association in CDS View

Purpose of this blog:

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] (min .. max). This means if the record in which Sales Organization 1200 is there in Sales Order View, in Sales Organization View, there is up to one record (min = 0 / max=1) in which Sales Organization is 1200.

This blog explains how the internal behavior is changed by the setting of Cardinality in Association.

 

My motivation to write the blog:

Association is one of the main strong characteristics of CDS View and cardinality is set for the association. Many would wonder what it is, and I think more concrete explanation would be needed than the existing ones.

 

Summary:

  • Joins and path expressions along associations can duplicate records of the result set in the 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]. See SAP Help in detail.

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:

Test 1 Cardinality Test

Test 2 LEFT OUTER JOIN Test

 

Test 1 Cardinality Test:

Case (go to Result)

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

Result (go to Case)

Case1 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.

 

Case2 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 (go to Result):

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 ONE 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

 

Results (go to case):

Case1 Source : Target = 1:N / 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.

 

Case2 Source : Target = 1:N / 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.

See SAP Help about LEFT OUTER TO ONE in detail.

 

Case3 Source : Target = N:1 / 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.

 

Thanks for reading the blog!

Assigned Tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Subhendu Ghanty
      Subhendu Ghanty

      Very detailed and well explained article  Masaaki Arai .
      Is it possible to define right outer join using Association just by changing Cardinality ?

      Author's profile photo Kaifa IS
      Kaifa IS

      HAVE U TRY ?WHAT'S THE CONCLUSION?PLS TELL ME

      Author's profile photo Jitendra Soni
      Jitendra Soni

      Hello Masaaki Arai,

      Thanks for informative blog.

      Could you please let me know what is the reason behind always using Join when executing [0..*] or [1..*] cardinality even no field is used in the select query from associated view?

       

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Hi Jitendra,

      In my understanding, [0..*] or [1..*] is more normal behavior, and [0..1] or [1..1] is exceptional. [0..1] or [1..1] means the developer declare only one records are there on the right side so the records in the left side should not be duplicated, so the system can ignore the right side and do it for the efficiency.

      Thanks, Masa

      Author's profile photo Jitendra Soni
      Jitendra Soni

      Thanks Masaaki Arai

      As per my understanding [0..*] or [1..*] would be normal if any field is used in selection list or in where clause from associated view

      But even when no field is used from associated view and still join triggers, that i could not understand.

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      HI Jitendra,

      If relationship between left and right is 1:n, even when no fields are selected, the record might have to be multiplied intentionally, so Join has to be happening. However, when using [1..1], as the developer declare the relationship should not be 1:n, the system can skip the join process.

      Thanks, Masa

      Author's profile photo Jitendra Soni
      Jitendra Soni

      Thank you Masaaki Arai

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      Too many typos, which makes it hard to understand the essence. In Test 2 description it should be

      and in case 2), LEFT OUTER TO ONE JOIN is used

      instead of

      and in case 3), LEFT OUTER TO NE JOIN is used

      Also it is better to place Test 2 definitions right before the test results, it will be more handy to compare them.

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Thanks, Pavel.

      Corrected and try improving.

      Author's profile photo Olivier Monteau
      Olivier Monteau

      Nice Blog!

      Author's profile photo Jay Malla
      Jay Malla

      HI Masaaki Arai ,

      Nice article. Is there a way to search on an association with [1…*].  I have a scenario where we have a ListReport object page template.  The ListReport has search for sales orders and the object page shows the line items.  We want to have a search on the ListReport that searches through the line items for material number but just shows the sales orders.

      So if we have

      VBAK (sales order header table – where most of the List report searches are except for the material search)

      VBAP (sales order line item table) – we need to expose the field Matnr

       

      I have an association from VBAK to_items to VBAP.  But I cannot do a search  VBAK  with to_items/matnr because of the 1 to many cardinality.

       

      You have mentioned this above:  

      • 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.

       

      We do not have table functions on our system so I am trying to flatten the materials at the line item level into one view that I can link as an association to the header as [0..1] – but I am having some issues with that.  I was wondering if you have any experience with flatten child records to the parent level to get around this issue.

      Thanks,

      Jay

       

       

       

       

      Author's profile photo Andrea Borgia
      Andrea Borgia

      Masaaki Arai came across this blog looking for answers to my question

      Have you ever found a case where the same CDS views would behave differently between HANA and earlier systems?

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Hi Andrea,

      Sorry, I have no experience to create CDS View on non-HANA DB.

      I think SQL Trace (Tcd ST05) would help.

      regards, Masa

      Author's profile photo Andrea Borgia
      Andrea Borgia

      The limited information ST05 gives me is the same for both cardinalities on the older system.

      I can't get more information because of this error:

      [ASE Error SQL10331][SAP][ASE ODBC Driver][Adaptive Server Enterprise]Permission denied, database NPL, owner dbo. You need the following permission(s) to run this command: SETUSER.

      Thanks anyway.

       

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Hello Andreas,

      Sorry, I could not help enogh.

      But accourding to the error messages, it would be the permission issue in ASE layer. I found some SAP Notes with the term SETUSER, ASE.

      I think it is efficient to ask help to ASE expert.

      Thanks, Masa

      Author's profile photo Andrea Borgia
      Andrea Borgia

      Thanks, this is the AS ABAP 7.52SP04 demo from SAP, I guess I'll post a question shortly.

      Author's profile photo Rohan Chavan
      Rohan Chavan

      Hi Masaaki,

      My requirement is to add a Standard Field ANLA-IZWEK (Investment Reason) to the Asset History Sheet FIORI App.

      Std%20Investment%20Reason%20Field%20required

       

      Std Field required in Filter n Dimension of Asset History Sheet app

      I have added quite a lot Custom Fields to FIORI Apps using CFL, however, am clueless of how to add a Standard Field to filter Prompt & Dimensions of a Standard FIORI App.

      Would really be obliged if you help throw some light on this.

      Also, huge thanks for your very informative blog. Kudos!

      Kind Regards,
      Nida Patankar

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Hi Nida,

      You can associate ANLA to the source of Asset History with Extend View in the source of Asset balance.

      https://blogs.sap.com/2018/08/22/sample-custom-cds-view-using-predefined-virtual-data-model-and-extend-view-in-s4hana/

      or you can create wrapping view on top of the source of asset history VDM and associate ANLA in it.

      https://blogs.sap.com/2020/09/24/analytics-on-universal-journal-the-heart-of-sap-s-4hana/#How%20to%20add%20fields%20to%20VDMs?

      Thanks, Masa

      Author's profile photo Takayuki Sawada
      Takayuki Sawada

      詳しい解説ありがとうございます。

      本テーマと直接関係ないのですが、CDS Viewに関する質問です。

      アドオンで作ったCDS ViewにFioriなどではなくSAP GUIからアクセスする

      場合、一番ベストなやり方は何になるでしょうか?

      SAPクエリで試してみたのですが、SAPクエリでインフォセットにCDS Viewを

      割り当てた場合、アクセスコントロール(DCL)が効きませんでした。

      ABAPでCDS Viewをアクセスする記述をすれば、アクセスコントロールが使われる

      ことが確認できましたが、アドオンを各CDSビューごとに作ることは避けて今までの

      クエリ的なツールでGUIも対応したいと考えています。

      アドバイス頂けると幸いです。

      Author's profile photo Masaaki Arai
      Masaaki Arai
      Blog Post Author

      Sawada-san

      CDS ViewはABAPプログラム内でSELECT文でアクセスが可能です。

      SAPクエリでは、ご指摘の通り、SQL Viewからのアクセスとなり、アクセスコントルールはききません。

      ベンダ的回答で恐縮ですが、SAPとしては、S4HANAのレポートは、Fioriベースのアプリで実現頂く事を推奨しています。例えば以下があります。

      https://launchpad.support.sap.com/#/notes/2579584

      (言い方を変えると、その方向で開発投資が行われている、という事になります。)

      色々な状況があるかと思いますが、私個人もそれを推奨したく思います。

      SAPGUIメニューをFiori Launchpadで利用する事については、以下をご確認下さい。

      https://launchpad.support.sap.com/#/notes/2441101

       

      Author's profile photo Sneha Veerabhadrappa Dugani
      Sneha Veerabhadrappa Dugani

      Awesome blog . Very well explained. This helped me a lot . Thanks a Million Masaaki Arai.