Association in cds view abap CDS Part 9 ABAP on HANA Course

Summary

In this video, the speaker explains the concept of associations in SAP and how they differ from joins. They demonstrate creating a CDS view using associations, replacing a left outer join, and discuss the advantages, such as lazy loading and efficiency in data modeling. The video covers cardinality between tables, ad hoc and exposed associations, and how associations work in data retrieval without forming joins until necessary. The speaker also explores SQL execution plans, showing when and how joins occur. Finally, they preview future topics like using associations in WHERE conditions and advanced cardinality scenarios.

Takeaways

• 😀 Associations in CDS views establish a relationship between the source and target entities, similar to joins but with distinct differences.
• 📊 Joins in data models form a relationship between source and target entities, but associations act as 'lazy joins,' only executing when needed.
• 📝 Cardinality in associations defines the relationship between the source and target, such as one-to-many or one-to-one relationships.
• 🔄 In this example, a sales order can have multiple line items, represented by a one-to-many (1..n) cardinality between tables VBAK (header) and VBAP (line items).
• 💡 Associations are preferred over joins because they only activate when fields from the associated target entity are accessed, optimizing performance.
• 🔍 The example replaces a left outer join with an association, demonstrating how the system understands fields from the source entity without needing specific field references.
• 📊 SAP recommends prefixing association names with an underscore (_) to easily distinguish associations from other fields in SQL queries.
• 🛠️ There are two types of associations: ad-hoc (used directly within the CDS view) and exposed (used when accessed in other CDS views or SQL queries).
• 🚀 Exposed associations allow for flexible data retrieval across CDS views, enabling access to associated data without immediately forming a join.
• 🧩 Using associations in a where condition requires additional understanding, as the join only occurs when the associated fields are accessed, referred to as 'join on demand.'

Q & A

What is an association in CDS views?

An association in CDS views represents a relationship between a source entity and a target entity, which can be a table, view, or another CDS entity. Associations are similar to joins but with key differences, like being 'lazy' or 'join on demand,' meaning they are not executed unless necessary.

How does an association differ from a join?

A key difference is that associations are 'lazy' or 'join on demand,' meaning the join will only form if fields from the target entity are accessed. In contrast, a join forms immediately when the tables are linked. This allows for optimized data retrieval in associations.

What is the importance of cardinality in an association?

Cardinality defines the relationship between the source and target entities. For example, in a 1-to-n cardinality, one record in the source entity can relate to many records in the target entity. This relationship is critical in defining how data is fetched between associated tables.

What are ad hoc and exposed associations?

Ad hoc associations are used directly in the select list or where clause, triggering a join immediately. Exposed associations are defined but not accessed, so they do not form a join unless accessed by another view or query. They are used for flexibility and deferred joins.

What happens if you don’t access fields from an association?

If you do not access any fields from the association, no join is formed, meaning the system will only fetch data from the source entity. This lazy loading of associations helps optimize performance by avoiding unnecessary joins.

Why does SAP recommend using an underscore before the association name?

SAP recommends using an underscore before the association name to easily identify that the association is being used in the CDS view. This naming convention helps differentiate between simple fields and fields coming from an association.

What is a path expression in an association?

A path expression in an association is the syntax used to access fields from the associated entity. For example, 'sourceEntity.association.field' allows you to access the 'field' from the associated target entity.

How do associations behave in a program when data is accessed?

When accessing data in a program from an association, the system forms the join only if you access fields from the associated entity. If you only retrieve data from the source entity, the join is not formed, optimizing performance.

What are the two types of association in CDS views?

The two types of association are ad hoc and exposed. Ad hoc associations are used directly in a select list or where clause, immediately forming a join. Exposed associations are defined but not used until accessed later, keeping the join deferred.

What does 'join on demand' mean in CDS views?

'Join on demand' means that the join between the source and target entities only forms when fields from the target entity are accessed. This feature of associations helps avoid unnecessary joins and improves query performance.

Outlines

00:00

👋 Introduction to Associations in SAP

The video begins with an introduction to associations in SAP. It explains how associations differ from joins, specifically left outer joins. The speaker introduces the concept of a CDS view and plans to demonstrate how associations work by replacing a left outer join with an association. The goal is to highlight how associations behave differently from joins when used in reports, particularly with sales order data.

05:01

🔗 Defining Associations and Cardinality

This section explains the fundamentals of associations, defining them as relationships between source and target entities, which can be tables, views, or entities. The speaker explains the concept of cardinality, specifically the 1-to-n relationship between sales order headers and line items, and how associations define these relationships. Associations are described as 'lazy joins,' which differ from traditional joins by executing only when needed.

10:02

💡 The Benefits of Associations Over Joins

Here, the speaker explains the flexibility of associations compared to left outer joins. They show how associations allow more efficient queries by only accessing necessary fields. Unlike joins, where specific fields must be mandatorily referenced, associations don’t require specifying the source entity in the select list. This flexibility makes associations more dynamic and reduces query overhead.

15:06

📊 SQL Query Differences: Associations vs. Joins

This paragraph compares how SQL queries are generated when using associations versus joins. The speaker demonstrates that with associations, the join is only formed when fields from the associated entity are used, unlike with left outer joins where the join always forms. Two types of associations are introduced: ad hoc and exposed associations, with recommendations on naming conventions to differentiate them from standard fields.

🔄 How Associations Are Accessed in CDS Views

The focus here is on how to use associations in CDS views and how associations behave similarly to left outer joins when data is accessed from both entities. The speaker walks through the process of accessing data from an association and how using fields from the associated entity triggers the join. This paragraph introduces the concept of 'path expressions' to access data from associated entities.

⚙️ Exposed Associations and 'Join on Demand'

This section covers the concept of exposed associations, where the join only occurs when fields from the associated entity are accessed, leading to the 'join on demand' behavior. The speaker demonstrates that if no fields from the associated entity are used, no join is formed. This behavior optimizes performance by avoiding unnecessary joins until needed.

🧪 Testing Associations in a Program

The speaker transitions to testing the association within a report program. They show that when only fields from the header table are accessed, no join is formed, confirming the lazy join behavior of associations. By enabling SQL trace, the speaker checks whether the join is formed, revealing that it is not unless fields from the line items are accessed.

🔍 Exploring Join Formation and Execution Plan

In this paragraph, the speaker demonstrates how, after accessing fields from the associated entity (line items), the join is formed. They explain how the SQL trace and execution plan show the join between the header and line item tables. The paragraph concludes by reiterating that associations allow 'join on demand' functionality, which optimizes query execution based on the data accessed.

📖 Summary and Next Steps in Understanding Associations

The video wraps up with a recap of the key points about associations, particularly how they differ from joins, their 'lazy join' nature, and how they are used in CDS views. The speaker hints at the next video, which will cover more advanced topics, including using associations in WHERE conditions and how cardinality impacts their behavior. They also encourage viewers to like, subscribe, and share the content.

Keywords

💡Association

In the context of this video, an association refers to the relationship between two data entities, such as tables or views, in SAP CDS (Core Data Services). Unlike joins, associations are 'lazy', meaning they only form relationships when necessary (on-demand). This allows for more efficient data retrieval in certain cases. Associations can be either ad-hoc or exposed, depending on their usage in a CDS view.

💡Join

A join is a SQL operation used to combine rows from two or more tables based on a related column. In the video, joins are compared to associations, which serve a similar purpose but behave differently. For example, a left outer join always forms a relationship between two tables, while an association only forms when data from the related table is explicitly accessed.

💡Cardinality

Cardinality defines the relationship between entities, specifically how many entries in one table (source) relate to entries in another table (target). In the video, the example is given of a sales order (header) and line items, where one sales order may have many line items, hence a cardinality of '1 to n'. This concept is important for understanding how associations function in SAP CDS.

💡Lazy Join

Lazy join, in the context of associations, means that the join between two entities does not occur until it is needed. The video explains that associations behave this way, only forming when data from the target entity is accessed, unlike traditional joins that form immediately. This concept highlights the efficiency of associations in certain data modeling scenarios.

💡Ad-hoc Association

An ad-hoc association refers to an association that is defined and used directly within a CDS view’s select list or where clause. It behaves similarly to a traditional join, forming when needed based on the query. In the video, this is demonstrated by selecting fields directly from the associated entity to form the join.

💡Exposed Association

An exposed association is one that is defined in a CDS view but not immediately used in the query. The join only forms when the association is accessed in another CDS view or SQL query. The video explains that this type of association is efficient because it doesn't form unnecessary joins until data from the related entity is explicitly required.

💡Path Expression

A path expression in SAP CDS is the syntax used to access fields from an association. The video illustrates this concept by showing how fields from the associated entity are accessed using the association's name followed by a dot (.) and the field name. Path expressions are crucial for retrieving data from associations.

💡Left Outer Join

A left outer join is a type of SQL join that returns all records from the left table and the matched records from the right table. In cases where there is no match, NULL values are returned for the right table's columns. The video compares this with associations, explaining how an association can be used to achieve a similar result but with potentially better performance.

💡SQL Create Statement

An SQL Create Statement in the context of the video refers to the SQL code generated when a CDS view is activated. This statement shows how the associations or joins are handled in the background. The video demonstrates this by showing how a different SQL create statement is formed when using associations versus traditional joins.

💡Where Condition

The where condition is used in SQL queries to filter data based on specific criteria. The video mentions that associations can also be used in where conditions to form joins on demand, depending on the fields accessed. This adds flexibility and efficiency in querying data from multiple entities in SAP CDS.

Highlights

Introduction to associations and how they differ from joins in SAP CDS views.

Associations are referred to as 'lazy joins' as they do not execute unless explicitly accessed.

The importance of cardinality in defining the relationship between source and target entities.

Example of replacing a left outer join with an association to improve data modeling efficiency.

Associations can be used with various entities such as tables, DDIC, CDS views, and normal views.

SAP recommends using an underscore before association names for clarity in SQL or CDS views.

Ad hoc associations are triggered when fields from the target entity are accessed in the CDS view.

Exposed associations allow associations to be exposed without forming a join unless the data is accessed.

Associations are join-on-demand, meaning they only execute when a field from the target entity is explicitly used.

Demonstration of creating a CDS view with an association, replacing a left outer join, and executing in a report program.

Understanding of SQL create statement to view native SQL query formed using associations.

Associations can form left outer joins by default, with specific scenarios triggering inner joins.

Explanation of path expressions used to access fields from associations, critical for join formation.

Utilizing ST05 trace to analyze execution plans and verify if joins are formed with associations.

Preview of the next video focusing on associations used in WHERE conditions, cardinality, and join formation specifics.

Transcripts

hello everyone welcome to sap techno

maniac in this video we will learn about

the association we'll talk what is the

association and how it is different from

the joints and what are the types of the

association

we will create one cdsu with this

association and we will try to call this

particular cds view in one of our report

program and we will see how it is

behaving different differently with the

different different kind of association

let's get started

to understand the association i will use

same cds entity which we have created in

my last video

so in my last video we we saw how left

outer join work with the too many and

two one so we we were having the short

stable as a vba k and we were doing the

left outer join with the vbap

in this video we will replace this left

outer zone with the association

then first thing what is this

association

association is the relationship between

the source

entity i can say source entity and it

can be anything like the table it can be

ddic cds view it can be cds entity it

can be normal view

anything it can be and the

target it's a relationship between the

source entity and the target entity

but the zone also doing the same thing

it's also relations between the source

and target entity but there is a

minor difference i can tell but it due

to this minor difference we can achieve

a lot of things in our

in in our data modeling i can say

so what is that minor difference the

associations are

or we can say it is

lazy zoin

so how it will work to understand this

first we will see in the system itself

so uh what i will do i will replace this

left outer join with the association so

instead of using the left outer join i

will use association to use association

you have to use association keyword then

you have to give the cardinality

so the cardinality basically it is the

relationship between the source and

target it always talks about the time

currently the relationship between the

sales order and sales of the header

table and line and line item table is

like that

the header table for one entry in the

header table that can be the end number

of entry in the line item table and

number means it can be one day entry or

it can be two three and up to n number

any number can be the

entries in the vba because one sales

order can have the n number of line item

but at least one line item will be there

in the sales order so cardinality should

so in this case it should be one to star

so it is telling that in the

how many line item can be there for one

entry in the

vba table

how many lines from in the vbap table

will be there for one entry in the vba

table so it's a cardinality defines for

the target only here not for the source

so you can see the cardinality then we

have to define

this we have to define association to

this particular vbap and we have to give

some on condition what is the condition

that same is it how we are doing in the

zone only

so i what i did

instead of using the left outer join i'm

using simple association i have given

the cardinality what is the relationship

between the source and target entity and

i have given the condition and i'm using

some of the fills from this particular

particular

table means first shows entity not i'm

not using

any fill from the target entity i'm only

using the fields from the source entity

so you can see

so one more thing i just want to add

over here if you don't use this alias

name here and you try to access this

particular sales order and kuna directly

you can access directly but in the in

case of the join we have to define

mandatory if you are using vba here so

we have to define vba dot mandatorily

but here for the

source entity it is not mandatory to use

the particular

in the control here it's mandatory but

in the select spell list we no need to

define that where we are getting from it

it is it system will understand by

default you are getting from the short

entity this particular two fields so as

of now i go to the

this particular two fields from the vba

let me activate this cds view

and let me see what will be the

code will be executed in the native form

native hana phone what is the code got

executed to create this particular uh

cds view so to see this we have to right

click and create so as

sql create statement so in this c in

this statement you can see

it's a simple s it's a simple select

like statement where we are getting the

data uh where we are getting only the

vbl and kuna it's added one mandate

client field additional and there is no

vbap table at all here but instead of

this if i would have used the inner zone

left outer zone here and i left

outer join

in this case you can see now

it's mandatory to give here we have to

give the vba k dot where it is coming

from

otherwise it is giving me error so

that's okay but now i will see this sql

create statement then you will see

the zoning

happen we we have we are not only

hitting the vba table to create this

statement we are hitting the vbap table

as well and we are creating the zone

this particular based on the sales order

but in the case of the association it

didn't happen

so

here i want to tell there is a two kind

of association

so first is the

ad hoc association and second is the

exposed association we will call this

association name is item underscore item

so sfp recommend

us to use underscore before the

association name

because whenever we will use this

particular association in another cds

view or

sql statement

then we can easily understand this is

coming from the association not from the

simple simple field list to

differentiate between them we should use

this underscore and asset recommend us

to use underscore

with the association name

so you can see uh i didn't use here any

other field

so if i want to use now this field here

control v

dot

before that let me replace again with

this association

and let me tell this association one to

start

association two with vbap

and in this case what i will do i will

get some field instead of the previously

i didn't get some filled then i saw the

sql query statement and in that case

what i was getting there there was no

vbap at all but as soon as i get some

filled from here control c

control v dot poisoner i get

as soon as as soon as i got some field

from this particular

association and if i try to see the sql

statement

it will form a zone that is laughed

after joined so you can see

most of the time association from the

left of the join there are some of some

other scenario there are the sum of

scenario which i will explain

it will form

in a zone as well we have either we have

to convert that left outer zone to inner

join and if we access this particular

cds entity in another cds directly is a

source that time also it will form in a

zone that we will see

but before seeing that first thing

normally whenever we are creating the

association it converted into the left

outer zone so obviously the relationship

between table like that one to n so it

will form the left outer zone and we

will get the some data not only from the

vba table and we will get some data from

the item table let me activate this one

control f3 and do the

affect to see the data how the data is

coming from this particular association

so you can see

uh we we go to the data header data

sales order owner and this from the

line item this is working like a zone

only if i use left outer join the same

result i will get but there is one

important thing over here

this is azo now whatever i have

explained this is called the ad hoc

association advance means we have

whatever the

association which we have defined in the

cds view which we have used in this

particular

filled list or we can use in where

condition as well that also i will

explain when to use and what is the all

are the condition to use in the where

condition this particular association

before that

if i will use

i will not use any field from this

particular association and just i will

give the association name in the filled

list

and i will activate this one and now i

will try to see this particular sql

create statement

so you can see

we we have used the association name but

we didn't access any field from this

particular association

then the zone didn't form at all so that

is the reason we call the association

join on demand

until and unless we will not access any

field from this particular association

the join will be not formed

so join how to access the uh access to

any field from the particular assertion

using the dot we have to give the path

expression whatever we will do after

this dot and blah blah blah some fill

value if you are using some other

association then field value

this particular thing called the path

expression if you read asset standard

documentation it will help sometimes we

will not understand what is the path

expression it is the whatever we are

giving after dot we are getting that

particular failed value it might be that

we have another association then another

association then the field value like

that this particular thing is called

this chain is called the path expression

this also we will see but basically

if i am not accessing this particular

field in this particular select list

the join is not forming and this this

particular association is called exposed

association we are exposing this

particular association

so that whenever this particular cdhvu

is called in some other select statement

or

in some other cdsu we can use the fields

from this particular association and as

soon as we will use the fields from this

select statement then zone will form so

that is the reason

this association is called join on

demand

so what i will do now

let me activate this one control f3

and let me execute and see the data

again

if you see we got the two data

but

in the data preview also we can still

see the associated data i want to see

the association associated data with the

this particular sales order what you

have to do you have to right click on

that and you have to click on the follow

association so how many associations are

there is up now we have only one

association if we have multiple

association it will give all the options

so one to start i just double click on

that so it will give much give me the so

11 number size order what a large the

line item is there the query will be

executed so you can see for this sales

order we have two line item

10 and 20 line items are there so i got

the data from the association itself so

let me call this particular

cdsu entity one in the my one of the

program

and see how this particular

association is behaving when we are

accessing the data from this association

and when we are not accessing the data

from this association so i have already

programmed like

which we have created in my last video

so we'll use same program

let me open it here itself

so what i will do instead of getting all

the data first i will get only sales

order and vba key

so in this case i am getting only two

field let me see

in the back end what native sql query

got executed and the the join formed or

not when i'm accessing the data from

this particular thing when we did the

left outer join the join form because

because of one to n relationship between

the table the join form if you've

watched my last video you already know

what is one to n and n to one relations

relationship when we are using the left

outer one so if the table relationship

between

the

left hand side and right hand side table

one two and and we are using the

in left outer join in that case the join

always forms but in this case we use the

association instead of using the

zoin and the relationship between the

table is 1 to n so what will happen i

and the one more important thing i am

not accessing the data from the item

table let me activate this one control

f3

and do

f9

obviously i will get the data i know i'm

not interested in the data i just want

to activate the st05 trace

and see

activate trace this with the filter

and let me give this particular program

name

and the activate the trace

and let me execute this program now

f9

and deactivate the trace

and display this particular trace

execute it

and the select this particular we have

called this program only and inside this

program we call this consumption view

and we want to see the execution plan

so you can click on that and you can see

the execution plan so you you can see

join didn't form because we didn't get

the data from the

item table but in case of laughed out as

one the join formed

but what i will do now

and

since the join didn't form

we didn't get any data from vb ap table

and for each

vba table if i do f9 again and see the

console result there is a no duplicate

entries for the sales order which we are

having the multiple line item but now

what i will do i will access the data

from this particular association how to

access the data

from the association you have to use

backwards sorry and you have to do

control space

before that the comma should be there

and there it will show you the there is

one association and you want to access

some data dot control space you can do

you have to give this path as expression

this is called path expression

ah in this case i think we have to give

date

dot will not work we have to use this

posner

i got one field from the path expression

so i am getting that particular item

increase the cardinality so it is

telling the increasing the cardinality

means previously we got one data the

identity will obviously it will get the

change let me activate control f3

let me do

f9 before doing f9 let me activate the

trace so i did f9 i got the

let me clear out the console

that will be better

uh let me

go to the st05 come back and activate

this trace again so i can see the how

the zone is happening activate trace

with filter and i will give only this

particular program name to x

activate the trace and now i will

execute this particular program i will

do f9

so you can see i got the data

uh for sales order number 11 you can see

we have multiple line item previously we

will be we were having only single line

item because join didn't form in this

case since we have access the data from

the line item table the join got formed

let me deactivate the trace

and the display trace

see the execution plan of this

particular

query

if you see this particular execution

plan let me close this console

and let me analyze little bit this one

so you can see

uh that we are the join is happening on

the vbap and we will be ak and vb ap

table and we are getting the data from

both the table

so you can see it's now join on demand

when we access the data

from

item association then only zoning is

happening otherwise there is

no joint

in this video we have learned about the

basics of association in my next video

we'll see how we can use these

associations in our where condition if

we are using in where condition what is

the prerequisite

and

when this particular association will

form in a zone instead of left outer

zoin

not only that we will talk about

cardinality as well before going to that

video please like this video subscribe

this channel and share these videos with

others as well thank you and happy

learning