Skip to Content
Technical Articles
Author's profile photo Apoorva Nagaraju

Real-time Replication task using SAP Business Application Studio

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.

  1. Enter credentials and login to the application. A new tab opens and the welcome page for SAP Business Application Studio loads.
  2. 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.
  1. 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
  1. In the Add Database Connectionwizard 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
  1. 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
  2. Name it as grants.hdbgrants and use the below code and Save
  1. 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
  1. Open REPTASK1, click on connect button and choose the remote source
  1. 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.
  1. 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.
  2. Select one of the available functions from the categories in the Functions pane.
  3. Click or type any operators to complete the expression.
  4. 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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mohan Krishna Krishna Perichetla
      Mohan Krishna Krishna Perichetla

      Very clear documentation, Thanks for sharing

      Author's profile photo Sunny Makker
      Sunny Makker

      Helle everyone,

      currently I face an issue which i can't solve myself. Maybe I will find a solution by posting my challenge.

      Scenario:

      • DB2 is connected via DB2LogReader Adapter to XSA using Smart Data Integration
      • Created a replication task which loads one single table
      • Load mode is "intial + realtime" and preserve all

      Note: The scenario described above worked pretty fine for about two hours. We captured every single data change coming from the DB2 source system. At one point we truncated the table we had in use for testing directly within DB2. Since then I am no longer able to get a replication task mode "initial + realtime" running although "initial only" works as fine as before.

      So the moment I execute the replication task in "initial + realtime" i receive the following error message:

      Could not execute 'CALL "DELTA_XXX"."DataWareHouse.Database.Replication ...'
      Error: (dberror) [686]: start task error: "DELTA_XXX"."DataWareHouse.Database.Replication Tasks::XXX_DELTA.START_REPLICATION": line 27 col 6 (at pos 1110): [140038] Error executing SQL command in task.;Error executing ALTER_REMOTE_SUBSCRIPTION_QUEUE with command: 'ALTER REMOTE SUBSCRIPTION "DELTA_XXX"."SUB_XXX" QUEUE'. ,exception 71000129: SQLException
      exception 71000256: QUEUE: SUB_XXX: Failed to add subscription for remote subscription SUB_XXX[id = 16579466] in remote source XXX_LogReader[id = 14918173]. Error: exception 151050: CDC add subscription failed: RS[XXX_LogReader]: Failed to add the first subscription. Error: Failed to subscribe table ["SCHEMA"."XXX"]. Error: Initialization for replication of database <XXX> has not been done.

      Maybe you guys have a working solution for me. Thanks in advance.

      Author's profile photo Apoorva Nagaraju
      Apoorva Nagaraju
      Blog Post Author

      Hi Sunny,

      I am not aware of the exact solution for the issue. You can try following the link shared below which has DB2 configurations for replication. https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS05/en-US/b111659b4f9e4ec2bf0dceae9991502e.html

      Hope this helps!

      Regards,

      Apoorva

      Author's profile photo Yufeng Zhou
      Yufeng Zhou

      Nice article in detail. I also read other documents and Youtube videos and noticed most of them are using the Web IDE which is different compare with the Business Application Studio I am using. The steps you wrote here is very helpful.

       

      Author's profile photo The-Viet Lai
      The-Viet Lai

      Hello,

      very helpfull. But I still get an error:

      Error: com.sap.hana.di.virtualtable: ""E01"."<NULL>"."SAPSR3"."ZBTP_TEST"": not authorized to access the remote source [8207016]

      I several times checked my user.

      I am thankfull for every hint resolving the problem.

      The Viet