Elucidation of Associations in CDs Views

[akashkannan22]

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.