Using Remote Data Source in ICMR

[vincezk]

This blog introduces an approach to allow ICMR to read data from remote systems. If you have already read the blog "The Next Generation Intercompany Solution: Intercompany Matching & Reconciliation", you may know that ICMR can real-time read data in S/4HANA through a CDS view. However, in reality, a lot of data still exists in legacy systems, which makes intercompany matching still a big challenge. By leveraging SAP HANA Smart Data Access(SDA), ICMR can directly read data from the remote systems, thus provide the real-time matching and reconciliation capability in a distributed system landscape.

1. What is SDA

SDA allows SAP HANA to access remote data sources as if the data is stored locally. The so-called "virtual table" can be created to map to a table located in a remote database system. Virtual tables can be manipulated just like ordinary tables. The amazing thing is that SAP HANA can connect to a lot of database systems, like Oracle, MSSQL, MySQL, HADOOP, and so on. Check the available remote sources in the SAP Note 2600176. Also refer more description on SDA in the SAP Help Portal.

Warning: we got feedback from one customer that if the S/4HANA runs on Little Endian architecture, and the remote DB runs on Big Endian, then the connection is not possible.

2. Create Remote Source

In the following step-by-step guide, we access the data from a remote HANA-based ERP system. Since ICMR runs on SAP S/4HANA, then it is a HANA-to-HANA connection. But it is safe to anticipate that the same approach can work on HANA-to-anyDB connections.

Let's first create a remote source. You need proper permission to operate in both database systems. We will call the S/4HANA database system "ICMR server" where ICMR is running on, and the target HANA database system "remote server" where ICMR reads data from.

Log on to your ICMR database server using SAP HANA Studio. Expand the DB system and then the "Provisioning" folder, right click the folder "Remote Sources". In the context menu, choose "New Remote Source" to open the definition window.

Create a Remote Source

In the definition window, you need to give the following information:

1. A unique Source Name, like "ICMR" in the below example;


2. Based on which database system you want to connect with, you choose the right adapter. In this case, please choose "HANA(ODBC)";


3. In the "Server" field, enter the host name of the remote server;


4. In the "Port" field: enter the port of the remote database server, in this case "30215";


5. Since ICMR only reads data from the remote server, we can give "readonly" in the "DML Mode" field;


6. Credentials must be given. Usually, you can log on to the remote server with a technical user and its password. Of course, you can use other authentication methods provided by the adapter

Set Remote Source

After successfully saving your remote source definition, you can expand it to see all the DB schema belong to the remote source. If you expand a schema, you can see all the tables under it.

Check Remote Source

3. Create Virtual Table

Now that you have built a connection to the remote ERP database, you can create virtual tables in the ICMR server. Let's say you want to read the data from table "BSEG" in the remote ERP system.

To create virtual tables in SAP HANA, you first need to know your HANA licensing model. Because not all the editions are allowed to do data modeling directly at database level. For S/4HANA users, we need to care following 2 editions, and choose either of the 2 options:

1. SAP HANA Runtime Edition for Apps and SAP BW (HANA REAB),


2. SAP HANA Full Use Edition

3.1 Following steps are only for HANA REAB:

In the ICMR server, you create an ABAP report "ZICA_CREATE_VIRTUAL_TABLE" by copying the following code lines:

*&---------------------------------------------------------------------*

*& Report ZICA_CREATE_VIRTUAL_TABLE

*&---------------------------------------------------------------------*

*& Use this report to create/delete a virtual table in SAP<SID> schema.

*& Prerequites:

*&  1. You must have a remote source created in HANA.

*&  2. DB user SAP<SID> has "create virtual table" permission on

*&     the remote source.

*&  "Grant CREATE VIRTUAL TABLE on remote source <rs_name> to SAP<SID>"

*& Result:

*&  A virtual table is created in schema SAP<SID> with prefix '/1BCAMDP/'

*&---------------------------------------------------------------------*

report zica_create_virtual_table.



parameters: vt_name type string obligatory, " the to-be-created virtual table name

            remote  type string, " remote source

            schema  type string, " the DB schema in the remote source

            rt_name type string, " the remote table name

            delete  as checkbox. " delete the virtual table



at selection-screen.

  if delete = abap_false and

     ( remote is initial or schema is initial  or rt_name is initial ).

    message 'Parameters remote, schema, and rt_name are mandatory!' type 'E'.

  endif.



start-of-selection.

  data(lv_virtual_table_name) = |"/1BCAMDP/{ vt_name }"|.

  data(lv_remote_table_name) = |"{ remote }"."NULL"."{ schema }"."{ rt_name }"|.



  data lv_ddl_string type string.

  if delete = abap_false.

    lv_ddl_string = |create virtual table { lv_virtual_table_name } at { lv_remote_table_name }|.

  else.

    lv_ddl_string = |drop table { lv_virtual_table_name }|.

  endif.



  try.

      data(mo_sql) = new cl_sql_statement( ).

      mo_sql->execute_ddl( lv_ddl_string ).

      message 'Action is successfully performed!' type 'S'.

    catch cx_sql_exception into data(lo_exc).

      raise shortdump lo_exc.

  endtry.

Also add the "CREATE VIRTUAL TABLE" permission for the DB user SAP<SID> if the remote source is not created by the user.

Grant CREATE VIRTUAL TABLE on remote source ICMR to <Schema of S/4HANA>;

Now activate and execute the report. Input the following parameters:

• VT_NAME: the to-be-created virtual table name.


• REMOTE: the remote source name.


• SCHEMA: the remote database schema.


• RT_NAME: the remote table name.

Create Virtual Table using ABAP

Once successfully executed, the virtual table is created in the namespace '/1BCAMDP/ '. In above example, the virtual table is created as '/1BCAMDP/REMOTE_BSEG'. You follow the same steps to create REMOTE_BKPF and REMOTE_T001. If you want to delete the virtual table, just give the virtual table name and check the 'DELETE' flag.

Please note the limitations:

1. No transportation. You have to create the virtual tables manually in your D, Q, & P systems.


2. Missing during system upgrading. Since the virtual tables are created in the namespace '/1BCAMDP/ ' which stands for the temporary objects, they will be cleared during the system upgrading. And after the upgrading, you must manually re-create them.

You can resolve the limitations by extending the solution to adapt the ABAP-Managed HDI Container.

3.2 Following steps are only for HANA Full Use Edition:

If you are using the HANA Full Use Edition, then you can create virtual tables directly in HANA. In this way, you have more options to manage the lifecycle of the objects.

In the ICMR server(HANA Studio), you use a DB schema other than the standard S/4HANA schema(you are not allowed to create tables directly in the SAP standard schema). Expand the schema, then right click the folder "Tables". In the context menu, you choose "New Virtual Table".

Create Virtual Table

In the definition window, you should give a unique table name, like "REMOTE_BSEG". Click the button "Browse…" to open a dialog, in which you choose the target schema in the remote source and filter "BSEG" to find the table. After selecting the table, you will see a definition like below.

Define Virtual Table

You follow the same steps to create REMOTE_BKPF and REMOTE_T001. To make sure ICMR can access the virtual tables in the schema, you should execute following SQL(replace the placeholders with yours):

GRANT SELECT ON SCHEMA <Schema of Virtual Table> TO <Schema of S/4HANA>;

4. Create Table Function

Because the virtual tables are not registered in ABAP DDIC, they cannot be accessed directly by ABAP CDS technology. To workaround this, you need table function.

Use ABAP Development Tool(Eclipse-based) to log on to your ICMR server, expand to your local objects folder, and right click "Data Definitions". In the context menu, choose "New Data Definition".

Create Table Function

After giving a unique name("ZICMR_TF" in this example) and some description, you can copy the below scripts to the editor. You just save it without activating. The scripts define the fields in the data structure. With each field, a Data Element is assigned(after the colon).

@ClientHandling.type: #CLIENT_DEPENDENT

 define table function ZICMR_TF

 returns

 {

  RCLNT : mandt;

  method_id : ica_method_id;

  DOCNR : ica_docnr;

  DOCLN : ica_docln;

  GRREF : ica_grref;

  PSTAT : ica_pstat;

  CSTAT : ica_cstat;

  DUE_DATE : ica_due_date;

  CLEARING_STATUS : ica_clearing_status;

  rbukrs : bukrs;

  ref_belnr : belnr_d;

  gjahr : gjahr;

  ref_docln : docln6;

  xopvw : xopvw;

  augdt : augdt;

  augbl : augbl;

  auggj : augbl;

  bschl : bschl;

  koart : koart;

  umskz : umskz;

  rwcur : waers;

  wsl   : fins_vwcur12;

  rhcur : waers;

  hsl   : fins_vwcur12;

  zuonr : dzuonr;

  sgtxt : sgtxt;

  rcomp : rcomp_d;

  rassc : rassc;

  racct : bilkt_ska1;

  lracct : hkont;

  kunnr : kunnr;

  lifnr : lifnr;

  awtyp : awtyp;

  awkey : awkey;

  awsys : awsys;

  budat : budat;

  bldat : bldat;

  blart : blart;

  xblnr : xblnr1;

  bktxt : bktxt;

  xref1_hd : xref1_hd;

  xref2_hd : xref2_hd;

  usnam : usnam;

  cpudt : cpudt;

  cputm : cputm;

  aedat : aedat_bkpf;

  bvorg : bvorg;  

  xreversing : co_stflg;

  xreversed : co_stokz;

 }

 implemented by method

zicmr_remote_sources=>CALL_01;

The data reading logic is implemented in the AMDP method "zicmr_remote_sources=>CALL_01". You can create the AMDP method in the ABAP Development Tool too. You just create a normal ABAP class, within which you add a static method "CALL_01". Just refer the codes below.

class zicmr_remote_sources definition

  public

  final

  create public .

  public section.

    interfaces if_amdp_marker_hdb .

    class-methods call_01

        for table function zicmr_tf.

  protected section.

  private section.

endclass.

class zicmr_remote_sources implementation.

  method call_01

    by database function for hdb language sqlscript

    options read-only.

    return

    select

      SESSION_CONTEXT('CDS_CLIENT')                                                 as RCLNT,

      ''                                                                            as method_id,

      '0000000000'                                                                  as DOCNR,

      0                                                                             as DOCLN,

      '000000000000'                                                                as GRREF,

      '00'                                                                          as PSTAT,

      ''                                                                            as CSTAT,

      '00000000'                                                                    as DUE_DATE,

      case when xopvw = 'X'

           then ( case when augbl <> '' then '3'

                                        else '1'

                 end  )

         else '0'

         end                                                                        as CLEARING_STATUS,

      bseg.bukrs                                                                    as rbukrs,

      bseg.belnr                                                                    as ref_belnr,

      bseg.gjahr                                                                    as gjahr,

      concat('000', bseg.buzei)                                                     as ref_docln,

      bseg.xopvw,

      bseg.augdt,

      bseg.augbl,

      bseg.auggj,

      bseg.bschl,

      bseg.koart,

      bseg.umskz,

      bkpf.waers                                                                    as rwcur,

      case when bseg.shkzg = 'H' then 0 - wrbtr

           else wrbtr end                                                           as wsl,

      t001.waers                                                                    as rhcur,

      case when bseg.shkzg = 'H' then 0 - dmbtr

           else dmbtr end                                                           as hsl,

      bseg.zuonr,

      bseg.sgtxt,

      t001.rcomp,

      bseg.vbund                                                                    as rassc,

      bseg.altkt                                                                    as racct,

      bseg.hkont                                                                    as lracct,

      bseg.kunnr,

      bseg.lifnr,

      bseg.awtyp,

      bseg.awkey,

      bseg.awsys,

      bseg.h_budat                                                                  as budat,

      bseg.h_bldat                                                                  as bldat,

      bseg.h_blart                                                                  as blart,

      bkpf.xblnr,

      bkpf.bktxt,

      bkpf.xref1_hd,

      bkpf.xref2_hd,

      bkpf.usnam,

      bkpf.cpudt,

      bkpf.cputm,                   

      bkpf.aedat,

      bkpf.bvorg,

      bkpf.xreversing,

      bkpf.xreversed

      from "/1BCAMDP/REMOTE_BSEG" as bseg        /* or use "ZHANGVIN"."REMOTE_BSEG" */

      inner join "/1BCAMDP/REMOTE_BKPF" as bkpf  /* or use "ZHANGVIN"."REMOTE_BKPF" */

              on bseg.mandt = bkpf.mandt

             and bseg.bukrs = bkpf.bukrs

             and bseg.belnr = bkpf.belnr

             and bseg.gjahr = bkpf.gjahr

      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 = '910'

        and bseg.h_monat > '00'

      ;

  endmethod.

endclass.

In the above code, it reads data from the remote tables with necessary conversion and filtering. One thing needs be mentioned is the ABAP Client handling if you are connecting with a SAP system. In above example, we fixed client to "910" in the remote system.

You can now activate the table function and the method together.

5. Create CDS View

Based on the table function "ZICMR_TF", you can create the CDS view. Just like creating table function, you right click "Date Definitions", and choose "New Data Definition". Then copy the following scripts to the editor.

@AbapCatalog.sqlViewName: 'ZICMRBSEGEV'

@EndUserText.label: 'ICMR Entry View Based on remote BSEG'

@ClientHandling.type: #CLIENT_DEPENDENT

@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.serviceQuality: #X

@ObjectModel.usageType.dataClass: #MIXED

@ObjectModel.usageType.sizeCategory: #XL

@AbapCatalog.compiler.compareFilter:true

define view ZICMR_BSEG_ENTRY_VIEW

  as select from ZICMR_TF as A

      inner join finsc_fisc_date as B

              on A.budat = B.calendar_date

 {

  A.rclnt,

  A.method_id,                                            

  A.DOCNR,

  A.DOCLN,

  A.GRREF,

  A.PSTAT,

  A.CSTAT,

  A.DUE_DATE,

  A.CLEARING_STATUS,

  A.rbukrs,

  A.ref_belnr,

  A.gjahr,

  A.ref_docln,

  B.fiscal_year                                                   as ryear,

  B.fiscal_period                                                 as poper,

  cast (B.fiscal_year_period as fis_jahrper_conv preserving type) as fiscyearper,

  B.fiscal_year_variant                                           as periv,

  A.xopvw,

  A.augdt,

  A.augbl,

  A.auggj,

  A.bschl,

  A.koart,

  A.umskz,

  @Semantics.currencyCode

  A.rwcur,

  @Semantics.amount.currencyCode: 'RWCUR'

  A.wsl,

  @Semantics.currencyCode

  A.rhcur,

  @Semantics.amount.currencyCode: 'RHCUR'

  A.hsl,

  A.zuonr,

  A.sgtxt,

  A.rcomp,

  A.rassc,

  A.racct,

  A.lracct,

  A.kunnr,

  A.lifnr,

  A.awtyp,

  A.awkey,

  A.awsys,

  A.budat,

  A.bldat,

  A.blart,

  A.xblnr,

  A.bktxt,

  A.xref1_hd,

  A.xref2_hd,

  A.usnam,

  dats_tims_to_tstmp( A.cpudt,

                      A.cputm,

                      abap_system_timezone( $session.client,'NULL' ),

                      $session.client,

                      'NULL' )                                    as timestamp,

  A.aedat,

  A.bvorg,                                                                                        

  A.xreversing,

  A.xreversed

}

The above CDS view reads data from the table function. You can join other ABAP DDIC tables, and do further conversion and filtering. In the CDS view, it joins with the table "FINSC_FISC_DATE" which is used to convert the posting date to fiscal year and period based on the given fiscal year variant.

Please also note the conversion of "cpudt" and "cputm" to a UTC timestamp. In above example, it assumes the ABAP system timezone is the same between the ICMR server and the remote server. If your case is not like this, please hard code the timezone according to your remote server.

Once you have successfully activated the CDS view, you can run a preview on the data to have a check. Please avoid reading too much data by adding necessary filters. Otherwise, you may get "out of memory" error.

You may ask if it is possible to match the data combined from the remote system and the ICMR server. The answer is yes. In the above CDS view script, you can union the data from the ICMR server by direct selecting from the local table BSEG.  See below script example:

define view ZICMR_BSEG_ENTRY_VIEW

  as select from ZICMR_TF as A

      inner join finsc_fisc_date as B

              on A.budat = B.calendar_date

 {

   <field list>

 }

 union all

 select from BSEG as A

      inner join finsc_fisc_date as B

              on A.budat = B.calendar_date

 {

   <field list>

 }

6. Create ICMR Data Source and Matching Method

With all those technical stuff done, you can now do some simple configurations. Log on to the ICMR server with SAP Logon. Enter TCode "ICADS" to define your own ICMR Data Source.

Create ICMR Data Source

Since the data structure of the remote CDS view is the same with the standard Data Source SF_AR_AP_ENTRY_VIEW, you can just copy from SF_AR_AP_ENTRY_VIEW and change the main CDS view to your remote CDS view. Then just save it.

Please also notice the Unit Entity CDS View "ICA_COMPANY". You must make sure all the company master data can be read from the CDS view. You can achieve this either by inserting the company master data into table T880 in the ICMR server, or using the same approach to read it from the remote system.

Based on the Data Source, you can create a Matching Method. Make sure you assign the remote Data Source to the method.

Define Matching Method

Please refer the SAP Help Portal on how to define Data Source and Matching Method.

Finally, you can check the data in the Fiori App "Manage Assignment". You can leverage all the ICMR functionalities without any limitation.

Check in Manage Assignment

7. Conclusion

With SDA, ICMR can read the data from remote systems directly. The above example demonstrates reading data from a HANA-based ERP system. You can achieve reading data from other systems as well. And you can read data from multiple systems by merging them together using the "UNION" operator in the CDS view.

The integration happens deep in the database, which is transparent to the application level. The benefit is obvious, it reduces the cost in copying data, and allows real-time matching and reconciliation in a distributed landscape. However, you should still be aware of following potential issues:

1. Performance will not be as good as the local cases. Because the data go through the network among the database servers. The network throughput between the database servers should be guaranteed.


2. Memory Consumption could be high as the filtering may not be well pushed to the remote systems. You should test and allocate reasonable query memory size in your S/4HANA database.


3. Error Handling would be difficult because the ICMR application has no ideas on what is happening in the remote systems.

ICMR also supports write-back adjustments to the source. I would NOT recommend you allowing write access to the virtual tables through SDA. Instead, you should call the APIs provided by the remote systems to do the adjustments.