Friday, 22 September 2023

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


SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

Step 2: create a simple Replication Task

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

you can configure the email notification as below

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

Here is the step by step screenshot

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

Create a BW source system point to local HANA datasource.

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

Then create a new data source

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

Next

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

SAP HANA, SAP HANA Career, SAP HANA Jobs, SAP HANA Prep, SAP HANA Tutorial and Materials, SAP HANA Guides, SAP HANA Certification, SAP HANA Tutorial

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

No comments:

Post a Comment