Skip to Content
Technical Articles

Using Remote Data Source in ICMR

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. More details can be found in SAP Help Portal.

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%20a%20Remote%20Source

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%20Remote%20Source

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%20Remote%20Source

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.

In the ICMR server, you find a DB schema other than the standard S/4HANA schema(you are not allowed to create tables in SAP standard schema bypassing ABAP DDIC). Expand the schema, then right click the folder “Tables”. In the context menu, you choose “New Virtual Table”.

Create%20Virtual%20Table

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%20Virtual%20Table

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%20Table%20Function

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 "ZHANGVIN"."REMOTE_BSEG" as bseg
      inner join "ZHANGVIN"."REMOTE_BKPF" as bkpf
              on bseg.mandt = bkpf.mandt
             and bseg.bukrs = bkpf.bukrs
             and bseg.belnr = bkpf.belnr
             and bseg.gjahr = bkpf.gjahr
      inner join "ZHANGVIN"."REMOTE_T001" as 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.

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%20ICMR%20Data%20Source

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%20Matching%20Method

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%20in%20Manage%20Assignment

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.

2 Comments
You must be Logged on to comment or reply to a post.