I want it all and I want it now! Smart Data Integration (SDI) – Initial Load and Real-time Replication

Introduction

One of the most common situations during the data replication process is that we have two (or more) systems and we need to constantly replicate existing and newly created/changed data from one to another and then track all the changes in all these available systems.

I think that immortal Freddie Mercury actually dreamt about an initial load and real time data replication.

Example of Realtime Flowgraph

We create a flowgraph that will be working in the Realtime mode. For this particular example, we will use a single HANA instance.

First of all, we create two Remote sources using HanaAdapter for source and target tables.

Then we create test database tables:

• source

• target

As we already have created test database tables we should create corresponding virtual tables in our schema TEST_SAP_2_ORA:

Flowgraph creation

Next, we go to SAP HANA Web-based Development Workbench Editor and create a Flowgraph:

To set Flowgraph as Realtime we should mark a corresponding checkbox in source table:

Keep in mind that we can have only ONE table per flowgraph marked as Real-time.

In Filter node we will create and map field CHANGE_TYPE:

In Data Sink node define Writer Type according to our needs. Then save and activate the Flowgraph.

In order to simulate the initial load we just Execute the Flowgraph and control the values in the target table.

In source table we have the following set of data:

After initial load we see in the target table the same data with mapped Change Type:

Now we need to proceed with Realtime replication by adding another entry to the source table. By saving the entry we assume to have automatically created an entry in the target table.

As we see, a new entry appeared in the target table automatically.

The basic principle of Realtime replication is trigger-based processing. For the user executing the Flowgraph system generates a set of triggers that track any changes (Insert, Update, Delete) in the source table and then update the target table with corresponding data.

The above-presented design is just an example that fits the purpose of sending data from one table to another with updates reflected as inserts. With the flowgraph palette, you are able to create other designs that would fit your specific scenarios.

Thank you for your attention. In case you’d have any questions about SDI please feel free to comment under the blog.