In days gone by, I've found people, with the question: How many ways I can replicate data on HANA? For this reason ,from here,We will see a little guide of the 5 different ways Data Replication:
SAP HANA - ETL Based Replication
SAP HANA - Log Based Replication
SAP HANA - DXC Method
SAP HANA - CTL Method
SAP HANA - MDX Provider
SAP HANA - Data Replication Overview
SAP HANA Replication allows migration of data from source systems to SAP HANA database. Simple way to move data from existing SAP system to HANA is by using various data replication techniques.
System replication can be set up on the console via command line or by using HANA studio. The primary ECC or transaction systems can stay online during this process. We have three types of data replication methods in HANA system
SAP LT Replication method
ETL tool SAP Business Object Data Service (BODS) method
Direct Extractor connection method (DXC)
SAP LT Replication Method
SAP Landscape Transformation Replication is a trigger based data replication method in HANA system. It is a perfect solution for replicating real time data or schedule based replication from SAP and non-SAP sources. It has SAP LT Replication server, which takes care of all trigger requests. Replication server can be installed as standalone server or can run on any SAP system with SAP NW 7.02 or above.
There is a Trusted RFC connection between HANA DB and ECC transaction system, which enables trigger based data replication in HANA system environment.
Advantages of SLT Replication
SLT Replication method allows data replication from multiple source systems to one HANA system and also from one source system to multiple HANA systems.
SAP LT uses trigger based approach. It has no measureable performance impact in source system.
It also provides data transformation and filtering capability before loading to HANA database.
It allows real-time data replication, replicating only relevant data into HANA from SAP and non-SAP source systems.
It is fully integrated with HANA System and HANA studio.
Creating a Trusted RFC Connection in ECC system
On your source SAP system A1 you want to setup a trusted RFC towards target system B1. When it is done, it would mean that when you are logged onto AA1 and your user has enough authorization in B1, you can use the RFC connection and logon to B1 without having to re-enter user and password.
Using RFC trusted/trusting relationship between two SAP systems, RFC from a trusted system to a trusting system, password is no required for logging on to the trusting system.
Open SAP ECC system using SAP logon. Enter transaction number sm59 → this is transaction number to create a new Trusted RFC connection → Click on icon to open a new connection wizard → click on Create and new window will open.
RFC Destination ECCHANA (enter name of RFC destination) Connection Type − 3 (for ABAP system)
Go to Technical Setting Enter Target host − ECC system name, IP and enter System number.
Go to Logon & Security tab, Enter Language, Client, ECC system user name and password.
Click on the Save option at the top.
To Configure RFC connection
In SLT system,Run transaction − ltr (to configure RFC connection) → New browser will open → enter ECC system user name and password and logon.
Click on New → New Window will open → Enter configuration name → Click Next → Enter RFC Destination (connection name created earlier), Use search option, choose name and click next.
In Specify Target system, Enter HANA system admin user name & password, host name, Instance number and click next. Enter No of Data transfer jobs like 007(it cannot be 000) → Next → Create Configuration.
Now go to HANA Studio to use this connection −Go to HANA Studio → Click on Data Provisioning → choose HANA system.
Select source system (name of trusted RFC connection) and target schema name where you want to load tables from ECC system. Select tables you want to move to HANA database → ADD → Finish.
SAP HANA - ETL Based Replication
SAP HANA ETL based replication uses SAP Data Services to migrate data from SAP or non-SAP source system to target HANA database. BODS system is an ETL tool used to extract, transform and load data from source system to target system.
It enables to read the business data at Application layer. You need to define data flows in Data Services, scheduling a replication job and defining source and target system in data store in Data Services designer.
How to use SAP HANA Data Services ETL based Replication?
Login to Data Services Designer (choose Repository) → Create Data store
For SAP ECC system, choose database as SAP Applications, enter ECC server name, user name and password for ECC system, Advanced tab choose details as instance number, client number, etc. and apply.
This data store will come under local object library, if you expand this there is no table inside it.
Right click on Table → Import by name → Enter ECC table to import from ECC system (MARA is default table in ECC system) → Import → Now expand Table → MARA → Right Click View Data. If data is displayed, Data store connection is fine.Now, to choose target system as HANA database, create a new data store. Create Data store → Name of data store SAP_HANA_TEST → Data store type (database) → Database type SAP HANA → Database version HANA .Enter HANA server name, user name and password for HANA system and OK.
This data store will be added to Local Object Library. You can add table if you want to move data from source table to some specific table in HANA database. Note that target table should be of similar datatype as source table.
Creating a Replication Job
Create a new Project → Enter Project Name → Right Click on Project name → New Batch Job → Enter job name.
From right side tab, choose work flow → Enter work flow name → Double click to add it under batch job → Enter data flow → Enter data flow name → Double click to add it under batch job in Project area Save all option at top.
Drag table from First Data Store ECC (MARA) to work area. Select it and right click → Add new → Template table to create new table with similar data types in HANA DB → Enter table name, Data store ECC_HANA_TEST2 → Owner name (schema name) → OK
Drag table to front and connect both the table → save all. Now go to batch job → Right Click → Execute → Yes → OK
Once you execute the Replication job, you will get a confirmation that job has been completed successfully.
Go to HANA studio → Expand Schema → Tables → Verify data. This is manual execution of a batch job.
Scheduling of Batch Job
You can also schedule a batch job by going to Data Services Management console. Login to Data Services Management Console.
Choose the repository from left side → Navigate to 'Batch Job Configuration' tab, where you will see the list of jobs → Against the job you want to schedule → click on add schedule → Enter the 'schedule name' and set the parameters like (time, date, reoccurring etc.) as appropriate and click on 'Apply'.
SAP HANA - Log Based Replication
This is also known as Sybase Replication in HANA system. The main components of this replication method are the Sybase Replication Agent, which is part of the SAP source application system, Replication agent and the Sybase Replication Server that is to be implemented in SAP HANA system.
Initial Load in Sybase Replication method is initiated by Load Controller and triggered by the administrator, in SAP HANA. It informs R3 Load to transfer initial load to HANA database. The R3 load on source system exports data for selected tables in source system and transfer this data to R3 load components in HANA system. R3 load on target system imports data into SAP HANA database.
SAP Host agent manages the authentication between the source system and target system, which is part of the source system. The Sybase Replication agent detects any data changes at time of initial load and ensures every single change is completed. When th ere is a change, update, and delete in entries of a table in source system, a table log is created. This table log moves data from source system to HANA database.
Delta Replication after Initial Load
The delta replication captures the data changes in source system in real time once the initial load and replication is completed. All further changes in source system are captured and replicated from source system to HANA database using above-mentioned method.
This method was part of initial offering for SAP HANA replication, but not positioned/supported anymore due to licensing issues and complexity and also SLT provides the same features.
Note − This method only supports SAP ERP system as data source and DB2 as database.
SAP HANA - DXC Method
Direct Extractor Connection data replication reuses existing extraction, transformation, and load mechanism built into SAP Business Suite systems via a simple HTTP(S) connection to SAP HANA. It is a batch-driven data replication technique. It is considered as method for extraction, transformation, and load with limited capabilities for data extraction.
DXC is a batch driven process and data extraction using DXC at certain interval is enough in many cases. You can set an interval when batch job executes example: every 20 minutes and in most of cases it is sufficient to extract data using these batch jobs at certain time intervals.
Advantages of DXC Data Replication
This method requires no additional server or application in the SAP HANA system landscape.
DXC method reduces complexity of data modeling in SAP HANA as data sends to HANA after applying all business extractor logics in Source System.
It speeds up the time lines for SAP HANA implementation project
It provides semantically rich data from SAP Business Suite to SAP HANA
It reuses existing proprietary extraction, transformation, and load mechanism built into SAP business Suite systems over a simple HTTP(S) connection to SAP HANA.
Limitations of DXC Data Replication
Data Source must have a predefined mechanism for extraction, transformation and load and if not we need to define one.
It requires a Business Suite System based on Net Weaver 7.0 or higher with at least below SP: Release 700 SAPKW70021 (SP stack 19, from Nov 2008).
Configuring DXC Data Replication
Enabling XS Engine service in Configuration tab in HANA Studio − Go to Administrator tab in HANA studio of system. Go to Configuration → xsengine.ini and set instance value to 1.
Enabling ICM Web Dispatcher service in HANA Studio − Go to Configuration → webdispatcher.ini and set instance value to 1.
Enabling ICM Web Dispatcher service in HANA Studio − Go to Configuration → webdispatcher.ini and set instance value to 1.
It enables ICM Web Dispatcher service in HANA system. Web dispatcher uses ICM method for data read and loading in HANA system.
Setup SAP HANA Direct Extractor Connection − Download the DXC delivery unit into SAP HANA. You can import the unit in the location /usr/sap/HDB/SYS/global/hdb/content.
Import the unit using Import Dialog in SAP HANA Content Node → Configure XS Application server to utilize the DXC → Change the application_container value to libxsdxc
Creating a HTTP connection in SAP BW − Now we need to create http connection in SAP BW using transaction code SM59.
Input Parameters − Enter Name of RFC Connection, HANA Host Name and Instance Number>
Setting up BW Parameters for HANA − Need to Setup the Following Parameters in BW Using transaction SE 38. Parameters List .
PSA_TO_HDB_DESTINATION− we need to mention where we need to move the Incoming data (Connection Name created using SM 59)
PSA_TO_HDB_SCHEMA− To which Schema the replicated data need to assign
PSA_TO_HDB− GLOBAL To Replicate All data source to HANA. SYSTEM – Specified clients to Use DXC. DATASOURCE – Only Specified Data Source are used for
PSA_TO_HDB_DATASOURCETABLE− Need to Give the Table name having the List of data sources which are used for DXC.
Data Source Replication
Install data source in ECC using RSA5.
Replicate the metadata using specified application component (data source version Need to 7.0, if we have 3.5 version data source we need to migrate that. Active the data Source in SAP BW. Once data source is activated in SAP BW it will create the following Table in Defined schema −
/BIC/Adata source>00 – IMDSO Active Table
/BIC/Adata source>40 –IMDSO Activation Queue
/BIC/Adata source>70 – Record Mode Handling Table
/BIC/Adata source>80 – Request and Packet ID information Table
BIC/Adata source>A0 – Request Timestamp Table
RSODSO_IMOLOG - IMDSO related table. Stores information about all data sources related to DXC.
Now data is successfully loaded into Table /BIC/A0FI_AA_2000 once it is activated.
SAP HANA - CTL Method
Open SAP HANA Studio → Create Schema under Catalog tab. Start here>
Prepare the data and save it to csv format. Now create file with “ctl” extension with following syntax −
---------------------------------------import data into table Schema.
"Table name"from 'file.csv'records delimited by '\n'fields delimited by ','Optionally enclosed by '"'error log 'table.err'-----------------------------------------
Transfer this “ctl” file to the FTP and execute this file to import the data −
import from ‘table.ctl’
Check data in table by going to HANA Studio → Catalog → Schema → Tables → View Content
SAP HANA - MDX Provider
MDX Provider is used to connect MS Excel to SAP HANA database system. It provides driver to connect HANA system to Excel and is further, used for data modelling. You can use Microsoft Office Excel 2010/2013 for connectivity with HANA for both 32 bit and 64 bit Windows.
SAP HANA supports both query languages − SQL and MDX. Both languages can be used: JDBC and ODBC for SQL and ODBO is used for MDX processing. Excel Pivot tables use MDX as query language to read data from SAP HANA system. MDX is defined as part of ODBO (OLE DB for OLAP) specification from Microsoft and is used for data selections, calculations and layout. MDX supports multidimensional data model and support reporting and Analysis requirement.
MDX provider enables the consumption of Information views defined in HANA studio by SAP and non-SAP reporting tools. Existing physical tables and schemas presents the data foundation for Information models.
Once you choose SAP HANA MDX provider from the list of data source you want to connect, pass HANA system details like host name, instance number, user name and password.
Once the connection is successful, you can choose Package name → HANA Modeling views to generate Pivot tables.
MDX is tightly integrated into HANA database. Connection and Session management of HANA database handles statements that are executed by HANA. When these statements are executed, they are parsed by MDX interface and a calculation model is generated for each MDX
statement. This calculation model creates an execution plan that generates standard results for MDX. These results are directly consumed by OLAP clients.
To make MDX connection to HANA database, HANA client tools are required. You can download this client tool from SAP market place. Once installation of HANA client is done, you will see the option of SAP HANA MDX provider in the list of data source in MS Excel.