Monday, 29 February 2016

Core Data Services in ABAP

Core Data services (CDS) are domain specific languages (DSL) and services for defining and consuming semantically rich data models in SAP HANA. They are integral part of SAP HANA, and can be leveraged in the ABAP stack.

Core Data Services in ABAP and Certifications
Core Data Services are enhanced “view entities” in ABAP provides several advantages:.

  • Pushes data intensive Calculations into database layer.
  • Queries can be combined,  ex: UNION, UNION ALL
  • Nested Views are supported
  • Association between view entities can be defined.
  • Case expression supported
  • Arithmetic, String and cast expression supported
  • ABAP CDS supports INNER joins, OUTER joins, and RIGHT OUTER joins.
  • Annotations can be used.
  • Aggregations, Grouping, Filtering groups
  • Support for AnyDB  But no access to specific HANA features
  • Supports about 90% of SQL features
  • Supports ABAP and SAP HANA deployment.
Support for CDS in ABAP begins with ABAP 7.4 SP2. Especially with SAP NetWeaver ABAP 7.40 SP5 additional capabilities like CDS Associations are supported. CDS Associations can be used to replace joins with simple path expression in queries.

The goal of ABAP CDS is to fully support the SQL-92 standard and beyond that to support additional features likes associations, entities, create, read, update, and delete (CRUD) and OData Services.
Core Data Services in ABAP

ABAP 7.4 SP05 support code to data paradigm. ABAP stack manages all entities and deploys all artifacts from ABAP layer into the database layer. ABAP server act as master for developing, managing and calling HANA views.  On Activation of CDS views two objects are generated one CDS Database View and other CDS entity.  CDS views are defined for existing database tables, any other views or CDS views in ABAP dictionary. Syntax for defining CDS view is similar to SQL.

Simple CDS view:

Core Data Services in ABAP

The annotation @AbapCatalog.sqlViewName is mandatory, and specifies the  name of DDL source in the ABAP Dictionary and the database.  On Activation of DDL source two objects are created CDS database view and CDS entity. CDS database view can be accessed using SE11 ABAP dictionary. These representation are generated, updated, and deleted automatically when activating DDL source.

In the above example simple CDS view, “ZCDS_DDL_SAMPLE2” is CDS ABAP Database View and “ZCDS_SAMPLE2” is CDS entity. CDS entity can be identified using “DEFINE VIEW” syntax and CDS ABAP Database view can be identified using “@AbapCatalog.sqlViewName” annotation which on activation accessible from SE11- ABAP Dictionary View. Same applies to all the examples shown below.

CDS View with column list and using column alias:

Core Data Services in ABAP

Here CDS view with column list with curly brackets and using column alias to rename a column is shown. Column list are comma separated and alias names are given with keyword ‘AS’.

Case Expressions in CDS View:

Core Data Services in ABAP

In the new open SQL, ABAP CDS supports the CASE expression.  It is also possible to nest cast expression. An Alias name is required for the resulting column except for nested CASE. The resulting column type is derived from expressions after THEN and ELSE clause.

String, Arithmetic and Cast expressions in CDS Views:

Core Data Services in ABAP

Arithmetic expressions using operators +, - , *, & or the unary -, or using built in functions such as CEIL( ) and MOD( ) are used. String expressions, including string concatenation operator &&, or built in functions such as SUBSTRING( ) are used. Cast expressions are also supported.

Joins in CDS Views:

Core Data Services in ABAP

ABAP CDS support INNER joins, LEFT OUTER joins and RIGHT outer joins. Join conditions can be inserted before or after the select list. Brackets are used to implement complex join operators. When joins are used asterix is not supported in SELECT statements.

Annotations in CDS Views:

Core Data Services in ABAP

Annotations specify additional information such as how records of the view should be buffered by ABAP table buffer, whether view is client dependent or not. By default CDS views are client dependent and there is no need to include client fields explicitly in the field list or in the join conditions.

Annotations are marked with the @sign on top of DDL source code in the view. In above example @AbapCatlog.Buffering.status states whether buffering is switched off or active, @AbapCatlog.Buffering.type states type of buffering for CDS view, @Semantics is used for reference currency and quantity fields.

Aggregation, Grouping, Filtering Groups in CDS Views:.

SAP HANA Certifications and Material

Through ABAP CDS calculation of aggregations, grouping records and filtering groups are supported. GROUP BY and HAVING are used.

Core Data Services in ABAP

Simple and complex filtering conditions are supported. It is also possible to include WHERE conditions in CDS view definitions.

Unions in CDS Views:

Core Data Services in ABAP

Using the UNION and UNION ALL keywords views are merged from two or more queries. Here UNION implies DISTINCT, the combined result contains no duplicate rows, UNION ALL implies the combined result has duplicate rows.

Selecting Data Using CDS Views:

Core Data Services in ABAP

Core Data Services in ABAP

The open SQL SELECT statement is used in ABAP to select data from a CDS view. ABAP Dictionary View name, respectively SQL view name of the CDS view is used in select from clause.

When using the new Open SQL syntax you can used CDS View name which is identified using DEFINE VIEW keyword in the select from clause. Note that CDS view name cannot have the same name as the SQL view/ ABAP Dictionary view and the CDS view name also does not have same name of the ABAP Dictionary.

In the above new Open SQL syntax CDS view is used in select from clause, you might notice the "@" symbol used fro escaping of host variables. Host variable basically mean an ABAP variable or constant inside Open SQL statement. Every host variable except literals can, should, and must be escaped with the preceding "@" sign at every position in the statement. If one host variables escaped all host variables have to be escaped.

To retrieve the data from a client dependent CDS view, new Open SQL syntax USING CLIENT is used.