“Core data services (CDS) is an infrastructure that can be used to define and consume semantically rich data models in SAP HANA“.
The purpose is straight forward but the technical definition can provoke confusion sometimes, especially because we can find two different types of Core Data Services:
◈ HANA CDS
◈ ABAP CDS
While HANA CDS has to support only an SAP HANA database, ABAP CDS provides support to different database vendors, consequently there are some restrictions for ABAP CDS views compared to HANA CDS views. When it isn’t possible to solve your scenario with an ABAP CDS View there is an alternative solution creating an ABAP CDS Table Function powered by ABAP Managed Database Procedures (AMDP).
Let’s do a quick comparison between the development approaches with ABAP CDS View and ABAP CDS Table Function.
ABAP CDS View
In a common ABAP CDS View development we declare our field structure and annotations through a scripting editor in the ABAP layer (usually through HANA Studio with ADT installation) and after activation the system generates automatically all the SQL Views in the database layer.
ABAP CDS Table Function
In an ABAP CDS Table Function development we define an entity with the field structure, parameters (optional) and an association to a class/method. With AMDP we’re able to write database procedures directly in the ABAP layer and encapsulate inside the class/method we defined in the Table Function, the call works as the same like any other ABAP methods and we have the following advantages:
◈ Detailed analysis of runtime errors through ST22;
◈ Database procedures debug available through HANA Studio;
◈ Transport identical as to ABAP classes.
Since AMDP works directly with database scripting some extra steps are necessary like the definition of database and the script language to be used (in SAP HANA the language is the SQL Script). We’ll talk about this configuration in details during the development of our demo.
With both development approaches explained we can finally start the development of our technical demo. After finishing this post you will be able to create your own ABAP CDS Table Function and provide a solution for a simple technical scenario that cannot be achieved directly through a default ABAP CDS View. For the purposes of this demo we will use the database view SFLIGHTS who provides details about flight connections.
Scenario
Each airline company provides flight connections to different cities around the world, the user wants to see all the cities supported by a specific airline in a single field separate by comma. Since the number of cities can be different for each one of the airlines we need to generate a logic to concatenate all the cities no matter how many entries are returned.
Through a common ABAP CDS View we could use a CONCAT function, but in this case we would need to define a fixed quantity of fields, since the CDS View can’t handle this logic dynamically how should we proceed?
This is actually a perfect scenario for an ABAP CDS Table Function because we can solve with a simple database function called STRING_AGG (String Aggregation). This function is available in the SQL Script but currently there is no support through ABAP CDS View.
Development
Open your HANA studio and create a new Core Data Services -> Data Definition.
Select your project, package and define the name and description as below.
Select your transport request and click Next. In the Templates section select the last option “Define Table Function with Parameters” and Finish.
Edit the generated entity including the following details:
- Fields:
- Client
- Airline Code
- Airline Name
- Cities To
- Class: ZCL_FLIGHTS_DEMO_CDS
- Method: FLIGHTS_CONNECTIONS
This should be the final result:
define table function ZDEMO_FLIGHTS_TABLE_FUNCTION
returns
{
client : abap.clnt;
airline_code : s_carr_id;
airline_name : s_carrname;
cities_to : abap.string;
}
implemented by method
ZCL_FLIGHTS_DEMO_CDS=>FLIGHTS_CONNECTIONS;
Let’s create a new ABAP class with the name ZCL_FLIGHTS_DEMO_CDS, select a transport and click in Finish.
Adapt your class including the interface IF_AMDP_MARKER_HDB. This step will transform the ABAP class into an AMDP class and provide the possibility to include database procedures inside its methods.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
Declare a new public method and include the statement FOR TABLE FUNCTION referencing the table function we created in the first step.
CLASS-METHODS:
flights_connections FOR TABLE FUNCTION ZDEMO_FLIGHTS_TABLE_FUNCTION.
In the method implementation we need to include some configuration options:
◈ BY DATABASE FUNCTION: This option will mark the method as a table function, another option is to generate a procedure changing the statement to BY DATABASE PROCEDURE.
◈ FOR HDB: Defines the database type as HDB (HANA Database).
◈ LANGUAGE SQLSCRIPT: Language used by HANA database procedures.
◈ OPTIONS READ-ONLY: No changes allowed inside the database procedure.
◈ USING: Definition of database tables, views or procedures that would be consumed inside our table function. In our case we need to access data only from SFLIGHTS view.
METHOD flights_connections
BY DATABASE FUNCTION
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING sflights.
<<YOUR_CODE_HERE>>
ENDMETHOD.
Let’s prepare our selection splitting the logic in two SELECT statements.
The first SELECT should collect the fields Client, Airline Code, Airline Name and City To and execute a DISTINCT to remove possible duplicate entries, the reason behind this logic is because we can find different connection dates for the same Airline and City. Check the example below with a few entries from SFLIGHTS view:
One of the advantages of AMDP is that you can transfer results from a specific SELECT to an “internal table” and you can execute a new SELECT reading data from it. Let’s take advantage of this functionality and store the result of this first SELECT in a table called itab_cities.
itab_cities =
SELECT DISTINCT
sflights.mandt as client,
sflights.carrid as airline_code,
sflights.carrname as airline_name,
sflights.cityto as city_to
FROM sflights;
In the second SELECT we should read the data from itab_cities and implement the database function STRING_AGG to aggregate multiple Cities per Airline. To achieve this functionality we need to GROUP BY the entries based on Client, Airline Code and Name. The expected result is the following:
RETURN
SELECT client,
airline_code,
airline_name,
STRING_AGG(city_to, ', ' ORDER BY city_to) as cities_to
FROM :itab_cities
GROUP BY client,
airline_code,
airline_name;
Note: A table function will always expect a return parameter, so remember to place a RETURN statement before the last SELECT. Also, notice that we converted the field names to the expected name by the ABAP CDS Table Function declaration, if you don’t provide a proper name alias the compiler will complain during the activation.
This should be your final version of the class ZCL_FLIGHTS_DEMO_CDS:
CLASS zcl_flights_demo_cds DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS:
flights_connections FOR TABLE FUNCTION zdemo_flights_table_function.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_flights_demo_cds IMPLEMENTATION.
METHOD flights_connections
BY DATABASE FUNCTION
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING sflights.
itab_cities =
SELECT DISTINCT
sflights.mandt as client,
sflights.carrid as airline_code,
sflights.carrname as airline_name,
sflights.cityto as city_to
FROM sflights;
RETURN
SELECT client,
airline_code,
airline_name,
STRING_AGG(city_to, ', ' ORDER BY city_to) as cities_to
FROM :itab_cities
GROUP BY client,
airline_code,
airline_name;
ENDMETHOD.
ENDCLASS.
And this is the result of the Data Preview:
Nice blog.. Thanks for sharing
ReplyDeleteThanks! Helped me to solve my problem.
ReplyDelete