Synonyms in HANA XS Advanced, Introduction

Why Synonyms?

A complex HANA data warehouse might use several DB schemas in which tables, views and other
DB objects reside. E.g., there might be a replicated ERP schema managed by SLT, a Netweaver/BW schema managed by the Netweaver Stack, and a “native” HANA schema, all residing in the same HANA instance and all of them consumed by the same data warehouse application.

On  database level, tables and other DB objects from different schemas can be accessed just by providing the corresponding object privileges to a user. Synonyms can be used for convenience or to improve design, but are not needed.

In XS Advanced (XSA) based HANA data warehouses and applications, development is schema-less. A developer can only access the “local schema” that is generated for the application. Access to objects in other schemas has to be done via private/local synonyms or projection views created in this local schema.

In this series of three blogpost I will introduce the basic concepts, show how synonyms can be used to access objects in a remote schema, and explain the more complex concepts like configuration and service replacement. Even though I focus on HANA data warehouses, this document also applies to the usage of synonyms in XSA application development in general. I will not cover projection views explicitly, but include them in one of the example repos.

Use Case

I consider mainly the following use cases:

• Accessing tables owned by a classical schema from a XSA based schema (HDI container), e.g. accessing an existing ERP Schema from XSA
• Accessing tables owned by one XSA based schema from another XSA based schema, e.g. one schema containing data, a different XSA based DW application accessing those data
• Transport/deployment  to another environment

The following picture shows the principle. Changing the schema names is optional.

HDI Containers

In XSA DB objects reside in an HDI container, which is a generated schema. Development has to be done in a schema-less way. This isolates HDI containers from each other completely and makes it easier to deploy multiple containers into the same system, have several developers work independently from each other etc.. Using synonyms is therefore the designated method to access objects in other schemas.

In a pure SAP BW environment without any native HANA development you might not need this level of isolation. But as soon as native HANA development with multiple development teams becomes part of the use case, the isolation of HDI containers is an extremely powerful security mechanism, superior to the classic repository in that aspect.

Before continuing, we need to understand the object owner and user concept of XSA. Several pre-defined users are generated for each HDI container.

• A schema owner (name of the HDI container/schema)
• An object owner (creates and owns all the objects)
• An application user (also called runtime user, HANA user that runs XSA Applications within the HDI container)
• Some other technical users which we are not interested in here
• Other external user, e.g. from BI Tools

The following picture tries to illustrate different HDI containers/schemas and users involved:

Prerequisites for Examples

To execute the steps in the coding examples, the following prerequisites apply:

• You are familiar with the basic concepts of XSA, Web IDE and SQL
• You have access to the XS advanced run-time environment
• You have access to SAP Web IDE for SAP HANA
• You have access to the XS command-line interface client
• You have access to a “classical” database schema

Some examples access tables in a classical DB schema. A repository with the description how to generate the schema can be found at https://github.com/CGilde/syn-prov-hdi

Some examples access tables in an HDI container. A repository with the description how to generate the schema can be found at https://github.com/CGilde/syn-prov-hdi

The complete coding examples can be found in public github repos at https://github.com/CGilde .

All examples were tested on HANA 2.0, but most features are available already in 1.0 SPS12.

Simple example

I will finish with the most trivial example I can think of. Probably the most often used public synonym in HANA is DUMMY. Since DUMMY is a public synonym pointing to table SYS.DUMMY, for usage in XSA a private synonym pointing to table SYS.DUMMY has to be defined first.

Instead of issuing a “CREATE SYNONYM” statement, an .hdbsynonym file is included in the db folder of a project, either by using the graphical editor:

or by including a file:

{

  "DUMMY": {

    "target": {

      "object": "DUMMY",

      "schema": "SYS"

    }

  }

}

To keep things simple, no namespace is used (.hdinamespace file with empty name and option “subfolder” : “ignore”).

To test the synonym, a short function is created by including a file TEST_FUNC.hdbfunction in the db folder of the project. This function uses the newly created synonym DUMMY and can be called from the HANA runtime tools (hrtt).

FUNCTION "TEST_FUNC" ( )

RETURNS table (result NVARCHAR(100)) 

LANGUAGE SQLSCRIPT 

SQL SECURITY INVOKER AS 

BEGIN 

  return select 'CURRENT_USER: ' || CURRENT_USER result from DUMMY;

END;

Executing this function in hrtt/Database explorer will give the application user of the generated HDI container as result (see above for details on the different users involved in HDI).

The repo for this example can be found at https://github.com/CGilde/syn-dummy.