ABAP on SAP HANA. Part V. Deep Dive into CDS Views

Share on Facebook24Share on LinkedIn37Tweet about this on TwitterShare on Google+0
Please Share!!

Deep Dive into CDS Views

In our previous post on ABAP on SAP HANA Part IV, we tried to understand why SAP came up with CDS Views when database views and other views were already there. We learned the need of Core Data Services and it’s advantages and its possible usage.

In this article, we would dive a little deeper on CDS View from an ABAPer’s perspective.

By now we know, there are two components of CDS Views in HANA.

DDL SQL View : It is read-only classical database view which is visible in ABAP Dictionary (SE11). It cannot be edited in SE11.
CDS View Entity: It is the DDL Source File and the actual CDS View. It is a Database Object which is visible in Eclipse/HANA Studio/ADT and we cannot view CDS View Entity in SE11. It covers the CDS Database view and makes other attributes possible, such as authorization checks defined in CDS view.

Before I show, how CDS View is created in HANA ADT, let me start with, how CDS View can be deleted.

Question: Do we need to delete both the Dictionary DDL SQL and CDS View individually?
Answer: No.

Question: Can we delete DDL SQL to delete the CDS View?
Answer: No.

Check the below image, I am trying to delete the DDL SQL which is created when CDS View is created.

SAP ABAP for HANA d2

HANA does not allow me to delete this independently. ‘Generated DDL SQL views cannot be deleted’.

SAP ABAP for HANA

So we are left with CDS View entity. And you guessed it right. Check the below images, we can delete CDS View entity.

Question: What happens to DDL SQL View when CDS View (DDL Source) is deleted?
Answer: They are twins. They cannot be separated even by death. 🙂

ABAP for SAP HANA

DDL SQL is automatically deleted when the CDS View is deleted. Check the image below, both are deleted in one go.

HANA Views

Also Read: Create your first program in SAP HANA Studio/ADT/Eclipse.

Now, let us see how we can create a CDS View. There are ample tutorials available on this topic. We would be short and would show what is new, in the below image.

Core Data Services

In all other tutorials, you would see that DDL Source is below Dictionary. In our image above, check it is below Core Data Services folder. HANA and SAP are evolving at great pace. We need to keep up with their pace. 🙂

CDS View 1 CDS View 2 CDS View 3

The above images are self-explanatory. Let us pause at the final step of the wizard. As of now, SAP is kind enough to provide six templates for creating the CDS View as per our need. ABAPers feared they might not be able to learn SQL and remember the syntaxes. Look, SAP already thought for us. ABAPers are not going anywhere. 🙂

In most of the tutorials on CDS View in other blogs, you might have seen only first 5 templates. You would find the sixth template “Define Table Function with Parameters” now. SAP and HANA innovation team are really fast (like their in-memory system) 🙂 . When you actually get a chance to make your hand dirty in HANA ADT, do not be surprised if you find more that 6 templates. 😛

Let us select the first template Define View and hit Finish button.

CDS View 4_1

Here system expects us to christen our DDL SQL View Name. We also need to provide the data_source_name (i.e the table or view from where data would be selected). As pointed out in the previous article, it is a good idea to separate SQL View Name and actual CDS View Name. For consistency, we name SQL View Name with DDLS and CDS View with CDSV. You might have a different naming convention in your project.

CDS View 5

For our example the SQL View Name is YDDLS_WO_STAT and CDS View is YCDSV_WO_STATUS.

Also Read: Create your first SAPUI5 and Consume Custom OData Service with NO code

Food for thought for all ABAPers
What is the maximum length of the name which we can give to the SQL View Name?

Look at the first four auto-generated lines. They precede with “@”. They are called “Annotation”.

Additional information and properties can be specified to the CDS Views using Annotations. For example @ClientDependent annotation lets us set whether the CDS View is Client Dependent or not. In the above example, it is client dependent (by default). Annotations also can be used for specifying the Buffer Status (switched on/off) and Buffer Type (single/generic/fully) of the CDS View.

Annotations enrich the data models with additional (domain specific) metadata.

In layman’s words, Annotations are extensions of CDS to SQL.

Annotation “AbapCatalog.sqlViewName” is mandatory for the definition of a Core Data Services (CDS) view.

Also, check the Outline window section in the left side corner. It shows the CDS views breakups. source data table / view, CDS View key and field list.

Now, let us try to open the CDS View entity in SE11.

CDS View 6

Oops, it is not meant for Data Dictionary.

Open the DDL SQL CDS View in SE11.

CDS View 7

No problem to view it. We can even display the data pulled by the view.

CDS View 8

Also Read: Know SAP HANA Studio/ADT/Eclipse icons/buttons.

Transports for CDS View

Ok, while creating the CDS View, it asked for the transport where we wanted to save our generated objects. What do you think, did both DDL SQL View and CDS View entity get saved in that transport? Or do you think otherwise?

Let us check it for ourselves.

Transports in CDS View

You can see, only the CDS View entity is saved in the transport.
All change objects and transports are managed in the ABAP layer end to end. We do not need to go to the lower underlying database (HDB) level to manage the transport of CDS entities/artifacts.

Join in CDS View

While creating the new CDS View, let us select the Define View with Join template. As discussed, we need to type our ABAP Dictionary (DDL) SQL View name. In addition, we need to replace the auto-generated data_source_name and joined_data_source_name along with its element names.

Join in CDS View 1 Join in CDS View 2 Join in CDS View 3

For our example, we have joined the Status table and Status text. Join is the same as we have been doing in ABAP.

Check the output of the Joined CDS View.

Join in CDS View 4

Parameters in CDS View

ABAPers are familiar with the term Parameter. Just like we can have PARAMETERs in the selection screen of a report, similarly we can have Parameters on CDS Views. Do not be too optimistic, we do not have SELECT OPTION in CDS View till now. 🙂

We know, Parameter helps to filter the data and is useful in WHERE Clause.

CDS View with Parameters is normally created to filter the data during selection process at database level itself (i.e additional filtration is done using CDS View with Parameters). So, there is no need to put additional filtering (where condition) at ABAP Layer. Code to Data shift (one of the motivations of Core Data Services). 🙂

Let us see how we can define a CDS View with Parameter.

CDS View with Paramter 2

Choose the template Define View with Parameters and provide the DDL SQL View name (data dictionary) and data source name as done in above examples. In addition to that, provide the parameter name and parameter type. We can have multiple parameters in a CDS View, separated by a comma.

CDS View with Paramter 4_1

Check the usage of parameters in the above image. If we define CDS View with multiple parameters separated by a comma, we can have multiple parameters in the WHERE Clause separated by AND/OR. Also, note that “$” sign needs to be provided with parameters while using it in WHERE Clause.

Les us see the DDL SQL View (data dictionary) for this CDS View with Parameter and try to display the content output for this CDS View.

CDS View with Paramter 5

Opps. Data display for views with parameters is not yet supported. 🙂 Hopefully, SAP would come up with this option too in near future.

There are other templates like View with Association, Extend View and Table Function with Parameters. We can cover them some other day. If you cannot wait, please check this external link. This has the exhaustive demonstration of different CDS Views and its capabilities.

You might also like to check Debugging in SAP HANA Studio/ADT/Eclipse.

Usage of CDS View in ABAP Programs

The last thing we want to cover today is how to consume a CDS View in ABAP Program.

We can use the CDS View like any other table or data dictionary view in ABAP. I found the usage of CDS View with Parameters little tricky. Please check the below code snippet for usage of CDS View with Parameters. Let me accept up front that the below program does not show the real power of CDS View. It is only for demonstration. 🙂

You would notice below that “@” symbol is used for escaping of host variables. They help to identify ABAP work areas/variables/constants in Open SQL statement. Literals need not be escaped using “@”. If we decide to escape one host variable, all host variables should be escaped.

Also, we can select from both DDL SQL View and CDS View. So, we need to declare the internal tables/work areas according to the View you intend to use. Although DDL SQL View and CDS View are mirror images still you cannot use the TYPE statement interchangeably in the program.

Also Read: End to End Config and Implementation of Two Fiori Apps with NO code

Question: In our previous article, we suggested that SE11 Data Dictionary DDL SQL View should not be normally used. Why?
Answer: If we consume DDL SQL View in ABAP SELECT statement, then, it will act as any other normal view/table which is created in data dictionary using SE11. We would not be taking real advantage of HANA. We would not see the performance improvement. Theoretically, when the DDL SQL View is used, a database connection from ABAP Layer to Database Layer is established and this process would consume some resources for database connection (even though your database in HANA). 🙂

Question: Why is it good practice to use CDS View Entity (DDL Source) while using ABAP SELECT statement?
Answer: By now we have a fair idea that CDS View Entity (DDL Source) is a database object which is known to ABAP Layer and does not exist in data dictionary (SE11). This database object contains SQL power and resides at the database layer. Consumption of CDS View by DDL Source name invokes Database Object which is residing at Database Layer i.e., SQL inside the DDL Source Name at Database layer (DDL Source). This way, we can execute an SQL without creating a database connection between ABAP Layer and Database. Only results will be transferred back to ABAP layer. This will save resources for creating a database connection from ABAP Layer to Database Layer.

I would like to request HANA Experts to provide some more insight and justification of using CDS View Entity (DDL Source) in SELECTs.

Finally, the program to show usage of CDS View with Parameter.

Please check this video from abap channel. This video speaks about CDS Views with Parameters in more detail.

Next Post: SAP HANA ABAP Training. Part VI. New Age Open SQL ABAP 740

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Thank you very much for your time!!

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial
9. SAPUI5 Tutorial

Call for Guest Authors and Contributors to write SAP Articles on our page and get noticed and also receive cool Gifts.

Do you have any tips or tricks to share? Do you want to write some articles at SAPYard? Please REGISTER and start posting and sharing your knowledge to the SAP world and get connected to your readers. Please check our simple guidelines for contributing your articles and receiving the gifts.

Share on Facebook24Share on LinkedIn37Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

SAP Yard
SAP Yard
SAPYard is one stop page for all Technical Folks in SAP. You would find un-conventional explanations, tutorials, and tricks. Please like our Facebook Page and also join our LinkedIn Group.

14 Comments on "ABAP on SAP HANA. Part V. Deep Dive into CDS Views"

  1. Hi,
    Finally I’m able to create CDS view with HANA studio. Its not working with Eclipse Neon version as well. Looking forward for more posts on ABAP HANA and HANA

    • Dear Venky – Thank you very much for the update. Glad you made it work.

      For the benefit of all readers, can you please share, how did you resolve the issue? Which Eclipse version did you use now?

      Regards,
      Team SAPYard.

      • With even latest Eclipse version, I was able to just create CDS view, but not able to code and execute. So, I downloaded and installed “ADT & HANA Studio & SAP UI5” zip folder. Finally it worked with this.

  2. Hi,
    I’m not able to create CDS view, through the procedure given above. Error is “Development object type is not supported in the corresponding SAP system”. Please help me reslove the error

    • Dear Venky,
      Seems it is some issue with your system. At which EhP level is your system?

      At what step mentioned in the post are you getting the error?

      Regards,
      Team SAPYard.

      • Ya, it was problem with my system. After using another system, I am able to create, but I’m not able to code in the CDS view created, as it says: “A request to ABAP backend could not be processed”. Even after installing the updates, its giving same error. I’m using Eclipse Neon. Please help me.

  3. Bryce DeSimone | November 16, 2016 at 7:40 pm | Reply

    Why are CDS view select statements executed faster in an ABAP report than the same select statement on a database table? For example: select * from cds_view as compared to select * from dbtabdiffer in execution time even if both data sets that are being returned contain the exact same data.

    • Dear Bryce,

      We would request HANA/ABAP experts to throw some more light on your question.

      Our explanation and understanding is:

      When we use the normal table, a database connection from ABAP Layer to Database Layer is established and this process would consume some resources for database connection.

      Whereas CDS View is a database entity. This database entity contains new SQL power and resides at the database layer. Consumption of CDS View by DDL Source name invokes Database Object which is residing at Database Layer i.e., SQL inside the DDL Source Name at Database layer (DDL Source). This way, we can execute an SQL without creating a database connection between ABAP Layer and Database. Only results will be transferred back to ABAP layer. This will save resources for creating a database connection from ABAP Layer to Database Layer.

      Hence CDS View is better.

      Regards,
      Team SAPYard.

      • Bryce DeSimone | November 21, 2016 at 5:20 pm | Reply

        Thank you for the reply! That seems to be the consensus from the research I did. The performance comparisons heavily favored CDS Views, and I’m excited about the performance benefits that are possible with this new technology.

        • You are Welcome Bryce. Please keep visiting and sharing your thoughts.

          If you have any article to publish, please let us know. We would be happy to publish your research.

          Regards,
          Team SAPYard.

  4. Thanq….Team…It’s Nice Article to learn ABAP Hana

  5. Hello Raju,

    I question is not related to HANA, but in the ABAP example I found the event END-OF-SELECTION event. My concern is why to use this event in ABAP 7.40? Why not move the code to START-OF-SELECTION? What would be difference? Though this is a basic question, could you please clarify on this?

    Thanks.

    • Dear Sharath – Thank you for your message. In our example there is no real advantage of the event END-OF-SELECTION. We have written it just to make it more readable and let our young readers know that selection is over and now it time to display. If you move to START-OF-SELECTION, there is not difference in this case.

      Regards,
      Team SAPYard.

Leave a Reply to venky Cancel reply

Your email address will not be published.


*