Relationship in CDS Association

[Former Member]

Hi,

Could you help me understand the relationship defined in association in CDS views is same as cardinality?

For Example, what does [0..1] and [1..1] mean? How is this different from cardniality in a join in HANA view?

association [0..1] to Z_I_Material as _Material on $projection.Material = _Material.Material association [1..1] to Z_I_Sales_Hdr as _SalesHeader on $projection.SalesDocument = _SalesHeader.SalesDocument

Thanks,

Remya

[Sean_Zhang]

Hi

if you're consuming the association in a higher level view, then [0..1] and [1..1] for association will be interpreted as LEFT OUTER MANY TO ONE JOIN in the sql statement, then it would be similar to the HANA column view's LOJ to one.

in HANA, such kind LOJ will be optimized in the runtime, e.g. if the fields is not selected, then the join will be by passed.

you could get the sql statement for your CDS by right click your CDS content->show SQL create statement in AIE.

you could also check a similar question and the comments in the bottom.

https://answers.sap.com/questions/225779/cardinality-in-cds-assocation.html?childToView=234259#answe...

Best Regards,

Sean

[Former Member]

Hi Sean,

I am bit unclear on this - Could you please help me understand the 0s & 1s in this example -

C_NonManagedPurchasingSpend1 association [0..1] to I_CompanyCode

Would it be right if I say - The target will have minimum of 0 and maximum of 1 entry for Company Code in I_CompanyCode?

Thanks & Regards,

Remya

[Sean_Zhang]

[0..1] here exactly means the target could have minimum 0 entry, and maximum 1 entry.

actually, if you could always ensure that there will be and only be one entry, then [1..1] shall be used.

however, you have to think about some rare case, e.g. is it possible that company code be blank in the C_NonManagedPurchasingSpend1? or would there be any data(in C_NonManagedPurchasingSpend1) which doesn't have an entry in the I_CompanyCode?

if the answer is YES, then [0..1] would be better.

if the answer if NO, then [1..1] shall be used, which will explain the model more clearly.

this explains the I_SalesOrderItem, which have the following.

association [1..1] to I_SalesOrder

And, all in all, the association cardinality is mainly explaining the modeling, in run time, there is no difference between [1..1] and [0..1], because both of them are interpreted as LEFT out JOIN in SQL View.(at least, it's the case right now), so you don't have to very specific in this case, since it will not introduce any runtime difference.

Yes, you could be more strict to do the modeling, since it will definitely introduce benefit to you in the future to understand the modeling.

Hope my comments help you.

Best Regards,

Sean

[UweFetzer_se38]

You already answered your question for yourself: it is the cardinality.

[0..1] there may be one or none entries

[1..1] there's exactly one entry

[Former Member]

Hi Uwe,

It do not seem to be cardinality, if it was, Order to Material should be 1..N not 0..1 right?

Let me give you the example of a standard CDS view.

CDS View Name: C_NONMNGDPURCHASINGSPEND2

select from C_NonManagedPurchasingSpend1 association [0..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode

In this case, every Purchase entry will have a company code, then how come it can be [0..1] ? Shouldn't it be [1..1]?

Looking forward to your comments.

Thanks,
Remya

[UweFetzer_se38]

I don't know your view, but in the order example the cardinality [0..1] from item to material is correct: you don't need a material number you can also enter a text item into an order.

[eddy_declercq]

If I understand it correctly the cardinality only reflects the right side, meaning in your example I_CompanyCode

Does the cardinality have any influence at all - thus providing the wrong cardinality gives different results.

Or is this cardinality for documentaion purposes only since association is by default left outer join.

Speaking of, one needs to use the inner keyword if one needs an inner join. Do you have an example of this, since I don't find any?

[horst_keller]

"Does the cardinality have any influence at all - thus providing the wrong cardinality gives different results."

Yep, on HANA there are optimizations (from 7.50 on) that can influence results. Unfortunately, especially the documented default settings can produce unexpected results. Therefore, the cardinality should reflect the real data.