Skip to Content
Technical Articles

A tip of defining Synonyms at HDI using SAP Business Application Studio

Purpose

I found accessing tables in HDI container using SAP BAS is a bit of tricky and have been juggling of how to use the form editor for synonym. Here is a tip for someone who might have been in the same dilemma as I have, or for who don’t want to spend time on investigating and heading around in trying to find the reason as I did.

 

Environment

A  table (“city”) & the data have been generated in an external data source – Data Lake whose connection is already established on HANA Data Explore (Please refer to other blog if you need to link external data sources to HANA ). I created a schema ‘booking’ in my HANA cloud.

Note : You need to login HANA Data Explore as an admin user with the privilege of DB admin to manage DB objects.  The way of connecting external resources on SAP Web IDE might be a bit different and eventually SAP Web IDE would fade out and to be replaced with BAS.

A table ‘city’ in remote data source ‘BLLAKE’ created as

Then mounted the table ‘city’ onto my HANA DB BLHANA as a virtual table under the schema ‘booking’

I also created two tables called ‘AIRPLANE’ & ‘FLINFO’ as below in my HANA DB directly, under the same schema ‘booking’

You should be able to notice the virtual table ‘city’ referring to the external Data Lake has a green triangle icon on the bottom.

I imported data into them by using import function on HANA Database Explorer… which are like

Okay ….. now go to BTP Cockpit

In my BTP cockpit, I have 2 spaces defined under my subaccount, one for development, the other for test.

note: Database links for HANA & Data Lake are setup in space ‘dev’ but ‘test’ shares the link which makes developers able to access DBs from space ‘test’. intended to separated this project from my other projects

 

 

Defining Access and role

This is very important part in order to access classic schemas from the HDI container in BAS. go to SAP Database Explorer again

Create an end user ‘BKUSER’

CREATE USER BKUSR PASSWORD “Password1” NO FORCE_FIRST_PASSWORD_CHANGE ;

Then need to create a role which you can do it in SAP DE, alternatively, you can also make the role in DB admin cockpit. Assign the privileges which required to manage and to access data in the tables under schema ‘BOOKING’

CREATE ROLE BOOKING_CROLE;
grant SELECT, UPDATE, INSERT, DELETE, EXECUTE, SELECT METADATA ON SCHEMA “BOOKING” TO BOOKING_CROLE with grant option;

Now grant the role to the end user with admin option,

grant BOOKING_CROLE to BKUSR with admin option;

 

Now technical users need to get the role granted as well.

Go to your BTP cockpit and define a User-Provided HDI instance. It is “booking_external_access” on the space “test” in my case.

Of course, you need to add a piece of Jason code to identify the end user (actually, application user) ‘BKUSER’. The password is ‘Password1’ as many guys used it for test purpose. This password was set for user BKUSR when creating the user.

Also don’t forget to add some snip in your yaml file in your project folder, I created a module ‘fl’ under project ‘booking’ as below.

Goto BAS and create a project and application, add some code snips (of “booking_external_access” in my case ) in your yaml file to get your project to access your User-Provided HDI instance.

Create a .hdbgrants file as below. “booking crole” is the role that I created and assigned it to the end user “BKUSR” at SAP Database explorer. So object owners, that is, technical users will have the same privilege for the schema “BOOKING” as “BKUSR”

{
  “booking_external_access”: {
    “object_owner” : {
      “roles” : [“BOOKING_CROLE” ]
    },
    “application_user” : {
      “roles” : [“BOOKING_CROLE” ]
    }
  }
}

Deploy it

 

 

Finally! Let’s Define Synonyms 

Here is the tricky part, SAP BAS provides the auto generated form editor for most of DB artifacts in according to the plugins registered in the config file. right? I figured that the form editor for some artifacts is not working as it should be. Unfortunately, hdbsynonym is one of them.

As you can see there is a table “TICKETINFO” under the schema “BOOKING” exists in HANA

However, the searching function on the form editor couldn’t find the table “until you define a synonym for the table in your HDI container” like below 

 

 

So it seems we need to stick to the old-fashion code editor in creating synonyms for now even after you move from WEB IDE to BAS.

The reason why I explain it with the long details from about creating tables, assigning privileges, and defining synonyms is because I had been confused and misled by the thought that I did something wrong. such as granting my privilege, role assignment, sort of things .
Please don’t spend too much time in creating HANA artifacts with the form editor at BAS if it does not work as you expected. Just use code editor. it might work like charm

References

Here is some links you can refer to

https://saphanajourney.com/hana-cloud/learning-article/creating-remote-connections-and-virtual-tables/

 

Be the first to leave a comment
You must be Logged on to comment or reply to a post.