Jump Start to ABAP Core Data Services

In this second part of SAP S/4 Embedded Analytics multi-blog series(First part: SAP S/4 HANA Embedded Analytics https://blogs.sap.com/2017/06/29/sap-s4hana-embedded-analytics), we cover fundamental concepts of Core Data Services (referred to as CDS), a core component of SAP S/4 HANA Embedded Analytics with detailed examples.

In the next blog, we will cover the Implementation and Activation of Embedded Analytics with SAP S/4 HANA.

Core Data Services

The Core Data Services (CDS) offers domain-specific languages and services to define and consume semantically rich data models.

Core Data Services at a Glance

• Semantically Rich Data Models – Introduced Domain specific languages (Data Definition Language, Query Language, Data Control Language) and services for defining and consuming semantically Rich Data Models.
• Completely based upon SQL — Any ‘Standard SQL’ features directly available like joins, union, union all, projection, selection, built-in functions etc.
• Infrastructure Ensure Any-DB support — Fully compatible with any database. SQL views are created which can be consumed by Open SQL.
• Built in functions — Rich Set of Built-in SQL functions for calculations, aggregation and grouping etc.
• Code Push down — Follow Code to data paradigm. More data-intensive calculations can be pushed down to the database through extended view functionality.
• Extensible — Extend the data definition of CDS view at any time.
• Nested View supported — Highly Reusable. One view consumes another view.
• Fully integrated in ABAP infrastructure — Proven and consistent ABAP Lifecycle Management.

Figure1: Building Blocks

*************************************************************************

CDS View Life Cycle

Figure 2 shows the complete life cycle of CDS View from the creation to the consumption.

Step 1 : Creation of CDS view.

Step 2 :  Activation of a DDL source.

Step 3 : Consumption of CDS view using Open SQL.

Figure 2: Life Cycle of CDS View

****************************************************************************

Annotations

Annotations enrich CDS data models with additional metadata. They begin with @

There are two types of annotations:

• The first type of annotations provide metadata which applies to the whole CDS view entity (e.g. specifying the table buffer settings of a view). Such annotations are listed before the statement DEFINE VIEW.
• The second type of annotations are used to have influence on a single element from the select list (e.g. specifying a field as currency code with @Semantics.currencyCode or as unit of measure with @Semantics.unitOfMeasure). Annotations of this type are specified directly before the specific field.

****************************************************************************

Pre-requisites

ABAP CDS Views can only be created using the ABAP in Eclipse plug-in IDE(Integrated Development environment).

Download from https://tools.hana.ondemand.com/<eclipse version>

Eclipse versions are planetary objects, mars, luna, keplar, etc.

If using HANA Studio, use Help->About HANA Studio to get your Eclipse version number.

How to connect to HANA system to start the development?

Create an ABAP Project

Connection to an ABAP backend system

• Can use Logon pad entries or manual
• Must be active. No offline mode
• User-specific view of the backend system

Favorite Packages:

• Convenient way to organize your personal view of the backend
• Can Add or remove as necessary, does not affect the actual packages

Generating Some data

• We have used SFLIGHT – Airline Flight Model for our examples
• You can use report SAPBC_DATA_GENERATOR Report, transaction SE38 for this.

*****************************************************************************

Syntax of CDS View

The SELECT statement defines a query performed on the data sources specified in data_source for a CDS view. Possible data sources are database tables defined in ABAP Dictionary,  classic views, or other CDS views.

Syntax:

SELECT [DISTINCT]

Variant1

{  select_list
FROM data_source
[association1 association2 …] }

or

Variant2

{ FROM data_source
[ association1 association2 …]
{select_list} }
[clauses]

WHERE

• select_list defines the components read in a list.
• association1, association2, … define associations for the current SELECT statement. These associations can be accessed in data_source and in select_list using path expressions.
• DISTINCT removes duplicates from the results list.

Both forms of the code have the same semantics. Curly brackets { } must be used in the second variant to set  select_list. The optional additions clauses are SELECT clauses that enable conditions, groupings, and union sets to be specified or created.

Sample Code where all the columns from the database table are selected.

Annotations

@AbapCatalog.sqlViewName: ‘ZISFLIGHT’ – Database view in ABAP dictionary that is generated at activation

@EndUserText.label: ‘Sample Demo for sflight’ – Defines a human-readable text that is displayed.

Sample Code

@AbapCatalog.sqlViewName: ‘ZISFLIGHT’

@EndUserText.label: ‘Sample Demo for sflight’

define view ZI_SFLIGHT as select from sflight

{ * }

Selecting Columns in CDS View

This will be comma separated list of column names.  This includes table aliases and key fields. CDS View key fields need not be same as of the underlying data source.

Sample Code 1

@AbapCatalog.sqlViewName: ‘ZISFLIGHT’

@EndUserText.label: ‘Sample Demo for sflight’

define view ZI_SFLIGHT as select from sflight as sf

{

key sf.fldate as Flightdate,

key sf.connid as Flightno,

key sf.carrid as FlightCarrierID

}

**************************************************************************

Dictionary Representation, Active Annotations, Dependency Analyzer and Graphical Editor

Dictionary Representation: When activating a CDS view, the following objects are created in the ABAP Dictionary a shown in Figure 3.

1. The actual CDS entity
2. A CDS database view

Figure 3: CDS view Building Architecture

Below sample code select Distinct Columns and include where clause in CDS View.

Sample Code 2

@AbapCatalog.sqlViewName: ‘ZISFLIGHT’

@EndUserText.label: ‘Sample Demo for sflight’

define view ZI_SFLIGHT as select distinct from sflight as sf

{

key sf.fldate as Flightdate,

key sf.connid as Flightno,

key sf.carrid as FlightCarrierID,

sf.planetype

}

where sf.planetype=‘747-400’

Dictionary Representation of above sample code is shown in Figure 4. Transaction SE11 can be used to check the DDL SQL View and DDL Source.

Figure 4: Dictionary Representation of CDS Entity and Database View

Right click on DDL source and Expand “Open With” as depicted in Figure 5.

Figure 5 Options Available under “Open With”

Active Annotations View

The CDS annotations that are used for an active CDS view are displayed in the Active Annotations view as shown in Figure 6.

In the Active Annotations view, following columns are displayed:

1. Annotated Elements: The view, its parameters, fields, and associations, and their active CDS annotations.
2. Annotation Value: Values of the corresponding CDS annotations.
3. Origin Data Source: Name of the development object, for example, a data definition or database table from which the corresponding annotation is inherited. You can navigate to this object by double-clicking its name.
4. Origin Data Element: Name of the data element from which the corresponding annotation is inherited. You can navigate to this object by double-clicking its name.

Figure 6: Example of Active Annotations view

Dependency Analyzer:

Use the Dependency Analyzer to investigate which database objects (such as CDS database views, database tables, database views, and table functions) are used in your CDS view. In addition, it helps you to understand SQL dependencies and complexity. The SQL Dependency Tree tab displays dependencies as hierarchy as shown in Figure 7.

Figure 7: Example of Dependency Analyzer

Graphical Editor

The CDS Graphical Editor provides a read-only view containing CDS entities, user-defined types, and the relationships between entities in graphical representations as shown in Figure 8.

Figure 8: Example of Graphical Editor

***************************************************************************

Expressions and Built-In Functions in CDS View

Rich Set of Built-in SQL functions for calculations, aggregation and grouping etc are available for use in CDS view.

There are couple of Built-in Database Functions (SQL and Special Functions) managed by ABAP Dictionary which can be specified in the Select list of a CDS View and in operand positions. Below shows sample Code for case and arithmetic expressions

Sample Code 3

@AbapCatalog.sqlViewName: ‘ZISFLIGHT1’

@EndUserText.label: ‘Sample Demo for case expression’

define view ZI_SFLIGHT1 as select distinct from sflight as sf

{

key sf.connid as Flightno,

key sf.carrid as FlightCarrierID,

sf.planetype,

sf.price,

case sf.connid

when ‘0017’ then ( sf.price + 500 )

when ‘0790’ then ( sf.price + 200 )

when ‘0408’ then ( sf.price + 100 )

else ( sf.price + 300 )

end as final_price

}

Figure 9 shows Output of Sample Code 3.

Figure 9 Output

Sample Code for unit and currency conversion.

Sample Code 4

@AbapCatalog.sqlViewName: ‘ZISFLIGHT2’

@EndUserText.label: ‘Sample Demo for unit and currency coversion’

define view ZI_SFLIGHT2

with parameters

to_currency : abap.cuky( 5 )

as select distinct from sflight as sf

inner join spfli as _spfli on sf.carrid = _spfli.carrid

{

key sf.carrid as carrier,

sf.price  as amount,

currency_conversion(

amount => price,

source_currency => currency,

target_currency => :to_currency,

exchange_rate_date => fldate,

error_handling => ‘SET_TO_NULL’

) as convert_amount,

_spfli.distance as distance,

round(unit_conversion(

quantity => _spfli.distance,

source_unit => _spfli.distid,

target_unit => cast( ‘KM’ as abap.unit( 3 )),

error_handling => ‘SET_TO_NULL’

), 3)  as convert_distance

}

where _spfli.distid = ‘MI’

Input parameter is passed in Figure 10 and Figure 11 shows Output of Sample Code 4

Input 10 Input Parameter

Figure 11 Output

************************************************************************

Input Parameters in CDS View

Parameter types can be predefined or data element.  This view also shows how to associate input parameters with ABAP system fields.  Parameters can be accessed by :<param_name> or $parameters.<param_name>.

Environment Annotation: Below parameter is specified with default value sy-mandt. No explicit actual parameter can be assigned to an input parameter to which the system field sy-mandt was assigned using #CLIENT. Open SQL always passes (implicitly) the value of sy-mandt

@Environment.systemField : #CLIENT

Sample Code 5

@AbapCatalog.sqlViewName: ‘ZISFLIGHT3’

@EndUserText.label: ‘Sample Demo for input parameters’

define view ZI_SFLIGHT3

with parameters

@Environment.systemField : #CLIENT

p_mandt  : syst_mandt,

p_carrid : s_carr_id

as select from sflight as sf

{

key sf.fldate as Flightdate,

key sf.connid as Flightno,

key sf.carrid as FlightCarrierID,

sf.planetype

}

where

sf.mandt  = :p_mandt

and sf.carrid = :p_carrid

Figure 12 enter the Input Parameter P_CARRID and execute to display the result set which is shown in Figure 13.

Enter the Input parameters:

Figure 12 Input Parameter

Figure 13 shows Data Preview in HANA Studio for Sample Code5.

Figure 13 Output

*****************************************************************************

Associations in CDS View

Associations define relationships between entities in the data model.

Association Types:

1. Ad Hoc Association -> Association consumption constitutes a JOIN
2. Exposed Association -> Exposure does not automatically lead to a JOIN. It follows “JOINs ON DEMAND”

Association definition contains Target Entity with optional Alias (recommended to start with _), Cardinality and Join condition.

Note: Associations used in where clause, projection list, having clause and group by clause leads to left outer join while association used in from clause will lead to inner join.

Sample Code 6

@AbapCatalog.sqlViewName: ‘ZISFLIGHT4’

@EndUserText.label: ‘Sample Demo for associations’

define view ZI_SFLIGHT4

as select distinct from sflight as sf

association[1..1] to spfli as _spfli

on sf.carrid = _spfli.carrid and sf.connid = _spfli.connid

{

// Expose fields used in the ON condition

// Prerequiste for exposed associaton

key sf.carrid as carrier,

key sf.connid as connid,

// Exposed association

_spfli,

sf.planetype,

// ad hoc association

_spfli.distid

}

Figure 14 shows Data Preview in HANA Studio for Sample Code 6.

Figure 14 Output

************************************************************************

CDS Views -Extensions

Extend View statement is used to extend the data definition. In the extend view statement, you define the name of the CDS view where you want to add further database fields. After the keyword “WITH”, you enter the name of the extend view itself.   Extensions are used to enhance CDS views with Additional fields, Arithmetic and Case expressions and Literals.

Note: CDS view enhancements can’t be enhanced further.

Annotation

@AbapCatalog.sqlViewAppendName is used to generate Append view in Dictionary

Sample Code7

@AbapCatalog.sqlViewAppendName: ‘ZSFLIGHTEXT’

@EndUserText.label: ‘Sample Demo for Extension’

extend view ZI_SFLIGHT with ZSFLIGHT_EXT {

concat(sf.carrid, sf.connid) as flightno

}

Figure 15 shows Data Preview in HANA Studio for Sample Code7

Figure 15 Output

************************************************************************

CDS Table Functions and AMDP

CDS Table Functions allow usage of natively implemented database-functions in SAP HANA DB directly from CDS.  Standard ABAP class methods are used as containers for the implementation of AMDPs. Classes with AMDPs must use interface IF_AMDP_MARKER_HDB.  The implementation is done within an AMDP method inside AMDP class and is managed as an AMDP function by the AMDP framework in the database system.

Necessary parts for CDS Table Functions are illustrated in Figure 16.

Figure 16 Necessary Parts of CDS Table Functions

How to get the flight details of an Airline using CDS table function? In this case, we are fetching the data from two database tables “scarr” and “spfli”.

Step 1 CREATE A CLASS

Sample Code8

CLASS Z_DEMO_CDS_TF DEFINITION

PUBLIC

FINAL

CREATE PUBLIC.

PUBLIC SECTION.

** Classes with AMDPs must use interface IF_AMDP_MARKER_HDB

INTERFACES: if_amdp_marker_hdb.

** TABLE FUNCTION

CLASS-METHODS GET_DATA_Z_CDS_TF

FOR TABLE FUNCTION ZCDS_TF.

PROTECTED SECTION.

PRIVATE SECTION.

ENDCLASS.

CLASS Z_DEMO_CDS_TF IMPLEMENTATION.

METHOD GET_DATA_Z_CDS_TF

BY DATABASE FUNCTION FOR HDB

LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

USING scarr spfli.

RETURN

** PASS CLIENT AND CARRIER ID IN WHERE CLAUSE

SELECT sc.mandt AS client, sc.carrid,

sc.carrname, sp.connid, sp.cityfrom, sp.cityto

FROM scarr AS sc

INNER JOIN spfli AS sp

ON sc.mandt = sp.mandt

AND sc.carrid = sp.carrid

WHERE sp.mandt = :p_clnt

AND sp.carrid = :p_carrid”

ORDER BY sc.mandt, sc.carrname, sp.connid;

ENDMETHOD.

ENDCLASS.

Step2 CDS TABLE FUNCTION DECLARATION

Annotation

@ClientDependent: true – The CDS view is client-specific. When accessed using SELECT, automatic client handling is performed.

Sample Code 9

@ClientDependent: true

@EndUserText.label: ‘TABLE FUNCTIONS CDS’

define table function ZCDS_TF

with parameters

@Environment.systemField: #CLIENT

p_clnt   :abap.clnt,

p_carrid :s_carr_id

returns {

client   :s_mandt;

carrid   :s_carr_id;

carrname :s_carrname;

connid   :s_conn_id;

cityfrom :s_from_cit;

cityto   :s_to_city;

}

implemented by method

Z_DEMO_CDS_TF=>GET_DATA_Z_CDS_TF;

Step3: ABAP Program Consuming CDS table function Using Open SQL

Sample Code 10

*&———————————————————————*

*& Report z_demo_amdp_cds_tf

*&———————————————————————*

*&

*&———————————————————————*

REPORT z_demo_amdp_cds_tf.

PARAMETERS: carrid TYPE scarr-carrid.

*** USING OPEN SQL

SELECT *

FROM ZCDS_TF( p_carrid = @carrid )

INTO TABLE @DATA(result)

##db_feature_mode[amdp_table_function].

cl_demo_output=>display_data( result ).

Execution and Output:

Pass the input parameter as American Airlines(AA) as specified in Figure 17.

Figure 17 Output

In the result set we obtained two rows for American Airlines as shown in Figure 18.

Figure 18 Output

*************************************************************************

CDS Authorizations

The CDS authorization concept is a new concept which co-exists with the classic authorization concept. It can be used either independently or in combination with the classic concept.  While the classic authorization concept is based on authorization objects with authorizations checked either implicitly (e.g. when transactions are called) or explicitly (using the statement AUTHORITY-CHECK), the CDS authorization concept is based on implicit checks automatically performed by the ABAP runtime when the CDS entity is accessed using Open SQL as shown in Figure 19.

Figure 19 Data Control Language Overview

Step1: Create a view

Annotation @AccessControl.authorizationCheck: #CHECK – Perform access control, syntax check warning if no role is assigned.

Sample Code 11

@AbapCatalog.sqlViewName: ‘ZDEMOIAUTH’

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: ‘Demo for authorization’

define view ZDEMO_I_AUTH as select from sflight {

key carrid as FlightCarrierId,

key connid as FlightNumber,

key fldate as FlightDate,

planetype

}

Figure 20 shows Output of Sample Code 9 by performing Data Preview in HANA Studio.

Figure 20 Output

Step2: Role Creation

Annotaion @MappingRole true is used.  The role is assigned to all users automatically regardless of the client.

Sample Code 12

@EndUserText.label: ‘Demo for Access Control’

@MappingRole: true

define role Zdemo_Ac {

grant

select

on

ZDEMO_I_AUTH

where

// — Example WHERE condition

// — Equals-or-initial operator

// or

// — Literal condition

// isPublic = ‘X’;

(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt=’03’);

}

No result is obtained while doing the data preview in HANA studio as shown in Figure 21.

Figure 21 Output

Step 3: Create Authorization and Authorization Class from SU21 as shown in Figure 22.

Figure 22 Authorization and Authorization Class

Step 4: Create Role using transaction PFCG as shown in Figure 23. Assign the required selection parameter and generate the profile.

Figure 23 PFCG Role

Step 5: Next step is to assign role to required user.

Go back to HANA studio to test authorization. Result set is now obtained while performing data preview again in HANA studio as shown in Figure 24.

Figure 24 Output

Figure 25 shows two values are permitted for “CARRID” selection ‘AA’ and ‘LH’ as specified.

Figure 25 Filter Condition

Additional feature added to the role. Figure 26 shows the combination of literal and aspect conditions.

Sample Code 13

@EndUserText.label: ‘Demo for Access Control’

@MappingRole: true

define role Zdemo_Ac {

grant

select

on

ZDEMO_I_AUTH

where

// — Example WHERE condition

// — Equals-or-initial operator

// ( CustomerCountry ) ?= aspect pfcg_auth( S_ACM_DEMO, SACMCNTRY )

// or

// — Literal condition

// isPublic = ‘X’;

(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt=’03’)

and FlightNumber = ‘0017’;

}

 

Figure 26 Output

Regards,

Esha Rajpal and Megha Gandhi

(Authors)