Purpose of this blog
Data in external system can be consumed in S/4HANA directly and remotely via CDS View.
It would help integration between S/4HANA and external system greatly like the following scenarios.
- Create cross system analytical app in S/4HANA in which data in S/4HANA and external system are integrated.
- Enjoy prediction for data in external system leveraging S/4HANA embedded Machine Learning capability.
- Extract data from external system and store in S/4HANA without other tools, e.g. data Data transformation is possible with SELECT statement in S/4HANA.
This blog explains how easy it is and what are possible using this CDS View consuming external data. The main point is the program to create Virtual Table which access the data in external system remotely (Available in SAP HANA Runtime Edition for Apps and SAP BW (HANA REAB)).
The solution in this blog is to show the technical possibility, but it is not official recommendation. There are some other ways for cross-system analytics, e.g. using BW/4HANA or Data Warehouse Cloud.
This solution should be available in all ABAP Platform in which CDS View is available, not only in S/4HANA, but BW/4HANA, etc..
Technology Overview:
HANA Virtual Table should be created for remote access in HANA Database layer (SDA, Smart Data Access), and the table in HANA Database layer is accessible with Table Function in which AMDP ABAP Class-Method is used as the source and HANA Native SQL statement can be used in the AMDP.
How can the Virtual Table be created?
Thanks to the program “ZICA_CREATE_VIRTUAL_TABLE” in the blog “Using Remote Data Source in ICMR”, Virtual Table can be created in ABAP Layer (so, it is possible with HANA REAB edition).
Which external sources are available?
The sources available for HANA SDA are available, e.g. HANA, HANA Cloud, ASE, IQ, Oracle, MSSQL, Google Big Query, etc.. All SDA supported remote sources require installation and configuration of ODBC driver. Details are in SAP Note 2600176 - SAP HANA Smart Data Access Supported Remote Sources,
Remote CDS View and scenarios
Details to create Virtual Table and table function are explained in this blog. This blog shows the objects created for the scenario.
- Remote CDS View: Table BSEG and T001 in the external HANA system is accessed remotely in S/4HANA system via CDS View/Table Function.
- Analytical app: Web Dynpro Grid app (Multidimensional Analytics) is created in which data in S/4HANA and external HANA system are merged. The Interface View (CUBE View) is created in which data in BSEG/T001 in S/4HANA and the created Table function are combined (UNION). Consumption View (Analytic Query) is created on top. It can be consumed with Web Dynpro Grid App.
- Embedded Machine Learning: Consume the external data for S/4HANA embedded Machine Learning. Defining and training the Machine Learning model of S/4HANA using this remote CDS View with Intelligent Scenario Lifecycle Management (ISLM).
- Data extraction from external system: Extract external data from this remote CDS View in S/4HANA and store the data in the custom table in ABAP Program.
1. Remote CDS View
Remote source for the external HANA Database is created.
Create Virtual table with the program ZICA_CREATE_VIRTUAL_TABLE.
When running the program, you are expected to enter
VT_NAME: Virtual View name
REMOTE: Name of the remote source
SCHEME: Scheme name in which the virtual table is generated (normally SAP<SID>)
RT_NAME: Source table name.
By executing the program with the input values, the Virtual table is generated. (View name: “/1BCAMDP/<VT_NAME).
Generated Virtual Table in HANA Database layer:
Table Function and AMDP to read from the Virtual Table:
@ClientHandling.type: #CLIENT_DEPENDENT
define table function ZI_RM_BSEG_TF
returns
{
key RCLNT : mandt;
key bukrs : bukrs;
key belnr : belnr_d;
key GJAHR : gjahr;
key BUZEI : buzei;
SAKNR : saknr;
AUGDT: augdt;
DMBTR : dmbtr;
WAERS : waers;
KOKRS : kokrs;
KOSTL : kostl;
}
implemented by method
z_bseg_remote_sources=>CALL_01;
AMDP ABAP Class-Method
class z_bseg_remote_sources definition
public
final
create public .
public section.
interfaces if_amdp_marker_hdb .
class-methods call_01
for table function ZI_RM_BSEG_TF.
protected section.
private section.
endclass.
class z_bseg_remote_sources implementation.
method call_01
by database function for hdb language sqlscript
options read-only.
return
select
SESSION_CONTEXT('CDS_CLIENT') as RCLNT,
bseg.bukrs,
belnr,
GJAHR,
BUZEI,
SAKNR,
AUGDT,
DMBTR,
WAERS,
KOKRS,
KOSTL
from "/1BCAMDP/REMOTE_BSEG" as bseg
inner join "/1BCAMDP/REMOTE_T001" as t001 /* or use "ZHANGVIN"."REMOTE_T001" */
on bseg.mandt = t001.mandt
and bseg.bukrs = t001.bukrs
where bseg.mandt = '100'
;
endmethod.
endclass.
Remote CDS View in which the Table Function is used as the source:
@AbapCatalog.sqlViewName: 'ZIRM_BSEG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZI_RM_BSEG'
define view ZI_RM_BSEG as select from ZI_RM_BSEG_TF {
key bukrs,
key belnr,
key GJAHR,
key BUZEI,
SAKNR,
AUGDT,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'WAERS'
DMBTR,
WAERS,
KOKRS,
KOSTL
}
2. Analytical app
Analytical app in which internal and external data are merged can be created with Remote CDS View.
CDS view for cross system analytics
CDS View for Interface View for integration (CUBE View):
In this view, data of BSEG in S/4HANA itself and data of BSEG in external HANA Database are merged (UNION). New field SOURCE is added in which the value is set to “ORIGINAL” for the data from S/4HANA and “REMOTE1” for the data from external HANA Database.
@AbapCatalog.sqlViewName: 'ZIBSEG_INTEG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZI_BSEG_INTEG'
@Analytics.dataCategory: #CUBE
define view ZI_BSEG_INTEG as
select from bseg
inner join t001
on bseg.mandt = t001.mandt
and bseg.bukrs = t001.bukrs
{
key bseg.bukrs,
key belnr,
key gjahr,
key buzei,
saknr,
augdt,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'WAERS'
dmbtr,
waers,
kokrs,
kostl,
@Environment.sql.passValue: true
cast('ORIGINAL' as abap.char( 10 )) as Source
}
union all
select from ZI_RM_BSEG {
key bukrs,
key belnr,
key GJAHR,
key BUZEI,
SAKNR,
AUGDT,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'WAERS'
DMBTR,
WAERS,
KOKRS,
KOSTL,
@Environment.sql.passValue: true
cast('REMOTE1' as abap.char( 10 )) as Source
}
Consumption View (Analytic Query)
@AbapCatalog.sqlViewName: 'ZCBSEG_INTEG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZC_BSEG_INTEG'
@Analytics.query: true
define view ZC_BSEG_INTEG as select from ZI_BSEG_INTEG {
@AnalyticsDetails.query.axis: #ROWS
bukrs,
belnr,
gjahr,
buzei,
@AnalyticsDetails.query.axis: #ROWS
saknr,
augdt,
dmbtr,
@AnalyticsDetails.query.axis: #COLUMNS
waers,
kokrs,
kostl,
@AnalyticsDetails.query.axis: #COLUMNS
Source
}
The example of URL of Web Dynpro Grid to run the Analytic Query is below.
https://vhcals4hcs.dummy.nodomain:44300/sap/bc/webdynpro/sap/fpm_bics_ovp?bsa_query=2CZCBSEG_INTEG&s...
* This URL would not work in your site, but is only the example. 2CZCBSEG_INTEG is the analytic query name (“2C<SQL View name>”).
UNION view by avoiding the access to remote CDS View thanks for UNION Node (view) pruning. It happens when setting the annotation "@Environment.sql.passValue: true" for constant value in each view.
UNION Node Pruning:
In the case the CDS View ZI_BSEG_INTEG (above), 2 select statements are combined with UNION, and the first statement accesses BSEG and T001 and the second statement accesses ZI_RM_BSEG (remote CDS View). In case the SELECT statement to ZI_BSEG_INTEG includes "where SOURCE = 'ORIGINAL'", which mean data can be retrieved only from BSEG and T001 and access to ZI_RM_BSEG is not needed, the access happens only to BSEG and T001 internally thanks to the SQL optimization.
SELECT Statement:
SELECT "BUKRS", "SOURCE", "WAERS", "SAKNR", COUNT(*) , SUM( "DMBTR" )
FROM "ZIBSEG_INTEG" "A1"
WHERE "MANDT" = ‘100’ AND "SOURCE" = 'ORIGINAL'
GROUP BY "BUKRS" , "SOURCE" , "WAERS" , "SAKNR"
ORDER BY "A1" . "BUKRS" , "A1" . "SOURCE" , "A1" . "WAERS" , "A1" . "SAKNR"
WITH HINT(RESULT_LAG ('hana_long')) WITH RANGE_RESTRICTION('CURRENT')
The Plan Visualization for the SELECT statement shows the acces happens only to BSEG and T001 as below.
Plan Visualization
Static Result Cache is available also for the CDS View using the Table Function accessing external system remotely as the source. Although update of the static cache is not in real-time normally, it would help improving the performance to access external system.
Firstly, the configuration of HANA should be changed as below (T-cd: DBACOCKPIT or Administration in HANA Studio).
indexserver.ini -> [result_cache] -> enabled = yes (default: no);
Then add the cache to the SQL View of the CDS View.
See more in SAP Note 2336344 - FAQ: SAP HANA Static Result Cache.
3. Embedded Machine Learning
CDS View ZI_RM_BSEG is used as direct consumption of Table Function is not possible in ISLM. See more in this blog about ISLM.Create Intelligent Scenario (Machine Learning Model) with Intelligent Scenario.
The created CDS View can be used as the Training Dataset and Apply Data set. (For Apply Dataset (data for which the value/attribute is predicted), it would be appropriate to create another custom CDS View).
With Intelligent Scenario Management, the model can be trained.
ABAP Apply View is generated.
4. Data extraction from external system
Data in external database can be extracted in S/4HANA via Virtual Table through table function with ABAP SQL or directly with Native SQL.
Sample program:
Through Table function:
Data transformation is possible with Native SQL in AMDP and ABAP SQL and ABAP syntax in ABAP Program
Directly from Virtual table in ABAP Program with Native SQL:
Data transformation is possible with Native SQL in ABAP Program.
Other ways to access external system (but too complex!)
Other are some other ways to create Virtual table or to read from the Virtual Table. But the way in this blog should be much simpler.
Other ways:
When creating Open ODS View in embedded BW functionality in S/4HANA, Virtual Table is created internally.
Virtual Table can be consumed in ABAP Program via ABAP External View, in which HANA Calculation View can be the source, and in which Virtual Table can be the source of HANA Calculation View. Virtual Table cannot be the source of ABAP External Table directly. But now ABAP External View is not recommended by the development team. See the comment in the blog. By adding the hint "no_calc_view_unfolding" = "1" in the HANA Calculation View, the performance would be optimized.
For Virtual Table internally generated by BW Open ODS View, firstly Composite Provider has to be created using the Open ODS View as the source and generate HANA External View from the Composite Provider to generate HANA Calculation View. It is not supported to use the generated Virtual Table directly. In addition, renaming of the field name to set to start with alphabet, not number (4~) to use it as the source of ABAP External View (Too complex!).
Enjoy data integration with data in external system in S/4HANA!
- SAP Managed Tags:
11 Comments
