A tip of defining Synonyms at HDI using SAP Business Application Studio
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.
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
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.
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.
Here is some links you can refer to