Associations and Joins Decoded !!
Sequential and conventional approach has been changing into Conceptual approach. It is high time we changed our approach to more of a logical one.
In HANA, it is advised to replace “For All Entries” with “Joins”. We like obedient students while migration replaced all for all entries with inner joins!!
Have we ever wondered what actually are joins and what are the type of joins around us?
As per our common knowledge, there are three popular types of joins:
- Inner join – intersection of common entries of table
- Left outer join – all rows from the first table (left) plus the common entries from right
- Right Outer Join – all rows from the right table plus the common entries from left.
Caution: Please be sure to know the concepts of Joins and how it behaves and the expected output, before you apply Joins while replacing For All Entries in your S/4HANA migration projects.
By the way, will you be surprised, if we say there are other types of Joins in HANA?
In addition to the above 3 common joins, the below joins are also available in HANA.
- Referential Join – Smart Inner Join (data in tables/views determine the behavior of join)
- Text Join – Join between Text Table and Master Table
- Temporal Join – Join table with Time data
- Spatial Join – Join for Geo-Spatial Analysis
- Star Join – Joins Star Schema
This is just a teaser. You might want to dig deeper (try to Google it). We will try to dedicate a separate article for these unheard Joins.
But you thought, this post is about Associations and not Joins? Do not worry, we are in the right track. We just introduced you to the supporting characters of our stories. Now we would focus on our protagonist of the day – ‘The Association’.
Why do you need “Associations” when you have “Joins”?
Well, let me welcome you to the artistry of SAP.
Associations as per the dictionary meaning is “close relationship between objects”.
The meaning speaks loud and clear. We in SAP actually deals with objects and entities that are undeniable concepts which are masked and need to be revealed.
In simple words: Purchase order is not just a field, it is a business object and concept – having different attributes like Purchase order number (EBELN), its related company code (BUKRS), Creation Date (AEDAT) etc.
And we can very well relate to another entity Purchase Order item which contains Purchase Order Number (EBELN), Item Number (EBELP), and Article Number (MATNR) etc.
This established relationship between two entities is called as Association.
THE CONCEPTUAL THINKING IS ALWAYS BETTER THAN PROCEDURAL ONES!! ISN’T IT?
Drilling deeper into the concept:
Add-In Features of Associations
1. Cardinality: As we stated that Associations means relationship, now Cardinality specifies the type of relationship. It is denoted by [min .. max] of target entity and can be of types:
- [0..1] – means header has no or one target instance
- [1..1] – means header has one target instance
- [0..*] – means header has no or many instances
- [3..9] – means any number of possible instances
Points to be noted in bolds:
- DEFAULT CARDINALITY IS [0..1]
- It has no connection with the output of the association – it just aids to the optimization of the resulting JOIN.
- It is always the target cardinality!!
2. Path Expressions and Filters: With additions to joins, we can also add path expressions and Filters in associations which implicitly modifies the join condition and the ON condition. The expressions are prefixed by keyword $projection.
3. This has a big role to play with the annotation: AbapCatalog.compiler.compareFilter – associations provide us the ability to provide filter and the path expressions. In simple language with the joins (by default left outer join is used for associations), we can add filters in the joins to filter out the SQL statement. So if the query detects filters and the annotation “AbapCatalog.compiler.compareFilter” is true, it will consider the filter in the same join, if false a separate join expression is created for each annotation.
Example:
Created a CDS View ( Read in our previous article ), Zpo_item1 which is a join between ekpo and ekbe. The join condition is on ebeln and ebelp and we are selecting ebeln , ebelp and vgabe. So basically, an entity (CDS View) zpo_item1 is created which contains these fields or attributes.
Output:
Now I have created Associations from ekko to this CDS view. This states that ekko can have zero or many target instances that is line items. It just highlights the relationship.
$Projection is the keyword affix before on conditions.
is the path expression.
1. Exposing Associations – fields of associations are exposed
The output filters out the condition where ebelp = ‘00010’ which is mentioned as path expressions which explicitly states inner join to target instance _item entity.
That is the beauty. We can place an Inner Join inside the Association!
Output:
2. Unexposed Association: When the fields of Association are not exposed.
Output – Right click on the output fields ebeln, click follow association : you can actually see your association.
Thus we should move towards a broader spectrum and use Associations over Joins as required!!! And yes, Associations leads to a better understanding of OData Services created from it!
