Skip to Content

Core data services (CDS) is an infrastructure for defining and consuming semantically rich data models in SAP HANA. Using a a data definition language (DDL), a query language (QL), and an expression language (EL), CDS is envisioned to encompass write operations, transaction semantics, constraints, and more.

A first step toward this ultimate vision for CDS was the introduction of the hdbdd development object in SPS 06. This new development object utilized the Data Definition Language of CDS to define tables and structures. It can therefore be consider an alternative to hdbtable and hdbstructure.

In SPS 10 we continue to develop CDS with a focus on expanding the SQL feature coverage and improving complex join operations on views.

SQL Functions

In SPS 10, CDS is expanded to support almost all of the HANA SQL Functions. This greatly expands the kinds of functionality that you can build into views by formatting, calculating, or otherwise manipulating data with these functions. The following functions are the only ones not yet supported:

  • Fulltext functions
  • Window functions
  • the functions GROUPING, GROUPING_ID, and MAP in the section Miscellaneous function

Geo Spatial Types and Functions

In SPS 09, CDS first offered support for the usage of the Geo Spatial types in entity definitions. In SPS 10 we expand this support for Geo Spatial in CDS with the addition of GIS functions. This example shows how you can use the function ST_DISTANCE to calculate the distance between two geometry values. Specifically in this example we are taking the address of a business partner which is stored in the database and calculating the distance between it and Building 3 on the SAP Walldorf campus.


define view BPAddrExt as select from MD.BusinessPartner {
    PARTNERID,
    ADDRESSES.STREET   || ', ' || ADDRESSES.CITY   as FULLADDRESS,
    round( ADDRESSES.POINT.ST_DISTANCE(
                NEW ST_POINT(8.644072, 49.292910), 'meter')/1000, 1) as distFromWDF03
   };

Foreign Keys of Managed Associations in Other Associations

In the past using a managed association in a “circular” relationship where the key of entity is used in the association to another entity which in turn uses its key back to the parent would simply have resulted in an activation error. In SPS 10, the compiler now recognizes such relationships. When it sees that the referenced field is actually part of the base entity and thus can be obtained without following the association, it allows activation and doesn’t generate any additional columns in the underlying database tables.

The following is a common example of just such a Header/Item relationships:


entity Header {
  key id : Integer;
  toItems : Association[*] to Item on toItems.head.id = id;
};
entity Item {
  key id : Integer;
 head : Association[1] to Header { id };
};

Unlike a normal managed association, no additional column is generated for the association in the underlying database table. So this case it acts very much like an unmanaged association.

/wp-content/uploads/2015/07/header_737536.png

Filter Conditions

Another new features in SPS 10 is the addition of filter conditions. When following an association, it is now possible to apply a filter condition which is mixed into the ON-condition of the resulting JOIN. This adds more power and flexibility to the views you can build via CDS while also following the idea of CDS to make the definition more human readable and maintainable than the corresponding pure SQL functionality.

In this first example we apply a simple, single filter on LIFECYCLESTATUS to the Business Parnter -> Sales Order join.


view BPOrdersView as select from BusinessPartner {
  PARTNERID,
  orders[LIFECYCLESTATUS='N'].SALESORDERID as orderId
};

The resulting generated view is:

/wp-content/uploads/2015/07/view1_737609.png

Associations with filters are never combined.  Therefore in order to tell the compiler that there actually is only one association, you have to use the new prefix notation. In this example we want the LIFECYCLESTATUS filter apply to both the SALESORDERID and GROSSAMOUNT retrieval via association.


view BPOrders2View as select from BusinessPartner {
  PARTNERID,
  orders[LIFECYCLESTATUS='N'].{ SALESORDERID as orderId,
                                GROSSAMOUNT  as grossAmt }
};

The resulting generated view is:

/wp-content/uploads/2015/07/view2_737610.png

But we also see that by using the prefix notation, that such filters can be nested. This example expands on the earlier one. It still filters business partners who only have orders with LIFECYCLESTATUS = N, but now also only selects those who have ITEMS with a NETAMOUNT greater than 200.


view BPOrders3View as select from BusinessPartner {
  PARTNERID,
  orders[LIFECYCLESTATUS='N'].{ SALESORDERID as orderId,
                                GROSSAMOUNT  as grossAmt,
                                ITEMS[NETAMOUNT>200].{ PRODUCT.PRODUCTID,
                                                       NETAMOUNT }
                              }
};

The resulting generated view is:

/wp-content/uploads/2015/07/view3_737611.png

Series

The final new feature in CDS to discuss today is Series. Series data allows the measuring of data over a time where time is commonly equidistant; it allows you to detect and forecast trends in the data. You can read more about the general functionality of Series data in SAP HANA here: http://help.sap.com/hana/SAP_HANA_Series_Data_Developer_Guide_en.pdf

The major addition from the CDS side is that you can define Series data within CDS entities.  Here is a small example of the use of the series keyword:


entityMySeriesEntity{
  key setId : Integer;
  key t : UTCTimestamp;
 value : Decimal(10,4);
  series (
series key (setId)
period for series (t)         
equidistant increment by interval 0.1 second
)

};

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Holger Schäfer

    Hi Thomas,

    we just upgraded from SP93 to SP101.

    Inside CDS i have a strange issue:

    using an entity inside a context

    context Md {

         context Pd {

              @Catalog.tableType : #COLUMN

                Entity Catalog {

                    key ID : Integer;

    leads to no more be able to activate my hdbdd file!!!

    The error is on the @Catalog row:

    >Sub artifact “tableType” not found in “uniorg.bonvendo.db::Eco.Md.Pd.Catalog”

    It seems, that is is no more possible to use Entity names, that correlates with Annotation Names. For me this seems to be a bug, because the annotation should be independend from entity name. Also eclipse code completion is still working correctly and this has been working in all former releases.

    If this is not a bug, we need a blacklist of unallowed entity names.

    Maybe we should start using Z-Namespace also in HANA 😉


    Any help, how this will be treated in future directions. In the meantime i will start to rename entities to be able to use my models in SP10, but what does it mean in general?


    Regards

    Holger

    (0) 
    1. Thomas Jung Post author

      I would suggest entering a support ticket for this. Particularly if this worked in the past. I can’t find any document (internal or public) which states if this is allowed or not. I can confirm that I could create the same error in my internal system.

      (0) 
      1. Holger Schäfer

        Hi Thomas,

        FYI:

        This is the answer of thre OSS:

        The behaviour of the new version is intended: CDS name lookup is
        “inside out”, so the user-defined entity “<your
        namespace>::Md.Pd.Catalog” takes precedence over the predefined
        annotation “sap.cds::Catalog”.

        You can solve the name conflict by redefining the name of the
        annotation (we do not recommend this because it tends to cause
        confusion – if at all possible, you should rather rename your entity).
         
        namespace <your namespace>;

        // Make the predefined annotation visible under a new name
        using sap.cds::Catalog as CdsCatalog;

        context Md {
        context Pd {
        // Use the renamed annotation
        @CdsCatalog.tableType : #COLUMN
        Entity Catalog {
        key ID : Integer;
        };
        };
        };


        BTH: The Web IDE syntax highlighting does no more work for UI5 xml views, but js files work as expected. I am a little bit confused concerning quality assurance. Do not know what to expect next. I also opened UI5 tickets concerning Windows 10 support which was fixed for current BETA 1.30.7. HANA SPS101 is still using 1.28.x and currently the device fix is not downported to 1.28 branch. HAN SAPUI5 will only be updated with SPS, so the next quarter there will be no Windows 10 support (issue is, that chrome is not recognized as Desktop Device (Tablet Instead). Current workaround is using MS Edge (which is not officially not supported 😉


        Opening view.xml you can shortly see the highlighting, but then is disabled.


        Concerning quality assurance, the whole WEB IDE code is full of console.log statements (eg. plugin.js console.log(“File Extension ” + fileExtension);


        Sadly

        Holger


        (0) 
  2. Luis Becker

    Hi Thomas

    It’s good to see the improvement of CDS at each SPS, bringing more flexibility and power to this DDL.

    However, I still get confused with the topic of managed and unmanaged associations.

    I  usually just define the relationship and constraints at DB level and construct my joins with calculation views.

    But it seems that we can simplify the model if we build some of these relationships at DDL level with associations. But, as it keeps being improved and changing, I’m not sure when to use what, even after reading several times  the CDS reference of SPS 10 and your blogs with the new features for CDS on SPS 09 and 10.

    So, could you please give a brief explanation or recommend some material where we could undestand the differences of managed and unmanaged associations, to know when to use each one?

    Thanks and regards

    Luis Becker

    (0) 
    1. Thomas Jung Post author

      >So, could you please give a brief explanation or recommend some material where we could undestand the differences of managed and unmanaged associations, to know when to use each one?

      The SPS 09 what’s new material has some explanation of the different between managed and unmanaged.

      SAP HANA SPS 09: New Developer Features; Core Data Services

      One of the biggest differences is that managed associations generate additional columns in the source table for the association. Unmanaged, on the other hand, use existing columns and don’t actually generate anything in to the underlying database table. This gives you a bit more flexibility to do by-directional associations (Header <-> Item). Most of the more complex association types (Backling and many-to-many) both utilize unmanaged associations for exactly this reason.

      (0) 
      1. Luis Becker

        Hi Thomas Jung

        Still in the subject of associations (being managed or unmanaged), I’d like to see if you can confirm my understanding of what they provide and what they don’t provide:

        Supported:

        – automatic creation of joins in CDS views, when referring the association;

        – its information is read automatically when working with the entities in XSDS;

        Not supported:

        – referential constraints on DB (still must be created on runtime)

        – automatic associations on ODATA (still have to define manually associations on ODATA, even if the entity has associations in CDS)

        Not sure if any other gains obtained with associations can be mentioned.

        Can you confirm this understanding?

        Thanks and regards

        Luis Becker

        (0) 
        1. Thomas Jung Post author

          That’s a pretty good summary. For your list of what they provide I would probably add better maintenance (relationships at the entity source defined once) and simplified syntax.

          Some more details on the parts that are not support.

          Referential constraints: We discussed this recently and the prevailing feeling is that database constraints aren’t really a “good” thing. This is has been the driving reason not to implement them within CDS. The approach is that they are enforced by XSDS at the application logic level instead.  This is largely a design decision that probably won’t change.

          ODATA: This situation is completely different. We would very much like for you not to have to define entities and associations at the OData level again always and instead reuse CDS definition here. This enhancement has long been in our backlog and still high on our list of things we want to do. Hopefully this is something we can see happen in the future.

          (0) 

Leave a Reply