This blog is part of the larger series on all new developer features in SAP HANA SPS 09:http://scn.sap.com/community/developer-center/hana/blog/2014/12/02/sap-hana-sps-09-new-developer-features

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 09 we continue to build on the foundation of CDS and the HDBDD development artifact.  We introduce important new syntax as well as significant enhancements to the lifecycle management of these development objects.

Lifecycle Management

One of the biggest requests for CDS/HDBDD was to improve the lifecycle management capabilities.  Before SPS 09 if you made most changes to an entity definition for a table which had data in it, you received an activation error. The developer was responsible for migrated data out of the existing table, dropping it, recreating with the new structure and then migrating the data back. This process was not only cumbersome and error prone but also made transporting such changes automatically to downstream systems nearly impossible.

In SPS 09 we add an automatic migration process to activation of the HDBDD artifact. This supports a variety of scenarios.

  • Conversion from the same named hdbtable artifact
  • Change of key / data type
  • Removal of columns

In these situations, data is moved transparently to a shadow table.  The source table is then dropped/adjusted and the data is migrated back. This allows for structural changes that in the past would have required significant developer work to preserve the data.  Even better is that this process happens automatically upon activation, therefore transporting content via Delivery Units will automatically trigger the migration.

Multi-File Support

Another very common request was for multiple-file support in HDBDD. Before SPS 09 you often were force into very large monolithic HDBDD artifacts if you wanted to share association or type definitions between entities or views. It also meant no way to defined global types that could be reused across the system.

SPS 09 solves all of these problems by adding the ability to import from one or more existing HDBDD files.  All the entity definitions, types, etc then become visible to the destination HDBDD artifact.

It is possible to refer to an artifact that is defined in another HDBDD file (“external” artifact). Each external artifact must explicitly be made accessible by a using declaration. The using declaration introduces a local name as an alias for the external artifact, which is identified by a fully qualified name. The external artifact can be either a single object (type, entity, view) or a context. The using declarations are located in the header of the file between the namespace declaration and the beginning of the top level artifact.

For example you might create some reusable types in a central HDBDD:


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context ContextA {
  type T1 : Integer;
  context ContextAI {
    type T2 : String(20);
    type T3 {
      a : Integer;
      b : String(88);
    };
  };
};


You can then import and reference these types in a separate HDBDD artifact.


namespace playground.sp9.cds;
using playground.sp9.cds::ContextA.T1;
using playground.sp9.cds::ContextA.ContextAI as ic;
using playground.sp9.cds::ContextA.ContextAI.T3 as ict3;
@Schema: 'SP9DEMO'
context ContextB {
  type T10 {
    a : T1;               // Integer
    b : ic.T2;            // String(20)
    c : ic.T3;            // structured
    d : type of ic.T3.b;  // String(88)
    e : ict3;             // structured
  };
  context ContextBI {
    type T1 : String(7);  // hides the T1 coming from the first using declaration
    type T2 : T1;         // String(7)
  };
};


Enumerations

Another new syntax feature in SPS 09 is the introduction of enumerations.  That’s a type which declares several possible values or domains.  However Enumerations can only be used within Annotations. The definition of annotations is very similar to type definitions. Annotation definitions can be either located inside a context, or an annotation definition can be the single top level artifact in a CDS file.  However annotations do NOT generate catalog artifacts.


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context enumerations {
    type Color : String(10) enum { red = 'FF0000'; g = '00FF00'; b = '0000FF'; };
annotation MyAnnotation {
  a : Integer;
  b : String(20);
  c : Color;
  d : Boolean;
};
};


New Types

One of the other common requests was for the CDS syntax to support all the HANA data types.  With SPS 09 we extend the supported types, including the GeoSpatial types.  Please note that the support the GeoSpatial types ST_POINT and ST_GEOMETRY is still limited.  These types can only be used for the definition of elements in types and entities.  It is not possible to define a CDS view that selects such an element from an CDS Entity.


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context NewTypes {
@nokey
entity SomeTypes {
  a : hana.ALPHANUM(10);
  b : hana.SMALLINT;
  c : hana.TINYINT;
  d : hana.SMALLDECIMAL;
  e : hana.REAL;
  h : hana.VARCHAR(10);
  i : hana.CLOB;
  j : hana.BINARY(10);
  k : hana.ST_POINT;
  l : hana.ST_GEOMETRY;
};
 
};

View Syntax

The View Syntax receives several new features as well.  For example we can now specify the OrderBy clause in the view definition:


//Order By
view EmployeesView as select from Employee
{
  orgUnit,
  salary
} order by salary desc;

We can also use the CASE syntax within the view definition:


//Case
entity MyEntity {
key id : Integer;
     a : Integer;
     color : String(1);
};
 
view MyView as select from MyEntity {
    id,
    case color 
        when 'R' then 'red'
        when 'G' then 'green'
        when 'B' then 'blue'
        else 'black'
    end as color,
    case when a < 10 then 'small'
        when 10 <= a and a < 100 then 'medium'
        else 'large'
    end as size
};

Unmanaged Associations

Associations in general are one of the most powerful features of the CDS syntax. They move the relationship definition out of the view/join and into the source entity definition for better re-usability/maintenance and simpler overall syntax.

An unmanaged associations is based on existing elements of the source and target entity. No fields are generated. In the ON condition, only elements of the source or the target entity can be used; it is not possible to use other associations. The ON condition may contain any kind of expression – all expressions supported in views should also work in the ON condition of an unmanaged association. The names in the ON condition are resolved in the scope of the source entity. Elements of the target entity are accessed via the association itself.


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context unmanagedAssociations {
entity Employee {
  key id   : String(256);
  officeId : Integer;
};
entity Room {
  key id : Integer;
  inhabitants : Association[*] to Employee on inhabitants.officeId = id;
};
 
entity Thing {
  key id   : Integer;
  parentId : Integer;
  parent   : Association[1] to Thing on parent.id = parentId;
  children : Association[*] to Thing on children.parentId = id;
};
 
};

Backlink

Unmanaged associations also make several previously unsupported features possible.  For example the backlink. The CDS specification defines backlink associations. This kind of association is not yet supported. But you can (with a little extra effort) achieve the same effect by using unmanaged associations.

According to the CDS specification, it shall be possible to define a header/item relationship like this:


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context backlink {
//Backlink syntax not yet supported
//entity Header {
//  key id : Integer;
//  items : Association[*] to Item via backlink header;
//  description : String(120);
//};
//
//entity Item {
//  key header : Association[1] to Header;
//  key id : Integer;
//  description : String(120);
//};
//
//Workaround using unmanaged associations
entity Header {
  key id : Integer;
  items : Association[*] to Item on items.headerId = id;
  description : String(120);
};
entity Item {
  key headerId : Integer;
  key id : Integer;
  header : Association[1] to Header on header.id = headerId;
  description : String(120);
};
};

Many to Many

Similarly Many to Many can also be simplified using unmanaged associations. CDS defines mediated m-to-n associations, which can be defined using the “via entity” keyword. These kinds of associations are not yet supported directly in HDBDD. But using unmanaged associations, there is a workaround.

Example: model of employees and projects, employee can be assigned to any number of projects. The link table must be modelled explicitly.


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context manyToMany {
entity Employee {
  key id : Integer;
  name : String(80);
  projectLinks : Association[*] to E2P on projectLinks.e_id = id;
};
entity Project {
  key id : Integer;
  name   : String(80);
  employeeLinks : Association[*] to E2P on employeeLinks.p_id = id;
};
entity E2P { 
  key e_id : Integer;
  key p_id : Integer;
  projects  : Association[*] to Project  on projects.id  = p_id;
  employees : Association[*] to Employee on employees.id = e_id;
}; 
view EmployeesWithProjects as select from Employee {
     name                       as EmployeeName,
     projectLinks.projects.id   as projectId,
     projectLinks.projects.name as projectName
  };
   
view ProjectsWithEmployees as select from Project {
     name                         as projectName,
     employeeLinks.employees.id   as EmployeeId,
     employeeLinks.employees.name as EmployeeName
};
 
};

Full Text Index

Last but not least we now have syntax to define full text Index within the CDS/HDBDD artifact.


namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context fullText {
entity Header {
  key id : Integer;
@SearchIndex.text: { enabled: true }
@SearchIndex.fuzzy: { enabled: true }
  description : String(120);
};
};
To report this post you need to login first.

16 Comments

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

  1. Trond Stroemme

    Hi thomas,

    What is the exact intention of the associations? For instance, I’m able to insert records into CDS entities although these violates the defined associations (example: I can create customers with city codes not already stored in an associated city table etc.). Instead, I have to wite explicit SQLscript procedures for validating such data entries and then refer to these in my OData services – thus replicating the whole association concept. Will there be an easier way to implicitly use associations in odata services, without the need for this duplication of validations?

    Regards,

    Trond

    (0) 
    1. Thomas Jung Post author

      While its true that associations aren’t yet integrated at the SQL or even SQLScript level yet (hopefully in the future), you can get such automatic validation if you use the XSDS library. And yes one of the backlog items we have is to reuse the CDS association within the XSODATA service definition so you don’t have to define it again.

      (0) 
        1. Thomas Jung Post author

          No. The XSODATA framework only works against database tables, SQL views,  and modeled views. It doesn’t know anything about CDS Entities or XSDS.

          (0) 
  2. Timothy Muchena

    Thanks for your blog.

    I am learning HANA native development and I have my xsjs application project set up and I want to develop a stored procedure for my project. Can I use CDS to define a table type for the procedure and if yes how?

    Thanks and kind regards

    (0) 
  3. Jonathan Choi

    How can CDS view take the SAP Enhancement Framework into consideration?  I’ve always used BAPI so I can include SAP enhancements in my reports or updates to SAP.  Will there be a way to include checking for SAP enhancements in CDS views implementing CRUD in future?  Not only for the enhancements, we were told that we can’t update SAP tables directly.  Is CDS views only for custom developments?

    (0) 
      1. Jonathan Choi

        Sorry about the confusion.  I’m working on S/4HANA embedded analytic where we use CDS for the transient provider for BO Design Studio.

        I would agree that CDS can be used for the custom tables or HDB development.  But for SAP S/4HANA or any SAP modules, CDS view should have a validation process using BAPI or Business Object.

         

        (0) 
        1. Thomas Jung Post author

          I can’t comment on CDS for ABAP nor should you post questions regarding CDS for ABAP in this blog. This blog discusses HANA CDS, which is a separate/different implementation of CDS entirely.

          (0) 
  4. Matthias Aumüller

    Hi Thomas,

    in the Section Multi-File Support you are talking about a central way to declare cds artifacts. I wonder how central can you define types and annotations with this approach. Is it only possible to define types for one hdbb-module or can the definition be used in other modules or even other projects (MTA) too?

    (0) 
    1. Thomas Jung Post author

      No you can’t access any database artifacts from another container without a Synonym.  So just including the other HDBCDS file from another HDB module wouldn’t be possible.  You can potentially share HDBCDS files centrally in an NPM repository. This is exactly what we do with the annotations. You include these by adding them to your package.json of you hdb module. However this requires publishing to an NPM repository.

      (0) 

Leave a Reply