Real-time Replication task using SAP Business Application Studio

[apoorva_nagaraju]

SAP Business Application Studio is a comprehensive browser-based IDE available on SAP Business Technology Platform for the development of complex applications comprised of web-based or mobile UIs, business logic, and extensive SAP HANA data models.

Designed to support developers who use SAP HANA, SAP Business Application Studio provides various tools, such as syntax-aware editors for code and SAP HANA artifacts, and calculation views, as well as inspection, testing, and debugging tools.

Pre-requisites

• An SAP HANA database (Source system used is Hana Cloud)


• Business Application Studio


• Data Provisioning Agent


• Connection set up between HANA and Data Provisioning Agent


• A source system connected with agent to target system (Target system used is HANA On-premise. SDI is supported with many other Databases)

Create a user in HANA for the User Provided Service

Create a user on the HANA database which has the privilege to grant access to create virtual tables, create remote subscriptions and to process remote subscriptions.

• DROP USER MYUSER;


• CREATE USER MYUSER PASSWORD "<password>" NO FORCE_FIRST_PASSWORD_CHANGE;


• GRANT CREATE VIRTUAL TABLE ON REMOTE SOURCE "<remote_source>" TO MYUSER WITH GRANT OPTION;


• GRANT ALTER ON REMOTE SOURCE "<remote_source>" TO MYUSER WITH GRANT OPTION;


• GRANT CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE "<remote_source>" TO MYUSER WITH GRANT OPTION;


• GRANT PROCESS REMOTE SUBSCRIPTION EXCEPTION ON REMOTE SOURCE "<remote_source>" TO MYUSER WITH GRANT OPTION;

Create BAS project

1. Login into BTP Cockpit and launch SAP Business Application Studio.

2. Enter credentials and login to the application. A new tab opens and the welcome page for SAP Business Application Studio loads.


3. Click on Create Dev Space under Dev Spaces and Select SAP HANA Native Application to create a new Dev Space

 

Create SAP HANA Database Project

1. From the SAP Business Application Studio Welcome tab, click Start from template Create a new project.


2. Choose SAP HANA Database Project and click Start.


3. Enter details such as Project Name, DB module name, Select SAP HANA Database version as HANA CLOUD, choose bind the DB module to CF service instance.


4. Under Bind to HDI Container service, either create new HDI service Instance or reuse the existing one. Also, if there are multiple SAP HANA Database instances in the CF, then choose the target SAP HANA database instance and click on Finish.

5. Upon completion of the project generation, you will see a message. Press the Open in New Workspace button in this success message to open the new project in the IDE.

 Create a user-provided service

You have already created a user called MYUSER that has required privileges. You will now create a user-provided service to access the Remote Sources through the user MYUSER from your HANA DB Project in the Business Application Studio.

1. Use add database connection option in the SAP HANA PROJECTS view

2. In the Add Database Connection wizard that opens, select either Existing User-Provided service instance as the Connection Type or Create User-Provided service instance by providing the required details and Add
   

3. From the SAP HANA PROJECTS view, you should see that the User Provided Service is now part of the Database Connections of your project and also updated in the mta.yaml

 Grant permissions to technical users

The HDI container owner and runtime users need privileges on the remote source to be able to browse remote tables, create virtual tables, and create remote subscriptions. You will now create an artifact that grants access to the two technical users of your HDI container. These are not real users, but technical ones.

1. Create a new file under db > src


2. Name it as grants.hdbgrants and use the below code and Save

3. After the file is saved, deploy the file from the SAP HANA PROJECTS.

Creation of Replication Task

1. Click on View > Find Command > Search for Create HANA Database Artifact


2. Chose Artifact Type as Replication Task and provide artifact name and create

3. Open REPTASK1, click on connect button and choose the remote source

4. Select the table that needs to be replicated and save the task.

Create Expression in the Replication Task

1. Select a replication object. Click Edit Details.


2. Click Projection, then perform one or both of the following steps:

• To create a column mapping expression, choose Target Table Columns to edit or add a column. Click in the Mapping column to create the expression.


• To filter the replication data, click Filter Target Table Rows.

3. Select the columns to use in the expression. You can drag and drop the column names from the list and place them in the Enter filter expression box.


4. Select one of the available functions from the categories in the Functions pane.


5. Click or type any operators to complete the expression.


6. Save the replication task.

Deploy the replication task from the SAP HANA PROJECTS. Open Database Explorer and you can find two tables source and target created. Execute the procedure to replicate data into the target table.

Conclusion

BAS IDE editor for SDI replication tasks provides you a toolset similar to the Web IDE editor to create and manage real-time replications. This blog post provides you details on how to create Database Project in Business Application Studio, pre-requisites for accessing the remote sources, creation of user provided service, replication task and applying filter for selective data.

In next blog we can learn on how to launch the Database Explorer from BAS and use it to monitor the subscriptions, executions of tasks and procedures created during the replication task in the HDI container.