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;

1. 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:

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..1
• 0..n or 0..*
• 1..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[1].  This will always trigger an OUTER join and will work in all the cases.

Let’s create a CDS view with ASSOCIATION concept.

1. Create View with Association template;

• Below is the default structure you will get. As you can see the association cardinality is defined as ‘Association[1]’. 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.

1. Add the required data elements to complete the view. Lets take the same example we did with JOIN view.

-EXPOSED Association

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.

-AD-HOC Association:

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[1].

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

Keep learning..

SK