How to use objects contained in a schema outside of your Web IDE Full-Stack project in SAP HANA Service

[jan_zwickel]

If you are working in Web IDE full-stack in a SAP HANA Service environment and you would like to consume objects that are not part of your project, you can achieve this with the following three steps:

1. Connect with a service to the schema


2. Assign the necessary authorizations to the technical users of your project


3. Create synonyms pointing to the objects

 

All of these steps are described in an on-premise context for example in documetation or blog1, blog2. For a recent example that also includes loading data and accessing external schemas in the context of SAP HANA Service see blog3

To make the start of modeling a bit easier the blog shows the steps of consuming objects outside of your project in SAP HANA Service. The example assumes that table "SalesOrder" in schema "BICERTDATA" should be consumed in a Calculation View and that you create a user provided service "UPS" to this end.

1. Connect with a service to the schema

In the SPACE of your instance, select the option "User Provided Service" under Services



 

If a user provided service that fulfills your requirements does not already exist you can create it with button "New Instance".

The following definition would create a user provided service called "UPS" that is based on a database user UPSUSER with password "secret1234"

{

"desc": "example",

"user": "UPSUSER",

"password": " secret1234",

"tags": [

"hana"

]

}

 

Make sure that the database user UPSUSER exists, has the authorization to GRANT SELECT WITH GRANT OPTION on table "SalesOrder", and uses this password. The GRANT SELECT WITH GRANT OPTION does not need to be assigned directly to the UPSUSER as long as the UPSUSER can grant a role with the respective privilege.

Next, you have to add the service to your project by including it in your .yaml file. To do so right-click on the .yaml file of your project and select "Open Code Editor". Below is a simple yaml file. The changes due to the added user provided service named "UPS" are marked in bold:

ID: Demo
_schema-version: '2.1'
version: 0.0.1

modules:
- name: db
type: hdb
path: db
requires:
- name: hdi_db
  properties:
        TARGET_CONTAINER: ~{hdi-container-name}
       
    - name: UPS
      group: SERVICE_REPLACEMENTS

      properties:
        key: ServiceName_1
        service: ~{the-service-name}

resources:
- name: hdi_db
properties:
hdi-container-name: ${service-name}
type: com.sap.xs.hdi-container

 - name: UPS
   parameters:
      service-name: UPS
   properties:
      the-service-name: ${service-name}
   type: org.cloudfoundry.existing-service

 

With newer Web IDE versions there is also a graphical support for creating and adding the user provided service by right-clicking on the database module and selecting "HANA Service Connection"



 

In the dialog, you can either reuse an already existing user provided service and only add the reference to the service into the .yaml file, or create a new user provided service that is then also automatically added to the .yaml file:



 

2. Assign the necessary authorizations to the technical users

Next you will need to create and build a hdbgrants file. This file should grant SELECT WITH GRANT OPTION on objects that are to be used in e.g., Calculation Views to the user "object_owner". Grant SELECT without the GRANT OPTION to the application user. The application user is used during interaction of the modeling tooling with the database, like for example during data preview.

As best practice, these privileges should be granted via roles.

{
"UPS": {
"object_owner": {
"roles": [
"roleForObjectOwner"
]
},
"application_user": {
"roles": [
"roleForApplicationUser"
]
}
}
}

The roles "roleForApplicationUser" and "roleForObjectOwner" can be creates as SQL roles. Make sure that the database user behind the user provided service (UPSUSER) has been granted the authorization to grant these roles and has at least privilege SELECT METADATA on the underlying objects that should be used in Calculation Views. In addition UPSUSER should have system generated role "PUBLIC" (see also question "In a nutshell: What authorizations do I need to start modelling?" here)

 

3. Create synonyms pointing to the objects

Finally, you need to create and build synonyms that point to the respective objects. The following description would create synonym "SalesOrder" pointing to table "SalesOrder" in schema "BICERTDATA":

{

"SalesOrder": {

"target": {

"object": " SalesOrder",

"schema": " BICERTDATA"

}

}

}

 

You might want to use the .hdbsynonymconfig approach that is also detailed at the help portal to achieve more flexibility when addressing the source schema, e.g., using no fixed schema but fill the schema by the user provided service. Other options to use synonyms flexibly can be found in e.g., this blog.This flexibility can come handy in a transport context.

With these steps you should now be able to use the object in e.g., your Calculation View. Simply select the object with the created synonym in the "Add Data Source" step of your Calculation View and press "Finish"

 



 

Alternatively, with newer versions of Web IDE you can create the synonym via the "Add Data Source" dialog (button "Create Synonym") and thus do not have to define the synonyms manually beforehand.

If you do not want to create the synonyms when adding data sources but would like to use a graphical editor, you can also create synonyms for all objects in a schema by double clicking on a .hdbsynonym file and selecting "Mass Import of Synonyms":