Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
thomas_jung
Developer Advocate
Developer Advocate

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.

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:

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:

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:

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
)

};

11 Comments