How to use Smart Data Integration Replication Task to get delta from SAP Analytics Cloud to HANA

Motivation

This blog is about how to use Replication Task in Smart Data Integration to replicate data from SAC to your on premise HANA.  You can replicate SAC planning transaction data not only with delta but also only full. The full load also applies to the analytic models in SAC. We will focus on delta in this blog.

Some customers have chosen this way as an implementation and like the flexibility in HANA replication task like filters, monitor and field mapping from SAC models to HANA DB tables. The others especially in S/4 HANA have concerns or restriction in company policies not to access the HANA DB level thus using SDI directly on HANA DB is no go for them. I would like to hear your feedback what are your preferred way for this integration.

Prerequisites

1. Data Export Service API  is enabled. 

2. DP Agent needs to be installed. Installation could refer to this link. Configuration to SAC is Configure HANA Smart Data Access sources. 

3. Enable relevant tools:to create HANA replication task, either  SAP HANA Web-based Development Workbench or Web IDE and SAP Business Application Studio should be used.

4. HANA Enterprise Edition license.

Architecture

How to read this blog

Chapter	Purpose	comments
1. How to create a simple replication task	Understand how the replication task and delta works between SACmodel and HANA DB tables.	Basic Knowledge
2. How to monitor replication tasks and subscriptions	Monitor	Optional to read
3. How to set filter and do field mapping in replication task	Advance topics	Optional to read
4. How to process the data in BW as next steps	Extended topics for BW: how to extract delta from local HANA DB tables.	Only relevant for BW on HANA and BW/4HANA

1. How to create a simple replication task

In this blog, we will use SAP HANA Web-based Development Workbench. If you have never use it before.

Step 1: check the  Remote Source in SAP HANA  is configured correctly.

In the workbench –> Catalog, you can expand the remote source to your SAC and see the models there.

Step 2: create a simple Replication Task

Here is the HELP document how to create a replication task.

Then you will open a replication task screen and can choose the Remote Source “SACDES” and your target HANA schema as below:

Select a remote object. Only FactData in a planning model choose “realtime” or “fulload only”. If you choose any model which does not support realtime (delta), you will get an error “API Provider cannot handle subscription creation” when saving the replication tasks. For the master data and other models, you can only load full.

In order to use delta, I choose realtime here.

Define the load behavior.   “Replicate with logical delete” is chosen . Two additional columns will be added automatically, which could be used later to detect delta to the next level Objects. For example, in BW HANA native datasource could be created with delta enabled.

Here are the differences.

• Replicate: Replicates changes in the source one-to-one in the target.
• Replicate, Preserve archived rows: Rows archived in the source are marked with a CHANGE_TYPE of M in the target.
• Replicate with logical delete: UPSERTS rows and includes CHANGE_TYPE and CHANGE_TIME columns in the target.
• Preserve all: INSERTS all rows and includes CHANGE_TYPE, CHANGE_TIME, and CHANGE_SEQUENCE columns in the target.

Step 3: Save the replication task

Saving the replication task will create virtual table and target table in HANA. The target table will store the data physically. Virtual table is only pointing to the SAC models.

You can see the virtual table and target table in HANA Web-based Development Workbench Catalog as below

Step 4: Execute the replication task

In this step, you will do the initial loading from SAC to HANA. After the initial loading has been done, the delta changes will be pulled into HANA every 5 minutes.

2. How to monitor replication tasks and subscriptions

You can launch monitor console by select a replication task , right click and will see as below

When the initial load has finished and the delta is ready to be replicated

you will see the task status “Completed” and Remote subscription monitor “replicating changes“.

you can navigate to different monitor screen

For example, in Remote Subscription, we can see all the subscription status, reset and drop the subscriptions.

you can configure the email notification as below

3. How to set filter and do field mapping in replication task

Filters could be set as below to restrict the data volume returned from SAC to HANA. Currently when the blog is written, one of the pain point in BW is there multiple conditions in the static filter of a Datasource is not possible. But here in HANA level, you are free to set many conditions. Just need to make sure it meets there SQL-where syntax.

In addition, you could do field mapping from your SAC models to the target tables. Create calculated fields are also possible.

4. How to process the data in BW as next steps

We will use this architecture. The upper part from virtual table to replicated table is using Replication task mentioned as above. Then we will use BW HANA datasource to point to this replicated table. It works for both BW on HANA and BW/4HANA. The interface might be a bit different.

Here is the step by step screenshot

Create a BW source system point to local HANA datasource.

Then create a new data source

Next

Choose delta properties : “_CHANGE_TIME”. This is generated when we created a replication task.

Choos Enable delta with Deletion, and column is “_CHANGE_TYPE”. This is generated when we created a replication task.