Connecting the SAP HANA Service on Cloud Foundry to SAP Analytics Cloud – The lazy approach, pt3
In the first and second blog posts, you created a user with an external identity the JWT provider and permissions to execute procedures in EPMMDS. You have configured trust between your SAP HANA service and the UAA service in Cloud Foundry. You also have some calculation views in an HDI container. There are roles and privileges protecting access to those views and tables.
You want this database user to have access to the HDI container, either by granting a role in the HDI container or schema privilege.
Grant a privilege or role in the HDI container to the database user
From Web IDE, navigate to the HDI container:
Right-click on it to open the SQL console as Admin:
Get the name of your schema, by right-clicking on a table or view and selecting “Generate SELECT statement”
If you have created a role (for example, using an .hdbrole artifact in Web IDE), here’s the snippet. Append “#DI” to the schema name you got from the previous select statement and replace “DB#DI”. Also replace the user.
set schema "DB#DI"; SELECT * FROM M_ROLES;CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_ROLES; INSERT INTO #PRIVILEGES (ROLE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME) values ('reporter','','YOUR_USER'); CALL "DB#DI".GRANT_CONTAINER_SCHEMA_ROLES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);DROP TABLE #PRIVILEGES;
If you do not have a role, below to grant SELECT access for example. Append “#DI” to the schema name you got from the previous select statement and replace “DB#DI”. Also replace the user.
set schema "DB#DI"; CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; INSERT INTO #PRIVILEGES (PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME) values ('SELECT','','YOUR_USER'); CALL "DB#DI".GRANT_CONTAINER_SCHEMA_PRIVILEGES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); DROP TABLE #PRIVILEGES;
How do I know this? Because it’s in the HANA Deployment Infrastructure Reference.
Connect from SAP Analytics Cloud
Finally some fun! Log in to your SAP Analytics Cloud instance and create a new connection
The choice is pretty obvious, but I’ll add a circle in case someone is distracted:
The host is the endpoint of xsahaa-entry
A window will pop-up, asking for the credentials for SAP Cloud Platform.
You can now create a model:
And this, my friends, is a happy screen:
The more the steps, the more chances of something going wrong. Some things I have found (and so the other people testing this — believe it or not, you are not the first to implement this)
- Stops working after a while with an “Authentication Failed” error: This has been reported and I found that restarting the module solves this.
- You get an authentication error when connecting: Press F12 to find out what the problem is. It could either be the CORS configuration or SYS.EXECUTE_MDS_DEV missing.
- You can create the connection but cannot see the models: Review the roles above (no, you don’t need to set the _SYS_BIC select access or the ina role).
Remember, you can trace authorization with:
alter system alter configuration ('indexserver.ini','SYSTEM') SET ('trace','authorization')='info' with reconfigure
and Unset it with:
alter system alter configuration ('indexserver.ini','SYSTEM') UNSET ('trace','authorization') with reconfigure