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.
If you are on BW on HANA or BW/4HANA systems and would like to use ABAP controlled way, please refer to my previous blog. This does not require HANA Enterprise Edition.
Realtime (delta) Data Replication from SAP Analytics Cloud to BW/4 HANA
If you are on any ABAP systems and do not like to use the Smart Data Integration, you can implement this delta logic in ABAP, Data Intelligence, Integration Suite or other integration tools following this blog How to implement the delta logic in SAC Data Export Service Odata API at the Consumers side
Hints: Depends on the business needs and data volume, delta does not mean a better solution. You need some efforts to implement it in a proper way. If you are using SDI to get the data, I recommend you only consider delta when the full load is big enough eg: above 1000K and users can not wait more than several minutes. For a less than 1000k records SAC models, it is expected a full load takes less than 5 minutes in a normal situation. You can test both of course. The SDI replication task can be easily changed to full load w/o delta.
Prerequisites
- Data Export Service API is enabled. Here is how to Enable SAC Data Export Service
- DP Agent needs to be installed. Installation could refer to this link. Configuration to SAC is Configure HANA Smart Data Access sources.
- 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.
- 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. Here is how to enable it How to enable SAP HANA Web-based Development Workbench in SAP HANA.
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.
You could refer to the example in Load Behavior Options for Targets in Replication Tasks to decide which fits your delta.
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.
This is similar to my other blog Realtime (delta) Data Replication from SAP Analytics Cloud to BW/4 HANA. But the previous blog the realtime replication is controlled by SAP BW. Alternatively, the (real-time) replication process can also be controlled by an external application. In our blog, this external application is replication task. Differences could refer to this help document.
Here is the step by step screenshot
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.
More information about how BW could use generic delta for HANA could be found in the link as below.
Generic Delta for SAP HANA DataSources
The handling in the next steps is well integrated in BW. The steaming process chain could also be triggered. I will not elaborate here.
Finally words
It might not be easy in replication task to map to your final target table. If your have complicated logic, for other non BW systems, think about other ways to transfer the data in your HANA DB to your final table. This logic could be written in ABAP, HANA AMDP or modeling in local HANA DB. Please be free to share your experiences.
Learn more
- SAP Managed Tags:
1 Comment
