Part 4: Creating a Remote Source with SAC

Missed the third part of this mini-blog series? Read it here.

Creating a Remote Source in HANA Studio:

The last article in this mini-blog series will describe the process of combining data from tables in another tenant database in the same calculation view. For example, data from FSDM and FPSL.

One possible way to achieve this is to create a “Remote Source”. Remote sources allow you to use virtual tables which represent tables in a remote source such as a another tenant database. Best practice is to create a remote source using either the SAP<SID> user or a technical user. In our example, we used the SAC_TECH_USER. The user will require the following system privileges to create a remote source: “Create Remote Source” or “Data Admin”.

To create a remote source, execute the following SQL statement:

CREATE REMOTE SOURCE <HANA1> ADAPTER hanaodbc

    CONFIGURATION ‘Driver=libodbcHDB.so;ServerNode=<server_name>:<SID>;

    WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘user=<user_name>;password=<password>’;

The SID (Instance Number) in this case refers to the tenant database in this case. The “WITH CREDENTIAL” syntax specifies the user credentials to connect to the remote source. This user needs to have access to the tables inside the remote.

Once successfully executed, the remote source will appear under the folder “Provisioning”.

You can also create remote sources by right clicking on the “Remote Sources” folder and selecting “New Remote Source”.

If the remote source was not created with the SAP<SID> user but with another technical user, then this technical user will need to assign the objection privileges for the remote source (CREATE VIRTUAL TABLE and DROP) to the SAP<SID> user. For more information on creating remote sources click here.

The next step is to create a virtual table on the remote source created in the previous step using the technical user. Creating a virtual table requires the “CREATE VIRTUAL TABLE” object privilege. To create a virtual table, execute the following SQL statement:

CREATE VIRTUAL TABLE [<schema>.]<virtual_table_name> AT <remote_source>.<database_name>.<schema>.<table_name>;

You can specify in which schema you want to create the virtual table. For example: _SYS_BIC

CREATE VIRTUAL TABLE “_SYS_BIC”.”FSDP_TEST_DC_CONTRACT” AT “FPSL_TEST_DC”.”FSDM”.”E0AFCD5755C843ABB1AD5A25E0599BFD”.”sap.fsdm::FinancialContract”;

Virtual tables can also be created by expanding the remote source, navigating to the schema in which the table you want to create a virtual table for is located, right clicking on that table, and selecting “Add as Virtual Table”.

The virtual table will now appear in the in the “Catalog” area for your schema (user). If you require multiple tables from the remote source, you will need to create a virtual table for each one. For more information on how to create a virtual table, click here.

Now we can add the virtual table to our calculation view. In the example below, we have created a calculation view in the FPSL tenant database and added a virtual table as a data source which is based on the table “sap.fsdm::FinancialContract” from the FSDM tenant database.

Once successfully activated, the calculation view should appear in SAC as a data source when creating a HANA Live Data Connection.

Creating a Remote Source in SAP Web IDE:

In this example, we will create a remote source in the FSDM tenant database on the S4D tenant database running FPSL in SAP Web IDE for HANA.

The first step will be to create a remote source in the FSDM tenant database. Again, best practice is to use either the SAP<SID> user or a technical user with the necessary privileges. To create a remote source, execute the following SQL statement:

CREATE REMOTE SOURCE <HANA1> ADAPTER hanaodbc

    CONFIGURATION ‘Driver=libodbcHDB.so;ServerNode=<server_name>:3<SID>15;

    WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘user=<user_name>;password=<password>’;

Once successfully executed, the remote source will appear under the folder “Remote Sources” in the database explorer for the tenant database.

The next step is to grant access to use the remote source to your local HDI container. The user that created the remote source must execute the grant. To check which database user created a remote source, execute the query in the screenshot below.

To grant your local container access to the remote source, and create virtual tables on that remote source, execute the following SQL statement:

GRANT CREATE VIRTUAL TABLE, DROP ON REMOTE SOURCE <remote_source> TO “schema_name#OO”;

To find out what your schema name is inside your local HDI container, open the SAP HANA XS Advanced Cockpit found under “Tools”. Select the space on which your container has been deployed, select service instances, and click on your container.

The alternative is to open the database explorer, navigate to “Public Synonyms,” in your HDI container and search for the table “Schemas”.

Now you can create a virtual table(s) in your local container. To do this, select “Database Artifact” and choose the file type “.hdbvirtualtable”. You will need to provide the following information:

  • Virtual table name
  • Remote source name (created in the first step)
  • Database name (tenant)
  • Schema name (Schema where the table you require is located)
  • Table Name (The table for which you want to create a virtual table)

Build the .hdbvirtualtable, navigate to the database explorer and click on “tables” in your local container. Your virtual table should appear in the list.

Now this virtual table can be used in a calculation view. In the example below, we have created the same calculation view in SAP Web IDE for SAP HANA as in the SAP HANA Studio example. We join the tables (“/BA1/F2_BT_FLAT and “/BA1/HFSPD) from the S4D tenant database with the table “sap.fsdm::FinancialContract” from the FSDP tenant database. The icons highlighted in yellow indicate that the tables are virtual.

Once successfully built, the calculation view should appear in SAC as a data source when creating a model with an HANA Live Data Connection.

Part 4: Creating a Remote Source with SAC
Markiert in:                     
Daniel Corry

Daniel Corry

Daniel Corry ist Consultant im Beratungsbereich Data Management. Seine Fachgebiete sind SAP HANA, SAP FSDM und SAP Analytics Cloud.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.