Association in cds view abap CDS Part 9 ABAP on HANA Course
Summary
TLDRIn 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
👋 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.
🔗 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.
💡 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.
📊 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.
Mindmap
Keywords
💡Association
💡Join
💡Cardinality
💡Lazy Join
💡Ad-hoc Association
💡Exposed Association
💡Path Expression
💡Left Outer Join
💡SQL Create Statement
💡Where Condition
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
تصفح المزيد من مقاطع الفيديو ذات الصلة
CDS View entity with join and literals Part 6 ABAP on HANA Course
Introduction to NoSQL databases
Lecture 4.2 - Association between two categorical variables - Introduction
Genome-Wide Association Study - An Explanation for Beginners
Tableau Data Modeling and Layers (Physical & Logical) | #Tableau Course #33
Emotional responses to music | Hauke Egermann | TEDxGhent
5.0 / 5 (0 votes)