From zero to Analytics pt3: Setting up a user for the HANA Analytics Adapter
The HANA Analytics adapter exposes an endpoint to analytics tools such as SAP Analytics Cloud, Analysis for Office and other reporting clients. The adapter is an application built for XS Advanced. This means the endpoint for connections does not rely on the deprecated XS Classic engine, unlike its predecessor.
Here are the instructions to create a sample user that will be connecting from the external clients.
Configuring the user for the HANA Analytics adapter
You can download the latest HANA Analytics Adapter for on-premise here: https://tools.hana.ondemand.com/#hanatools
Note that I am using version 1.5.0 for this.
The adapter includes two Java applications. One is the approuter, which will receive the HTTP(s) requests and the other is the backend module implementing the InA queries.
A quick and dirty (yet colorful) representation of components:
This diagram is not representing the dependency on the EPMMDS plugin, which needs to be installed in the database (it’s already there in HANA express)
I will create a sample user for this. This user will be accessing the database directly and will need a JWT token.
For more information about user authentication mechanisms check the SAP HANA Security Guide
This assumes the system database is the identity provider.
Connect to the databases
This is the kind of task that is usually done by an administrator. Like many of you out there, I am the administrator and developer of my HANA express instance.
I will do this from the SQL console in Web IDE. You can find the instructions to do this using the SAP HANA Cockpit here
You will need to connect both to the system database AND the tenant.
I’ll start with the system database:
And now the tenant database:
You will need a user with administration permissions to create other users (USER ADMIN).
Open two SQL consoles, one for each database:
Check the JWT providers in both databases
SELECT JWT_PROVIDER_NAME FROM JWT_PROVIDERS;
The result under JWT_PROVIDER_NAME should be the same for both the system and the tenant database. It should NOT be blank.
If it’s blank, try command renew-db-trust from the operating system of the instance ( XSA renew-db-trust -u XSA_ADMIN ). Or check this note. Thankfully, this is not an emotional entity and it’s relatively easy to re-establish trust.
Copy the value:
TENANT DATABASE – Create a user
In the HXE tenant database (or whatever tenant is mapped to the space with your calculation views), execute the following statements (replace the sample user name and password if you want).
Replace the placeholder for name of your JWT provider with the value from the previous step
CREATE USER HAAUSR PASSWORD HanaRocks1 NO FORCE_FIRST_PASSWORD_CHANGE; grant EXECUTE ON SYS.EXECUTE_MDS_DEV to HAAUSR; ALTER USER HAAUSR ENABLE JWT; ALTER USER HAAUSR ADD IDENTITY 'HAAUSR' FOR JWT PROVIDER <name of your JWT provider>;
SYSTEM DATABASE – Create the user and grant permissions
The user also needs to exist in the system database
CREATE USER HAAUSR PASSWORD HanaRocks1 NO FORCE_FIRST_PASSWORD_CHANGE;
Grant the external user permissions to your HDI container
You can guess from the previous statements that this user will be able to access the database but will not be able to read the calculation views in your HDI container. You will need to grant this external user with access to the HDI container where your calculations view live.
Open the HDI container in the database explorer:
Right click on it and choose Open as admin
Get the name of the schema:
And run the following statements (notice the #DI added to the end of the schema):
set schema "ANALYTICS_1#DI"; CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'SELECT', '', 'HAAUSR' );
Now add the following statement and execute it:
CALL ANALYTICS_1#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
You can test the access and user works by adding a new connection to the HXE database with user HAAUSR instead of SYSTEM:
This is the most complicated piece of this entire process and you’ve made it through!
For the sake of length, the following blog post shows how to install the HANA Analytics Adapter: https://blogs.sap.com/2019/01/22/from-zero-to-analytics-pt4-installing-the-hana-analytics-adapter/