Part#8. CDS Views – Joins and Associations
In the previous blog posts, you could see that the CDS views are an enhancement of SQL and at real time projects, we need to join multiple table together to convert data into a meaningful information. Let’ see how it works with CDS views.
JOINS: We can use below different type of Joins within CDS view;
- Inner Join
- Left Outer join
- Right outer join
I will not talk about all the joins, they behave exactly similar way as in simple SQL concepts. Let’s see an example of Inner Join code for syntax purose;
- Create a new CDS view with ‘JOIN’ template
Add the necessary components to complete the view. Join tables SFLIGHT and SPFLI. Add airport information from table SPFLI.
- Activate and execute the view to see the data;
We saw that how a CDS view fetched data from 2 tables with the help of a Join. Now let’s think about why are we creating these CDS views? What is the purpose?
CDS view are not directly accessed by the business users rather they will be consumed by either ABAP program, Fiori apps or BI front end tools. If you have created a CDS view with JOINS on 5 different tables then this JOIN conditions will be executed every time this CDS view is triggered. Even though the business user is looking at only fields from 2 tables but the CDS view will first run the Join conditions of all 5 tables and it doesn’t matter how many fields user is looking at. This is the SQL concept with Joins but results in delayed performance to fetch the data out of CDS view in case of user looking at just the partial data. To overcome this problem, SAP did an enhancement to this SQL way of getting data and beautifully developed the concept of ‘ASSOCIATIONS’. With Associations, data will be fetched only when user want to see it. Let’s see how it works;
ASSOCIATIONS are kind of Joins to fetch data from multiple tables on Join Conditions but these are ‘JOINS ON-DEMAND’ i.e. they will only be triggered when user would access the required data which needs the Association of tables. For example, your CDS view has 4 Associations configured and user is fetching data for only 2 tables, the ASSOICATION on other 2 tables will not be triggered and the system would return the results quickly, so it enables really high turn-around time as compared to regular SQL JOINS.
Associations are defined with ‘Cardinality’. Syntax : association[<cardinality>]
Cardinality concept is not new and holds the same concept with CDS views as well. There are 4 types of Cardinality possible based on the data and relationship in the tables joined;
- 0..n or 0..*
- 1..n or 1..*
NOTE: If you are confused on what kind of association I should configure in my CDS view then you can apply a rule of thumb: ‘always use this : association. This will always trigger an OUTER join and will work in all the cases.
Let’s create a CDS view with ASSOCIATION concept.
- Create View with Association template;
- Below is the default structure you will get. As you can see the association cardinality is defined as ‘Association’. You can keep it as it is, if you are not sure what association you have to assign.
- Check the default Association name as _association_name. This can be any name of your choice but SAP has created a naming convention standard to start this name with an underscore(_) and it is always good to follow SAP standard best practices.
- Add the required data elements to complete the view. Lets take the same example we did with JOIN view.
Right click and select ‘Show SQL CREATE Statement;
As you can see, NO Join is created;
- We can add separate fields in the CDS view as well like a join and that is called Ad-Hoc Association Concept. In this scenario a join will be performed beforehand.
Check the SQL CREATE statement. Note that a ‘LEFT OUTER MANY TO ONE JOIN’ is created. The join created because we selected individual fields instead of exposing the whole Association and it is LEFT OUTER MANY TO ONE i.e. *..1 due to the fact that we configured cardinality like ASSOCIATION.
As we saw above the ASSOCIATION concept with EXPOSED and AD-HOC Associations. Now lets activate the view with Exposed Association and see how it performs a join at RUN Time.
Activate this view and hit F8 to execute;
Execute and look at highlighted – CDS View name and CARRID data AA and AZ. Also note that NO data from table SPFLI is displayed as NO join is yet performed.
Now if you want to see further Airport information for CARRID ‘AA’ from table SPFLI. right click on any row with CARRID ‘AA’ and choose ‘Follow Association’. Another pop-up window will opn, click the Association type;
Check data: Join is now performed ON-DEMAND and data only for CARRID ‘AA’ is displayed based on Association Key.
In summary, Association concept is one of the wonderful things I liked about CDS views. So intelligently, SAP developed this to save the effort in reading only the information which is required for the analytics purpose. You can feel the real difference after designing a very complex CDS view with a combination of multiple table and other CDS views. The very complex CDS view still performance great when Associations are used. In this blog post, we saw the Association concept together with Exposed and Ad-Hoc associations.
Part# 9. CDS View – OData Service : Publishing to the outer world : Learn how to create an OData service.
Follow for upcoming blog posts: Sanjeev Kumar
Hi Sanjeev Kumar ,
Nice article. Is there a way to search on an association with [1…*]. I have a scenario where we have a ListReport object page template. The ListReport has search for sales orders and the object page shows the line items. We want to have a search on the ListReport that searches through the line items for material number but just shows the sales orders.
So if we have
VBAK (sales order header table – where most of the List report searches are except for the material search)
VBAP (sales order line item table) – we need to expose the field Matnr
I have an association from VBAK to_items to VBAP. But I cannot do a search VBAK with to_items/matnr because of the 1 to many cardinality.
We do not have table functions on our system so I am trying to flatten the materials at the line item level into one view that I can link as an association to the header as [0..1] – but I am having some issues with that. I was wondering if you have any experience with flatten child records to the parent level to get around this issue.
Beautifully articulated Sanjeev. Thanks very much for sharing your knowledge.
Just an observation.
I think the third one of the above mentioned cardinalities should be 1..1
Agree , it should be 1..1
Nicely written and properly explained .
This was very much needed and you have explained it very clearly. ThankYou for this blog.
I just wanted to confirm if association means cardinality [*..1]?
Very good blog!
Very nicely articulated blog post.
Thanks for sharing! Just a small observation. I think the joining column makes more sense on the connection id since it is part of the key definition on table SPFLI.
yes, or else a warning message pops up
Well explained, thanks for sharing.
cardinality often refers to the number of rows in table A that relate to table B.
That is, are there 1 row in B for every row in A then it is (1:1),
if there are N rows in B for every row in A then it is (1:N),
if there are there M rows in B for every N rows then it is A (N:M)
i don't understand why there is zero in cardinality definition [0..1 or 1..0 or 0..n or 0..* ]
Thank you for your great explanation.
I just want to understand how to decide cardinality for a specific scenario.
It will be very helpful if anyone can explain it to me.
any help with my question...
I tried to do association like this blog but there is one thing that I pretty wonder.
I made a CDS view with association and I did coding on ABAP progarmming like below.
INTO TABLE @DATA(LT_TEST).
However in here, LT_TEST has only eight column that we specified the column name on SFLIGHT table. By the way, I want to use columns on SPFLI table as well.
How can I use on ABAP programming?
Nice Explanation!! Great Job , Keep it up !
Thank you for sharing.
All the parts of this blog are beneficial, and very well explained - Thank you for sharing.
However, have a couple of questions regarding "associations" ...
1. Is there any limitation to the maximum number of associations we can use in CDS Views.
2. Kindly explain the following error message in simple words;
The association '_xyz' cannot be used locally in the view