CDS Part 13. Key Definition in CDS Views

If you are working in S/4HANA projects, Core Data Service (CDS) should be your buddy. Of all the innovations in SAP, Core Data Service is one of the smartest. CDS overcomes the transport cumbersomeness of Information Models (HANA Views viz Attribute, Analytical, Calculation). Information Models need to be saved in Packages and then the Packages are saved in the Delivery Units. Delivery Units have to be exported from one system and imported in another system. If you have corresponding View Proxies created for the HANA Information models (views) to consume in the ABAP layer, then you need to take special care that Delivery Units are exported and imported first and then the View Proxy transport is moved.

HANA Transport Container (HTC) somehow reduced the risk of moving Delivery Units and View Proxy inconsistently. HTC is an object in ABAP Layer which can store Deliver Units of Information Models from Database. And a transport can store both HTC and View Proxy together. This way, two dependent objects can be moved together. But, the developers and the modelers still have the responsibility to save their work in the same transport.

Also, if there is any delta change in the Information Model in Database, the developer needs to synchronize and save the change in the HTC and the View Proxy in a common transport (although they can move independently).

All these complexities and dependencies are simplified with the introduction of Core Data Services. We have already shared many tutorials on CDS in our series, but still we believe, there are many more concepts in Core Data Services which we need to explore continuously.

Those of you who are familiar with CDS views would know about keyword KEY. In this post I have tried to answer some questions which you might have pondered about while using this keyword.

  • What is the actual purpose of using KEY in CDS entity?
  • How does it impact the CDS entity?
  • Does it affect the records fetched by CDS view through Open SQL?
  • What is the role of annotation AbapCatalog.preservekey with respect to KEY?

Let’s us try to raise the curtains gradually, one by one.

KEY is used to define the current element as a key element of the current CDS entity. Any elements of a SELECT list can be defined as key elements that are placed without gaps at the start of the list.

It will give syntax error “Key must be contiguous and start at the first position” if key elements are placed with gaps or not at the start of the list

The key elements of the CDS entity are evaluated as follows if they are used as data source of a SELECT statement in Open SQL:

  • By the addition ORDER BY PRIMARY KEY in select statement. In this case, the key elements must be defined at the start of the SELECT list of CDS entity without any gaps. If key is NOT mentioned, the system will derive the key fields implicitly from the key fields of the basis tables and the join conditions.
    In implicit access control
  • By default, the key elements of the CDS entity are used to document the semantics of the data model. By default, when CDS view is activated and when it is consumed by programs, the addition KEY is ignored.

This default setting can be overridden by using the view annotation AbapCatalog.preservekey.

Let’s take an example to understand this concept.

Case 1

If the annotation is NOT specified or specified with the value FALSE, the key elements defined using KEY statement in CDS entity are ignored by the SQL Database view. The key fields of the database view, like in classic views, will be derived implicitly from the key fields of the basis tables and the join conditions. If this is not possible, all the fields of the database view are key fields.

For the above CDS entity, we haven’t specified any keys/ annotation AbapCatalog.preservekey.By default, the SQL database view will derive keys from basis tables SCARR and SPFLI and the join conditions.

Now let us explicitly define KEY fields in the CDS entity without defining the annotation AbapCatalog.preservekey and check the database view again.

We can see there is no change in the key fields of the database view.

Case 2

If the value TRUE is specified for the annotation AbapCatalog.preservekey, the key elements defined using the KEY are also used as keys of the SQL database view.

We can now see all the key fields defined using KEY in CDS entity are also keys of the database view.

Note – There will be no change in the number of records fetched from the database tables in case of changing the keys of CDS entity.

Output with CDS entity keys as CARRID, CONNID and CARRNAME

Output with CDS Entity key as CARRID only-

As you can see there is no change in the number of entries in view. The logical reason being, CDS is just a view and it displays all unique entries comparing all fields which are pulled.

Core Data Service is one of the most interesting topics in SAP ABAP for HANA. We would like to hear if you have used it for any innovative solution.