Taking Data Federation to the Next Level: Accessin...

SeungjoonLee · ‎2024 Mar 25

Update on November 15, 2024:

The answer to the first FAQ question has been updated. The SDA ABAP adapter is also supported in SAP HANA 2.0 Platform SPS08 or later.

Update on October 17, 2024:

Starting from QRC 03/2024, the ODBC driver for ABAP runs on the indexserver by default which means enabling script server is not prerequisite anymore.

Accessing data in ABAP systems, such as SAP BTP ABAP environment, S/4HANA Cloud, BW/4HANA, and others, from an external SAP HANA database is not merely a new requirement or passing trend; it has been a longstanding and ongoing need to enhance and streamline business processes. This need becomes even more crucial when considering the SAP HANA Cloud, which serves as a single gateway for all your enterprise data and where next-generation Intelligent Data Applications are built and run.

However, there are several reasons why directly accessing the ABAP-managed standard objects in the underlying SAP HANA database of an ABAP system is not recommended. These include the instability of names and internal structures, unexpected typecasts, incorrect session variables, and bypassed ABAP-level security, among other concerns. For more details on these issues, please refer to SAP Note 2511210.

To overcome these challenges, data federation through the Smart Data Integration (SDI) ABAP adapter has been proposed as a potential solution, while data replication is supported by both the SDI ABAP adapter and SAP Landscape Transformation (SLT). It is important to mention, though, that the SDI ABAP adapter, as explained by Zili Zhou in her blog, is primarily designed for data replication and may have limitations when it comes to data federation due to limited pushdown capabilities.

In this context, I'm glad to share that starting with SAP HANA Cloud, SAP HANA Database (SAP HANA database in SAP HANA Cloud) QRC 01/2024, a new Smart Data Access (SDA) ABAP adapter has been released. This adapter allows for accessing ABAP CDS view entities in a remote ABAP system using SQL via Open Database Connectivity (ODBC) from an SAP HANA database in SAP HANA Cloud. Please find a nutshell of this new capability below:

Create a remote source to an ABAP system based on SAP ABAP Platform 2108 or later.
Virtualize remote ABAP CDS view entities by creating read-only virtual tables that point to them, and if needed, also pass parameters to the remote parameterized ABAP CDS view entities.
All limitations from SAP Note 2511210 have been lifted, providing the most optimal pushdown capabilities for data federation.

Prerequisites & considerations

First, please ensure that the versions of the ABAP system and SAP HANA database in SAP HANA Cloud are as mentioned below:

The ABAP system should be based on SAP ABAP Platform 2108 or a later version where SQL service is available. For instance, starting with S/4HANA 2021, SQL service has been available.
SAP HANA Cloud, SAP HANA Database (SAP HANA database in SAP HANA Cloud) should be QRC 01/2024 or a later version where ODBC driver for ABAP is available, and the script server should be enabled and running if QRC 01/2024 or QRC 02/2024 is used.
Starting from QRC 03/2024, the ODBC driver for ABAP runs on the indexserver by default which means enabling script server is not prerequisite anymore.

Another aspect that should be considered is whether the ABAP system is protected by a corporate firewall (e.g., on-prem S/4HANA). If it is, then SAP Cloud Connector is required.

If the above conditions are met, the next step is to expose ABAP CDS view entities from the ABAP system side so that they can be accessed from SAP HANA database in SAP HANA Cloud via the SQL service.

Since developing and exposing an SQL service in the ABAP system is not the focus of this blog and has already been well-explained by Frank-Martin Haas in his blog, please refer to his blog and the following links.

SAP Business Technology Platform: Developing and Exposing an SQL Service in the ABAP System
SAP Tutorials: Using ODBC driver for SQL Service (From Step 3 to Step 9)

Getting started with SDA ABAP adapter (abapodbc)

If everything is ready in the ABAP system to expose ABAP CDS view entities, let’s configure the SDA ABAP adapter from the SAP HANA database in SAP HANA Cloud.

The configuration steps are almost the same as using SDA with other remote sources: downloading the DigiCert SSL certificate, importing the certificate for SSL connection, creating a remote source, and finally, creating a virtual table. Please refer to the examples below.

Download the DigiCert SSL certificate:

-- DigiCert Global Root CA
curl -O https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem

-- DigiCert Global Root G2
curl -O https://dl.cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem

Since DigiCert has stopped signing certificates with the root certificate authority (CA) "DigiCert Global Root CA" and is now moving forward with the "DigiCert Global Root G2" certificate, it is recommended to add both certificates during the switching period. However, using only one certificate is basically sufficient.

Import the certificate for SSL connection:

-- import the certificate for SSL connection (CA)
CREATE PSE SSL;
CREATE CERTIFICATE CA_CERT FROM '
-----BEGIN CERTIFICATE-----
MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBh
...
CAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=
-----END CERTIFICATE-----
';

-- import the certificate for SSL connection (G2)
CREATE CERTIFICATE G2_CERT FROM '
-----BEGIN CERTIFICATE-----
MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBh
...
CAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=
-----END CERTIFICATE-----
';

ALTER PSE SSL ADD CERTIFICATE CA_CERT;
ALTER PSE SSL ADD CERTIFICATE G2_CERT;
SET PSE SSL PURPOSE REMOTE SOURCE;

Create a remote source:

-- create a remote source to the ABAP SQL service endpoint
CREATE REMOTE SOURCE ABAP ADAPTER "abapodbc" 
CONFIGURATION 'uidtype=alias;driver=ODBC_driver_for_ABAP.so;servicepath=/sap/bc/sql/sql1/sap/s_privileged;host=<abap_host_name>;port=<abap_port_number>;language=EN;typemap=semantic;'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<user_name>;password=<password>';

Alternatively, like other SDA adapters, the remote source can be created using the SAP HANA Database Explorer UI. Once the remote source is created, the exposed ABAP CDS View entities can be browsed using the SAP HANA Database Explorer as described below.

Remote Objects.png

Create virtual tables:

-- create virtual tables by pointing to the exposed ABAP CDS view entities
CREATE VIRTUAL TABLE DEMO.V_ORDERS AT "ABAP"."NULL"."ZORDERS"."Orders";
CREATE VIRTUAL TABLE DEMO.V_ORDERITEMS AT "ABAP"."NULL"."ZORDERS"."OrderItems";

Alternatively, virtual tables can also be created using the SAP HANA Database Explorer UI by selecting the remote object and clicking on the 'Create Virtual Object(s)' button in the screenshot above.

Please also refer to the links below for further details.

SAP HANA Cloud, SAP HANA Database Data Access Guide: Import Certificates for SSL Connections to Remote Source
SAP HANA Cloud, SAP HANA Database Data Access Guide: Create an ABAP Cloud Remote Source

Before moving forward, please don’t forget to create statistics on virtual tables. Creating statistics is always strongly recommended to ensure optimal decision-making on the query execution plan by the query optimizer.

Create statistics on a virtual table:

CREATE STATISTICS ON DEMO.V_ORDERS TYPE SIMPLE;
CREATE STATISTICS ON DEMO.V_ORDERITEMS TYPE SIMPLE;

Further details can be found at the link below.

SAP HANA Cloud, SAP HANA Database Data Access Guide: Create Statistics on a Virtual Table or Linked Database

Query execution and execution plan

With above configurations, if the same statement from the blog by Frank-Martin Haas is executed but with virtual tables, the result will be as follows.

SELECT OI."Item", SUM(OI."Amount")
FROM DEMO.V_ORDERS O
INNER JOIN DEMO.V_ORDERITEMS OI
ON O."Id" = OI."Orderid"
GROUP BY "Item";

Now, let’s see the query plan using Analyze - Explain Plan.

As you can see, both aggregation and join are pushed down to the remote ABAP system and executed there. In other words, both aggregation and join are performed on the remote side, and then the result is transferred to the SAP HANA database in SAP HANA Cloud. Since SDA is a part of SAP HANA core like the query optimizer, if statistics are available, the query optimizer will create the most optimal execution plan based on the tight integration with SDA.

Other limitations

The most important limitation to keep in mind is that the SDA ABAP adapter supports only read-only virtual tables at the moment. This leads to the following limitations:

DMLs (e.g., INSERT/UPDATE/DELETE) cannot be executed on virtual tables.
WITH REMOTE is not supported when executing CREATE VIRTUAL TABLE.
Join relocation to a remote ABAP system, which requires temporary table creation, is not supported. However, if another remote source with temporary table creation capability is involved additionally, join relocation to that remote source is possible but it will be decided by the query optimizer.

FAQs

Q: Is this SDA ABAP adapter only available in the SAP HANA database in SAP HANA Cloud? Is there any plan to support this in SAP HANA Platform 2.0?
A: The SDA ABAP adapter is also supported in SAP HANA Platform 2.0 SPS08 or later.

Q: Is there any plan to support real-time replication, such as SDA-based remote table replication described in this blog? If not, what is the recommended technology for replicating ABAP CDS view entities?
A: There are no plans to support real-time replication with the SDA ABAP adapter. The SDA ABAP adapter is designed for data federation purposes only, even though toggling to a snapshot replica is supported to improve query performance. For replicating ABAP CDS view entities, it is recommended to use SDI ABAP adapter or SLT.

Q: Is it possible to pass parameters to the remote ABAP CDS view entity through the virtual table?
A: Yes, passing parameters to the remote ABAP CDS view entity is supported. Please refer to the link below.

SAP HANA Cloud, SAP HANA Database Data Access Guide: Virtualizing Parameterized Views

Q: Is it possible to connect to the on-prem ABAP system? If so, how can it be done?
A: Yes, the way of connecting the on-prem ABAP system can be found in the links below.

SAP HANA Cloud, SAP HANA Database Data Access Guide: Create an ABAP On-Premise Remote Source

However, please ensure that the following conditions are met:

The on-prem ABAP system is based on SAP ABAP Platform 2108 or later version.
SAP Cloud Connector is properly configured so that the on-prem ABAP system is ‘reachable’ from the SAP Cloud Connector and SAP BTP Cockpit.
SAP Cloud Connector is enabled, and the corresponding IP addresses are allowed in the SAP HANA database configuration in SAP HANA Cloud.
The CREATE REMOTE SOURCE statement includes the correct specifications for useCloudConnector and ConnectivityLocationId properties.

Q: It seems that only the ‘technical user’ can be used for authentication. Are there any plans to support other authentication methods?
A: Yes, other authentication methods will be considered in the future.

Q: Is it also possible to access remote ABAP CDS (DDIC-based) views or other remote ABAP-managed objects?
A: No, only remote ABAP CDS view entities or remote parameterized ABAP CDS view entities can be accessed. For the differences between ABAP CDS (DDIC-based) views and ABAP CDS view entities, please refer to this blog.

Q: Is it possible to use the linked database feature?
A: The linked database feature with a 3-part name is supported. However, passing parameters through the linked database feature is not supported. Linked database optimized mode is also not supported in SAP HANA Cloud.

Conclusion

To conclude, it is evident that accessing data in ABAP systems plays a vital role in the development of Intelligent Data Applications on the SAP HANA database in SAP HANA Cloud. This also aligns perfectly with the concept of Clean Core, which involves minimizing customizations and rearchitecting them on SAP BTP, particularly on the SAP HANA database in SAP HANA Cloud. By elevating data federation to the next level with SDA ABAP adapter and embracing these practices, organizations can unlock the full potential of their data, leveraging the power of SAP HANA Cloud. So, don't miss out on the opportunity to harness the benefits of Intelligent Data Applications and propel your business towards success with the SAP HANA database in SAP HANA Cloud.