How to: Integrate and Consume your S/4HANA Cloud Data on-premise using HANA Smart Data Integration ODataAdapter and Custom CDS View based OData Services

1. Disclaimer

This blog entry purely focuses on functional and technical aspects of the scenario. It does not address any license related aspects regarding the usage of the software components mentioned in this blog entry. In any case you must clarify the license and software usage side before implementing such a scenario with your SAP Account & License Expert to be on the safe side. Please also don’t raise any questions in relation to this context in the comments section.

2. What can you expect from this blog entry?

You can expect details about the configuration of the introduced scenario step-by-step, how to avoid pitfalls and some general aspects around the given context. The configuration and implementation is described end-to-end, involving both activities on S4HC as well as on the HANA DB on-premise.

3. Architecture

A very simplified architectural sketch outlines some of the main components involved on both sides.

4. Out of Scope

◈ Network/firewall/port settings

◈ Details on SDI user authorizations

◈ How to setup HANA PSE

◈ How to enable XS scheduler

◈ Steps undertaken in BW4H

5.1. Implementation Steps

In the following you will be provided with an overview on the required configuration steps in both S4HC and the HANA target system. Subsequently, a detailed description of how to execute these implementation steps is outlined.

S/4HANA Cloud – Overview

The steps required on the S4HC side are outlined in this blog entry. However, there are plenty of other blogs on this topic, on a much more detailed level available. Refer to other contributions that deal with the S/4HANA related configuration & setup by going into the “References” section of this page.

1. Create Custom CDS Views

2. Create Custom Communication Scenario

3. Maintain Communication User & Assign Certificate

4. Create Communication Arrangement

HANA On-premise (target) – Overview

The steps explained herein target the SDI software component setup and its configuration on the HANA side. It involves some basic steps to configure and shows the ease of use of SDI.

1. Deploy IM_DP

2. Enable XS scheduler

3. Deploy the SDI OData adapter

4. Setup HANA PSE (export certificate)

5. Establish trust relationship

6. Set SSL parameter

Further implementation steps:

1. Create Remote Sources

2. Create Replication Tasks

3. Load and Schedule

4. Monitor

5.2. S/4HANA Cloud – Implementation/Configuration Execution

Start your configuration with “Custom CDS Views”:

1. Create the Custom CDS Views

In S/4HANA Cloud numerous standard CDS views are shipped.

Based on the standard I_CostCenterText CDS view, our custom CDS view “CostCenterText” is built. You have to tick the “External API” box.

2. Continue with “Create a Custom Communication Scenario”

Search/select existing scenario or create a new one:

Next steps to consider:

3. Maintain Communication User

In S/4HANA Cloud, a communication user has to be maintained. This communication user is used in all of the SDI remote sources and will authenticate against the S/4HANA cloud system.

In the certificate section, the HANA certificate of the target system has to be uploaded, else the connection can’t be established.

4. Create Communication Arrangement

A communication arrangement defines a set of inbound services and respectively the communication user which has to be used to consume one of the services. In this screenshot, a set of services are shown, including the Costcentertext service which we have initially created.

The communication system defines the actual S/4HANA system by providing localhost and the HTTPS port.

5.3. HANA On-premise (target) – Implementation/Configuration Execution

1. Deploy IM_DP

2. Deploy the SDI OData adapter

3. Enable XS scheduler

4. Setup HANA PSE (export certificate)

5. Establish trust relationship

6. Set SSL parameter

1. Deploy IM_DP

Download and Deploy the Data Provisioning Delivery Unit – “HANA DP DU” (IM_DP add-on)  from the service marketplace (https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS00/en-US/61c1d46cbc1f4617950870097488b78e.html)

Access the PAM for SDI 1.0 or 2.0 to get the release that fits your HANA release + revision:

◈ https://drive.google.com/open?id=1y0Cy7UWNCkJyYEc6wK5roEhPT_jYrPS4

◈ https://drive.google.com/open?id=1KozXw36me_ZFek9qBq42Hb5H7A1PnkUP

2. Deploy the SDI OData adapter

Via SQL command line in HANA Studio create the ODataAdapter within the dpserver via the following command (ADAPTER ADMIN authorization is required):

CREATE ADAPTER "ODataAdapter" PROPERTIES 'display_name=OData Adapter;description=OData Adapter' AT LOCATION DPSERVER;

Remember, the OData Adapter is not a system adapter (other than the well-known SDA adapters). The adapter is C++ based and doesn’t run on the DP-agent side, it runs in the DP-server of HANA.

3. Follow the HANA/SDI documentation to Enable XS scheduler

From SDI Documentation “Consume HTTPS OData Services”:

https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS03/en-US/3fe5752db9aa4ca1883cacc8e0d2c822.htm

4. Follow the HANA/SDI documentation to setup PSE, then export the certificate

From SDI Documentation “Consume HTTPS OData Services”:

https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS03/en-US/3fe5752db9aa4ca1883cacc8e0d2c822.html

Finally your PSE should look similar to the following (in a real scenario you would typically use the SAPSRV.PSE – as applied in the remote source creation SQL string further down on the page):

5. Establish Trust Relationship

Once the HANA trust store/PSE environment is available, certificate import requires the following steps:

1. Download the certificates from the browser by opening the OData service URL (in Chrome: click on the small security/lock symbol left to the URL)

g. https://xyz-api.s4hana.ondemand.com/sap/opu/odata/sap/PROFITCENTERTEXT_CDS

2. Import the downloaded certificates into the trust store via XS Admin Trust Manager

https://<hanahost>:<pott>/sap/hana/xs/admin/

1. Click Manage PSE

2. Import Certificate -> Select the downloaded certificates one after another

6. Set HANA SSL parameter

We have faced a SSL related error, that could be narrowed down to a missing CA list on the server.

The Solution for the SSL Handshake Error is the following:

HANA Parameter “sslBlindCAResponse = on” in dpserver.ini has to be set 

Issue:

With HANA 1.0 SPS12 there has been a refactoring, so XS Classic will use the common crypto for all encrypted communication. This caused a different behavior, if a server is requesting a client certificate, but does not send a list of trusted CAs along with the request for the certificate. Since there is no selection criteria for the client certificate available, common crypto can not decide, which certificate should be used from the truststore. Hence, the handshake fails.

The following was our DPserver trace extract, that gives further details:

13: 0x00007fc7f6891462 in SAPODataAdapter::ODataClient::getMetadata(SAPODataAdapter::ODataMetadataRequest const&)+0x130 at ODataClient.cpp:204 (ODataAdapter.so)

14: 0x00007fc7f684e7e3 in SAPODataAdapter::ODataAdapter::getOservice()+0x150 at ODataAdapter.cpp:889 (ODataAdapter.so)

…………

24: 0x00007fc901e5edef in TrexService::WorkerThread::run(void*)+0x135b at TrexServiceThreads.cpp:630 (libhdbbasement.so)

[7341]{348972}[104/-1] 2018-07-12 18:40:00.737212 e IpConnection     IPConnection.cpp(00163) : comm::connect to 'xyz-api.s4hana.ondemand.com:443' failed with 'Internal Error Details. Crypto/SSL/CommonCrypto/Engine.cpp:424: SSL handshake failed: SSL error [-1604320766]: No trusted CA list from server, General error: 0xa0600202 | SSL | SSL_connect

No trusted CA list from server

No trusted CA list from server

0xa0600202 | SSL | ssl3_connect

No trusted CA list from server

0xa0600202 | SSL | ssl3_send_client_certificate

No trusted CA list from server

Solution:

5.4. Further Implementation Steps

1. Create Remote Sources

2. Create Replication Tasks

3. Load

4. Monitor

1 .Create Remote Sources

All in all, the scenario comprises 40+ custom CDS views and likewise 40+ OData services, that are to be consumed on HANA. It is advisable to make use of the SQL CREATE REMOTE SOURCE statement, instead of configuring each single one of it manually in the web development workbench or in HANA studio. It can be taken from the SDI documentation, but the following provides a wrap-up:

CREATE REMOTE SOURCE "COSTCENTERTEXT_CDS" ADAPTER "ODataAdapter"

AT LOCATION DPSERVER CONFIGURATION

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ConnectionProperties name="connection_properties">

<PropertyEntry name="URL">host:port/sap/opu/odata/sap/ COSTCENTERTEXT_CDS/</PropertyEntry>

<PropertyEntry name="proxyserver">proxy</PropertyEntry>

<PropertyEntry name="proxyport">8080</PropertyEntry>

<PropertyEntry name="truststore">sapsrv.pse</PropertyEntry>

<PropertyEntry name="isfiletruststore">true</PropertyEntry>

<PropertyEntry name="shownavigationproperties">true</PropertyEntry>

</ConnectionProperties>'

WITH CREDENTIAL TYPE 'PASSWORD' USING

'<CredentialEntry name="password">

<user>SDI_ODATA</user>

<password><password</password>

</CredentialEntry>';

This represents the view on the remote source from the web development workbench.

You have to grant the _SYS_REPO user access to the remote source to be able to create and run dependent objects, such as a replication task:

GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE <remote_source_name> TO _SYS_REPO;

2. Create Replication Tasks

Per each remote object (Custom CDS View), there is one replication task. As the remote source and OData adapter do not support any real-time capability, the truncate table option in combination with full-load is chosen. It is viable, as the data volume in our example is negligible. A full-load every day or hour was not a problem in our context. None of the data flows took longer than > 1 minute. This may of course vary from case to case:

The higher the data volume is, the likelier it will be that some custom data flow must be modelled to only reflect delta data being loaded. For this purpose a flowgraph can serve the need. For further reference how to reflect custom delta logic, take a look at my other two blog entries about using HANA SDI in various contexts. These are mentioned under “References” on this page.

3. Load – Schedule Replication Tasks

Each replication task is scheduled via the design time object monitor (<host>:<port>/sap/hana/im/dp/monitor/?view=IMDesignTimeObjectMonitor). The SDI monitors not only serve to schedule flowgraph or replication task executions but also to gain an overview on replication state or critical situations in which SDI data flows need to be reset or exceptions must be processed. There will be no further details provided how the scheduling of SDI objects is technically configured. This can be taken out of SDI standard documentation

4. Monitor SDI Data Flows

5. The very last step is to be done of the BW4H side: Creation of an SDI Source and then likewise the modeling on top of the entities the SDI remote source contains.

Of course you can also directly consume the tables you have replicated into your schema or build e.g. CalculationViews on top.

Another alternative are BW/4 sources that directly sit on top of an SDI remote source. This is sketched in the architectural illustration under point.