SAP S/4HANA Data Management and Analytics empowere...

Masaaki · ‎2023 Sep 02

Purpose of this blog

SAP S/4HANA data capabilities can be much empowered by SAP HANA Cloud.

Automate E2E Data Migration Process (Extract / Transform / Loading)

Cross-system Data Lakehouse and analytics in S/4HANA

Large Data Volume Management in SAP S/4HANA by analyzing data moved outside together

Data Storage for CFIN third party scenario and Syniti

Data-Hub: central point to extract data from S/4HANA and other data sources

CAP app based on HANA Cloud accessing to S/4HANA remotely (Extension scenario)

The purpose of this blog is to explain the scenarios and technical settings in which SAP S/4HANA is empowered by SAP SAP HANA Cloud, a scenario of BTP Side-by-Side Extensibility,

For non-production purposes like PoC, you can use Free SAP SAP HANA Cloud.

Cost of SAP SAP HANA Cloud can be optimized by adjusting allocated resources and moving data to data lake (low-cost storage).

Scenarios

Data Migration

Data Lakehouse Capabilities inside SAP S/4HANA

Data Volume Management for SAP S/4HANA

Others

Functions for Integration scenarios in HANA

Smart Data Access (SDA) and Smart Data Integration (SDI): With SDA and SDI it is possible to read/write data in the external database　to SAP HANA via Virtual Table. More sources are supported in SDI than SDA (SAP Note 2600176 - Supported Remote Source of SDA). SAP HANA Cloud is supported by bot SDA and SDI. Cloud Connector is needed to use SDA in SAP HANA Cloud, and DP Agent is needed for SDI. Watch Microlearning.

Calculation View: Calculation View is used for analytical app like SAC in SAP HANA Cloud. See the tutorial and microlearning for Calculation View and this microlearning for SAC. Web IDE is used to create Calculation View. To access the data of S/4HANA remotely in HANA Cloud with 3rd party tool, need to check the license issue.

SAP HANA Cloud, data lake: SAP SAP HANA Cloud, data lake is one of the components that make up SAP SAP HANA Cloud. Data can be moved from SAP HANA Cloud In-Memory database to data lake to optimize the cost. Internally, technology of SAP IQ is used. Use SAP SAP HANA Cloud Capacity Unit Estimator to estimate the cost. See also the blog: Estimating the costs of SAP SAP HANA Cloud

Thoes scenarios are valid also for S/4HANA Private Cloud Edition, e.g. to create Virtual Table in HANA for S/4HANA with help of SAP ECS team. See more in SAP Note 3169613,

Getting started with SAP SAP HANA Cloud

SAP HANA Cloud is very easy to use. Follow the tutorial of “DA261 - SAP SAP HANA Cloud Database Administration and Development” (especially SAP HANA Database Explorer - 25 minutes). For the scenario of SAC for SAP HANA Cloud and SDI, WebIDE has to be used. For other scenarios, Database Explorer is enough.

Skillset for SAP HANA Cloud can be found by reviewing the Learning for SAP HANA Cloud. To understand SAP HANA Cloud, there are many public sources, e.g. openSAP, SAP Tutorials for Developers and Youtube HANA Academy > SAP SAP HANA Cloud.

Configurations

Sign up for an SAP SAP HANA Cloud

Firstly, SAP HANA Cloud and Data lake have to be deployed. For non-production purpose, you can use Free Trial Account. Create BTP Trial account and deploy SAP HANA Cloud. This guide would also help. BTP Trial Account is available up to 90 days, if you want to use an existing SAP BTP account, SAP HANA Cloud Free Tier is available, which has no time limit. Step1 of “Start Using SAP SAP HANA Cloud Trial in SAP BTP Ceockpit” would help adding entitlements to your SAP BTP account to start using the appropriate services. See the blog to understand more.

When deploying SAP HANA Cloud, specify the parameter in JSON format like below. See the blog more about it.

{

"data": {

"systempassword":"",

"edition":"cloud",

"enabledservices": {

"docstore": false,

"scriptserver": false

},

"memory": 30,

"memoryScaleOut": 0,

"storage": 120,

"vcpu": 2

}

}

Change the setting to allow all IP addresses directly and via SAP Cloud Connector.

Open In SAP HANA Cloud Central.

Go “Manage Configuration”

Set to allow all IP addresses in Connections and Cloud Connector.

To deploy SAP HANA Cloud, Data Lake, follow the steps in the guide.

Enter Instance Name, Description, Administrator Password, select Allow all IP Addresses, and push “Create Instance”.

Data Lake is created.

Create Table with data for the test

For the test purpose, a table with data should be created in the schema of GLOBAL.

Follow the steps of 2. SAP HANA Database Explorer in the blog. You can use the test data, e.g. Orders.csv in the github.

Connections

There are 2 connection scenarios:

A. In SAP HANA Cloud to (1) ABAP Stack and (2) HANA for SAP S/4HANA via SDI DP Agent

B. In HANA server for SAP S/4HANA to SAP HANA Cloud

A. In SAP HANA Cloud to (1) ABAP Stack and (2) HANA for SAP S/4HANA via SDI DP Agent

Create Users:

Before creating connections, create users used in the setting.

Open in SAP HANA Database Explorer in BTP Cockpit.

In SQL Console, run the statements to create the users and add the authorizations.

--Create User and Schema

CREATE USER GLOBAL PASSWORD Welcome1

NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;



-- Authorization

GRANT CATALOG READ  TO GLOBAL;

GRANT CREATE SCHEMA TO GLOBAL;

GRANT CREATE REMOTE SOURCE to GLOBAL;

GRANT CREATE VIRTUAL TABLE on VIRTUAL_TABLES TO GLOBAL;



-- Authoriazation for Remote User

GRANT CREATE REMOTE SOURCE TO GLOBAL;



-- Disable password lifetime

ALTER USER GLOBAL DISABLE PASSWORD LIFETIME;

Create DP Agent Admin User: DPA_ADMIN

-- DP Agent Admin user

CREATE USER DPA_ADMIN PASSWORD Welcome1

NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;



GRANT AGENT ADMIN          TO DPA_ADMIN;

GRANT ADAPTER ADMIN        TO DPA_ADMIN;



-- Disable password lifetime

ALTER USER DPA_ADMIN DISABLE PASSWORD LIFETIME;

Create DP Agent Message User: DPA_MSG

-- DP Agent user for message

CREATE USER DPA_MSG PASSWORD Welcome1

NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;



-- Disable password lifetime

ALTER USER DPA_MSG DISABLE PASSWORD LIFETIME;



-- Disable password lifetime

ALTER USER DPA_MSG DISABLE PASSWORD LIFETIME;

In the statement above the password is “Welcome1”. Please change it to your password.

Create Remote Source:

See the blog more about the setting for 1), and this blog for 2). For 1), you can skip the chapter of “Set up your SAP ERP system” for SAP S/4HANA.

Script

C:\usr\sap\dataprovagent01\bin>agentcli.bat --configAgent



Configuring the Agent at the following location:

C:\usr\sap\dataprovagent



Initializing DPAgent Configuration Tool



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

                 DPAgent Configuration Tool

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

1. Agent Status

        :

7. SAP HANA Connection

        :

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

Enter Option:7

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

                    SAP HANA Connection

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

1. Connect to SAP SAP HANA Cloud via JDBC

        :

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

Enter Option:1



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

             Connect to SAP SAP HANA Cloud via JDBC

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

Press "Enter" button to keep default or skip an optional setting.

If a setting cannot be skipped, that means it is required.

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

Enter Use encrypted JDBC connection[true]: Valid options: true|false

true

Enter Host Name:



Enter Port Number:

443

Enter Agent Admin HANA User[DBADMIN]:

DPA_ADMIN

Enter Agent Admin HANA User Password:



Enter Agent Admin HANA User Password:  (confirm)



Enter Use Proxy Server[false]: Valid options: true|false

false

Enter HANA User Name for Agent Messaging:

DPA_MSG

Enter HANA User Password for Agent Messaging:



Enter HANA User Password for Agent Messaging:  (confirm)



Connecting to SAP HANA server via JDBC...

Agent configuration tool is connected to SAP HANA server via JDBC.



Press Enter to continue...





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

                    SAP HANA Connection

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

1. Connect to SAP SAP HANA Cloud via JDBC

    :

b. Back

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

Enter Option:b

Initializing DPAgent Configuration Tool



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

                 DPAgent Configuration Tool

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

    :

8. Agent Registration

    :

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

Enter Option:8

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

                     Agent Registration

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

1. Register Agent

    :

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

Enter Option:1

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

                       Register Agent

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

Press "Enter" button to keep default or skip an optional setting.

If a setting cannot be skipped, that means it is required.

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

Enter Agent Name[AGENT1]:



Enter Agent Host Name[XXXXXXX]:



Registering agent 'AGENT1' with SAP HANA.

Agent 'AGENT1' successfully registered.



Press Enter to continue...



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

                     Agent Registration

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

   :

b. Back

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

Enter Option:b





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

                 DPAgent Configuration Tool

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

   :

9. Adapter Registration

   :

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

Enter Option:9

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

                    Adapter Registration

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

1. Display Adapters

   :

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

Enter Option:1

Number of Adapters: 26

Adapter Name                            Registered              Custom Adapter

ABAPAdapter                             No                      No

   :

   :



Press Enter to continue...



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

                    Adapter Registration

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

   :

2. Register Adapter

   :

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

Enter Option:2

Enter adapter name:

ABAPAdapter



Adapter 'ABAPAdapter' successfully registered.



Press Enter to continue...



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

                    Adapter Registration

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

   :

2. Register Adapter

   :

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

Enter Option:2

Enter adapter name:

HanaAdapter



Adapter 'HanaAdapter' successfully registered.



Press Enter to continue...



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

                    Adapter Registration

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

1. Display Adapters

   :

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

Enter Option:1

Number of Adapters: 26

Adapter Name                            Registered              Custom Adapter

ABAPAdapter                             Yes                     No

   :

HanaAdapter                             Yes                     No

   :



Press Enter to continue...

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

                    Adapter Registration

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

1. Display Adapters

2. Register Adapter

3. Unregister Adapter

q. Quit

b. Back

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

Enter Option:q

Create Virtual Tables:

Either by running statement or with Database Explorer, it is possible to create Virtual Table in SAP HANA Cloud (1,2). To create Virtual Table, ABAP Program has to be run in Application Server layer in SAP S/4HANA (if HANA Enterprise Edition is used, you can run statement directly in HANA DB Layer).

For creating virtual tables in SAP HANA Cloud (1, 2), run the following statements.

create virtual table "S4H100_IFICOMPANYCODE" at "S4H100"."".""."ABAP_CDS.IFICOMPANYCODE$P";

create virtual table "HANAS4H_ZARTEST01" at "HANAS4H".""."SAPHANADB"."ZARTEST01";

Virtual tables are created.

Table ZARTEST01 in SAP S/4HANA.

Select and Insert data in HANA for SAP S/4HANA. “Insert” to Virtual Table is also possible.

-- Select and Insert data in Virtual Tables

Select top 100 * from "S4H100_IFICOMPANYCODE";



insert into "HANAS4H_ZARTEST01" values ('L','2','3');

Select top 100 * from "HANAS4H_ZARTEST01";

Data in ZARTEST01 in SAP S/4HANA.

It is possible for Virtual Table to have data physically by adding Replica. There are 2 types of replication: Real-time and Snapshot. See the blog for further detail. See the views M_VIRTUAL_TABLE_REPLICAS and M_VIRTUAL_TABLE_REPLICA_ACTION_HISTORY for checking the status of replica.

--- Create Replica for Virtual Table

-- Add Replica - Realtime

alter virtual table "HANAS4H_ZARTEST01" add shared replica;



--- Drop Replica - Realtime

alter virtual table "HANAS4H_ZARTEST01" drop replica;



-- Add Replica - Snapshot

alter virtual table "HANAS4H_ZARTEST01" add shared snapshot replica;



-- Add Replica - Refresh Snapshot

alter virtual table "HANAS4H_ZARTEST01" refresh snapshot replica;



-- Add Replica - Snapshot Drop

alter virtual table "HANAS4H_ZARTEST01" drop replica;

It is also possible to connect via SDA. SAP Cloud Connector is needed instead of DP Agent in the case. Refer to the blog for more about it.

B. In HANA server for SAP S/4HANA to SAP HANA Cloud

Create Remote Source:

To create Remote Source in HANA for SAP S/4HANA, follow the steps in SAP Help. Below are the statements run in SQL Console in HANA Studio logging with SYSTEM.

CREATE PSE HANAC;



CREATE CERTIFICATE FROM '

-----BEGIN CERTIFICATE-----



-----END CERTIFICATE-----

' COMMENT 'BTP'; 



SELECT CERTIFICATE_ID FROM CERTIFICATES WHERE COMMENT = 'BTP'; 

ALTER PSE HANAC ADD CERTIFICATE ;



SET PSE HANAC PURPOSE REMOTE SOURCE;



CREATE REMOTE SOURCE SAP HANA CLOUD ADAPTER "hanaodbc" 

CONFIGURATION 'Driver=libodbcHDB.so;ServerNode=xxxxxx.SAP HANA Cloud.ondemand.com:443;

encrypt=TRUE;'

WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=GLOBAL;password=Welcome1';

“SAP HANA Cloud.ondemand.com:443”: replace to the endpoint of your SAP HANA Cloud.

Replace “Welcome1” of the user GLOBAL to your password.

Create Virtual Tables:

To create Virtual Table in ABAP stack, run the program ZICA_CREATE_VIRTUAL_TABLE. See the blog to understand more.

In the following parameter, the virtual table for the table ORDERS in the Schema GLOBAL is created.

To read the data via the virtual table, run the SELECT statement with Tcd DBACOCKPIT > Diagnostics > SQL Editor in SAPGUI.

“/1BCAMDP/” is added as the prefix of the VT_NAME.

Tcd DBACOCKPIT > Diagnostics > SQL Editor.

Result:

Create CDS View for the Virtual Table.

Table function and AMDP are created for the virtual table, and CDS View is created on top.

Table Function

@ClientDependent: false

@EndUserText.label: 'ZPT_CUSTOMERS'

define table function ZPT_CUSTOMERS

returns {

  CUSTOMERID: abap.char(50);

  CITYID: abap.char(50);

  COUNTRYID: abap.char(50);

  REGIONID: abap.char(50);

  CUSTOMER: abap.char(50);

  CITYNAME: abap.char(50);

  COUNTRYNAME: abap.char(50);

  REGIONNAME: abap.char(50);



}

implemented by method ZCL_AMDP_FUNCTIONS_NOCL=>GET_CUSTOMERS_FOR_CDS;

Class-Method of AMDP used in the Table Function.

class zcl_amdp_functions_nocl definition

  public

  final

  create public .

  public section.

    interfaces if_amdp_marker_hdb .

    class-methods GET_CUSTOMERS_FOR_CDS

        for table function ZPT_CUSTOMERS.

  protected section.

  private section.

endclass.

 class zcl_amdp_functions_nocl implementation.



    method GET_CUSTOMERS_FOR_CDS by database function

        for hdb

        language sqlscript.

--        options read-only.

        RETURN

        SELECT

        CUSTOMERID,

        CITYID,

        COUNTRYID,

        REGIONID,

        CUSTOMER,

        CITYNAME,

        COUNTRYNAME,

        REGIONNAME

        FROM "/1BCAMDP/SAP HANA CLOUD_CUSTOMERS" AS CUSTOMERS;



    endmethod.



ENDCLASS.

CDS View:

@AbapCatalog.sqlViewName: 'ZIVHCCUSTOMERS'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'ZI_VHANAC_CUSTOMERS'

define view ZI_VHANAC_CUSTOMERS as select from ZPT_CUSTOMERS {

CUSTOMERID,

CITYID,

COUNTRYID,

REGIONID,

CUSTOMER,

CITYNAME,

COUNTRYNAME,

REGIONNAME    

}

Data Preview of the CDS View.

Move data to Data Lake

Data in SAP HANA Cloud can be moved to SAP HANA Cloud, Data Lake.

It is possible to create table in Data Lake by running Procedure in SAP HANA Cloud. To do that, privilege has to be added in Database Explorer.

GRANT HANA_SYSRDL#CG_ADMIN_ROLE TO GLOBAL;

Then, run the procedure REMOTE_EXECUTE.

CALL SYSRDL#CG.REMOTE_EXECUTE('BEGIN

CREATE TABLE "CUSTOMERS"(

	"CUSTOMERID" VARCHAR(50),

	"CITYID" VARCHAR(50),

	"COUNTRYID" VARCHAR(50),

	"REGIONID" VARCHAR(50),

	"CUSTOMER" VARCHAR(50),

	"CITYNAME" VARCHAR(50),

	"COUNTRYNAME" VARCHAR(50),

	"REGIONNAME" VARCHAR(50),

PRIMARY KEY(CUSTOMERID)

);

END');

Remote Source SYSRDL#CG_SOURCE in SAP HANA Cloud is that for Data Lake. In the remote source, the table “CUSTOMERS2 is created.

Create Virtual Table for this table in GLOBAL Schema.

Object Name: DATALAKE_CUSTOMERS

Schema: GLOBAL

--- Create UNION View Table in SAP HANA Cloud and in Data Lake

create view CUSTOMERS_TOTAL as

select * from GLOBAL.CUSTOMERS

union all

select * from GLOBAL.DATALAKE_CUSTOMERS;



--create Procedure to move data to Data Lake

--DROP PROCEDURE  MOVE_DATA;

CREATE PROCEDURE MOVE_DATA(CID int)

 LANGUAGE SQLSCRIPT AS

 BEGIN

	DECLARE CUSTOMERS_COUNT int;

	DECLARE CUSTOMERS_COUNT2 int;

	select count(*) into CUSTOMERS_COUNT from CUSTOMERS_TOTAL;

	insert into "GLOBAL"."DATALAKE_CUSTOMERS" select * from CUSTOMERS where CUSTOMERID < CID;

	delete from CUSTOMERS where CUSTOMERID < CID;

	select count(*) into CUSTOMERS_COUNT2 from CUSTOMERS_TOTAL;



	IF CUSTOMERS_COUNT= CUSTOMERS_COUNT2

	  THEN commit;

	ELSE

	  rollback;

	END IF;

 END;

 

--- Run Procedure

 call MOVE_DATA(10);

Select data in the Virtual Table

select * from "GLOBAL"."DATALAKE_CUSTOMERS"

Data of CUSTOMERID < 10 are moved to table in the Data Lake.

Analyzing data in SAP S/4HANA or SAP HANA Cloud

It is possible to analyze data either/both in SAP S/4HANA and SAP HANA Cloud on SAP S/4HANA or SAP HANA Cloud.

Characteristics of analytics in SAP S/4HANA

SAP Fiori analytical apps can be used. SAC is optional.

Analytic Engine coming from BW is available for analytic query. See Wiki and the section of the blog for further detail.

Users/authorizations in SAP S/4HANA is available.

Table Function/AMDP has to be created for the data in SAP HANA Cloud in addition to Virtual Table.

SAP Named User license for SAP S/4HANA is required for SAP Fiori analytical apps. In case of SAC, SAP Named user license should not be necessary. (Need to reconfirm SAP license sales).

Deeper knowledge is needed of CDS View.

Refer to the blog to understand more about SAP S/4HANA Analytics.

Characteristics of Analytics in SAP HANA Cloud

Analytics tool like SAC is mandatory.

SAP Named User license is not necessary.

Mainly modeled with SQL in SAP HANA Cloud,

Firstly, Calculation View has to be created in SAP HANA Cloud, then create Live Connection to the SAP HANA Cloud in SAC. Model and Story are created (Tutorial).

SDI - Data Transformation

It is possible to use Smart Data Integration (SDI) function for Data Transformation within SAP HANA Cloud without additional license. You can enjoy higher productivity for data transformation in SAP HANA Cloud.

SAP HANA Cloud can be connected with many sources by creating Remote Source. Virtual Table is created to access the data in external source. Flowgraph is created for data transformation.

It could be used for data migration, data transformation for data distribution. DP Agent has to be installed to connect to many sources.

See the blog for further detail.