Limitations of Associations in CDS views

[raveeshsaurabh3]

Hi Gurus,

I have started working with the CDS associations recently and noticed some of the limitations it has when compared to joins. One of the limitations seems to be that we can use the fields from the first data source only in all of the ON conditions if we want to use the fields from further tables in the same CDS view.

As per the above example, we cannot use _LFA1 in the path expressions in the same CDS view, as we have used $projection.lifnr in the ON condition, and this belongs to association _BSEG which is not the first data source. It would have worked perfectly fine if we had used JOINs instead of associations here. Could some one try to explain why we have this restriction on the associations?

Also If I remove the path expression and only expose the _LFA1, then the error is removed, but I get below warnings.

Can some one please explain their significance? Thanks for your help!

Warm Regards,

Raveesh

[raveeshsaurabh3]

Also, I just checked that provided cardinality does not matter. Whatever I provide [0..*], [1..1] or [0..1], it always returns the number of entries actually present in the DB. Does it mean that the cardinality is just for indicative purposes?

Thanks

Raveesh

[horst_keller]

If you are on HANA and select count(*) you might see an effect (from 7.50 or so on). That's because HANA optimizes based on the cardinality and therefore the denoted values should reflect the real data model, otherwise the result might differ.

PS: Please don't answer your own question with further questions.

[horst_keller]

$projection denotes the current SELECT list. That limits the association somewhat in order to avoid conflicts. Use the data source itself instead of $projection to get rid of the error.

The cardinality documents the data model and should be consistent all over the view and with the real data. From a recent release on, HANA evaluates the cardinality for optimizations.

[raveeshsaurabh3]

Hi Horst,

Thanks for the reply!

Even if I use the _BSEG instead of $projection , I still get an error, although a different one. The only way to remove the error is to move the BSEG to the top as the first data source, and then I can use it in the ON condition, and it works fine whether I use BSEG or $projection. This means that If I have a series of tables to be joined, for example, A -> B -> C, fields from B can not be used in the ON condition for the association between B and C, where as JOINs work perfectly fine. It looks like associations are meant to be used for star join like scenarios, where we have the fact table as the first table, and it is joined to other dimension tables.

Could you please check once.

Thanks & Warm Regards,

Raveesh