Use ODBC in Excel to consume SAP ABAP CDS view

Consuming CDS View Entities Using ODBC-Based Client Tools

This article introduces the method of accessing CDS view data of ABAP system through SQL statements based on ODBC (Open Database Connectivity).

ODBC is a set of standard APIs for accessing databases.

Why an ODBC Driver for ABAP?

In some cases, you want external SQL read access to CDS objects owned by the ABAP system. Direct SQL read access to the underlying SAP HANA database of the ABAP system is not a good choice. Some issues are listed in SAP Note 2511210. The name and internal structure in the ABAP database may be unstable because the life cycle is managed by the ABAP system. Type conversion may not be performed as expected, for example, the NUMC data type may not be filled correctly, or currency data may not be moved correctly. The database session variables can only be set correctly when the view is accessed from the ABAP system. If you directly use the underlying SQL statement to query, this bypasses the ABAP-level security concept.

When you view the ABAP system itself as a database by directly accessing the ABAP system using ODBC, all these problems will disappear. In this case, authentication and authorization are done using ABAP users. Apply full ABAP SQL semantics, and even use application server-level buffering and ABAP-level access control and read access log records.

Compared with the ODATA interface, the advantage of the ODBC interface is that it allows unlimited SQL access to all public ABAP CDS view entities. Data from different entities can be connected in a temporary way, and the data can be aggregated for analysis and query.

In its current version, the "ODBC Driver for ABAP" only supports the use of technical users with privileged access (no DCL) in the ABAP system. Only read access to public ABAP CDS objects is allowed.

Overview of Steps and Prerequisites

To access the CDS view entities in the ABAP system through ODBC, these entities first need to be correctly exposed in the back-end system, and then they can be accessed through the "ODBC Driver for ABAP" in the ODBC application.

The ODBC application can be any client tool that can load the ODBC driver, or it can be a program written in a programming language that can use the ODBC driver (such as C, C++, python, node.js, PHP).

This article will show use cases for Microsoft Excel on Windows.

The "ODBC Driver for ABAP" itself is available on Windows and Linux.

(1) Create and fill some test tables

First create two test database tables to store the header and line items of the order:

@EndUserText.label : 'ORDER ITEMS'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zorderitems {
  key orderid : abap.numc(10) not null;
  key pos     : abap.int4 not null;
  item        : abap.char(100) not null;
  amount      : abap.int4 not null;

}

@EndUserText.label : 'Jerry ORDERS'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zorders {
  key id       : abap.numc(10) not null;
  creationdate : abap.datn;

}

Create an ABAP class to fill in the data:

class zcl_fill_orders definition

  public
  final
  create public.

  public section.
    interfaces if_oo_adt_classrun.
  protected section.
  private section.

endclass.

class zcl_fill_orders implementation.

  method if_oo_adt_classrun~main.

    data: lt_orders type table of zorders.
    delete from zorders.
    lt_orders = value #(
      ( id = '1' creationdate = '20210801' )
      ( id = '2' creationdate = '20210802'  )
      ( id = '3' creationdate = '20210803' )
    ).
    insert zorders from table @lt_orders.
    out->write( sy-dbcnt ).

    data: lt_orderitems type table of zorderitems.
    delete from zorderitems.
    lt_orderitems = value #(
      ( orderid = '1' pos = '1' item = 'Apple' amount = '5' )
      ( orderid = '1' pos = '2' item = 'Banana' amount = '5' )
      ( orderid = '1' pos = '3' item = 'Orange Juice' amount = '2' )

      ( orderid = '2' pos = '1' item = 'Orange' amount = '10' )
      ( orderid = '2' pos = '2' item = 'Apple' amount = '5' )

      ( orderid = '3' pos = '1' item = 'Bottle Water' amount = '5' )
    ).
    insert zorderitems from table @lt_orderitems.
    out->write( sy-dbcnt ).

  endmethod.

endclass.

The inserted test data is shown in the figure below:

Let's create CDS views for these database tables, because only CDS views can be exposed to the ODBC consumer.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ORDERS'
define view entity ZORDERSVIEW as select from zorders {
  key id as Id,
  creationdate as CreationDate
}

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ORDER ITEMS'
define view entity ZORDERITEMSVIEW as select from zorderitems {
  key orderid as OrderId,
  key pos as Pos,
  item as Item,
  amount as Amount
}

Now we need a Service definition and a corresponding SQL type Service binding to define a new CDS view entity and expose it through SQL services.

To define a new service definition, right-click on one of the ZORDERSVIEW or ZORDERITEMSVIEW views in the Project Explorer, then select "New Service Definition" and give the service definition a name, such as Z_SERVICE_DEF_SQL.

@EndUserText.label: 'test SQL service'
define service Z_SERVICE_DEF_SQL {
  expose ZORDERSVIEW as Orders;
  expose ZORDERITEMSVIEW as OrderItems;
}

This new service definition can be used in a service binding. Select service definition and create service binding in the right-click menu:

The binding type must be selected as: SQL1-Web API

The name of this Service Binding, ZORDERS, will later become the schema name used by the external ODBC consumer.

Since we want to use technical users in the ABAP system to access the service binding, we now need to create a communication scenario in the SAP BTP ABAP Environment system: Z_COMM_SCENARIO_SQL and a communication arrangement.

In the generated communication scenario, click the inbound tab. Inbound Service ID, select S_PRIVILEGED_SQL1:

S_PRIVILEGED_SQL1 is a pre-configured service for privileged access to CDS view entities. DCL stands for Data Control Language, which provides an access control mechanism to filter the results returned from the CDS view in the database according to conditions.

Authentication method select Basic:

Switch to the Authorization tab and add a new Authorization object: S_SQL_VIEW

Maintain the corresponding values for the three fields of the Authorization object.

SQL_SCHEMA must contain the name of the service binding that we want to grant access to. In this case, uppercase symbols are fine.

The value "*" of SQL_VIEW means that we allow access to all views in the service definition attached to the service binding ZORDERS.

Since only read-only access is currently allowed, SQL_VIEWOP=SELECT is required.

In fact, we grant the SELECT permission of all views in the schema ZORDERS to users in the communication scenario, just like we issue GRANT statements in the database.

Click publish locally:

Right-click the ABAP project in ABAP Development Tool, select properties, and find the Fiori url of the ABAP environment:

Create a new communication system:

Check Inbound Only:

Create a new communication user and maintain a password with a length of 20, such as S! Repeat three times.

The last step is to create a Communication arrangement to connect the Communication scenario created in ADT with the Communication system just created.