Live Connection Between SAP Analytic Cloud and Cloud Foundry HANA DB

[arvind_barathwaj]

Introduction

SAP Analytic Cloud use Live Connection to connect a multitude of data sources, SAC model can be created based on the live connection and the data from source system will be read in real time.This blog explains the steps to establish live data connection in SAP Analytics Cloud (SAC) to SAP Cloud Platform, Cloud Foundry HANA Service.

Contents

• Integration Architecture.


• Installing Prerequisite Tools.


• Build and Deploy HANA Analytic Adapter in SAP Cloud Platform, Cloud Foundry.


• User Permission and Role Collection in SAP Cloud Platform.


• Identity Provider Configuration for SCP CF and SAC.


• Role & JWT identity authentication in SAP HANA Service.


• Configure Live Connection in SAP Analytic Cloud.

Prerequisite

• SAP Analytic Cloud.


• SAP HANA Service.


• SAML2 Identity Provider (To establish SSO between SAP HANA Service and SAC).


• Cloud Foundry Command line interface.


• MultiApps CF CLI Plugin.


• Oracle JDK 8.


• Apache Melvin.


• Node Package Manager & Node.js.

Integration Architecture

1. Installing Prerequisite Tools

1.1. Install Cloud Foundry Command line interface

We need Cloud foundry command line interface to build and deploy the node.js application in SAP cloud platform Cloud Foundry Space.

Download & Install Cloud Foundry CLI.

Note: Make sure to define CF CLI in environment variable.

1.2. Install Add-on Plugin in CF CLI


To deploy Multi Target Application (MTA) in Cloud Foundry, add MultiApps plugin to CF CLI command line tool.

Run the command cf install-plugin multiapps from CF CLI command line tool to install MultiApps plugin in CF CLI.

1.3. Install Oracle JDK 8

Download and install Oracle JDK8. To confirm the successful installation, execute the command java -version, the output has the Java Version.

Note: Make sure to define Java in environment variable.

1.4. Install Apache Maven

Download Apache Maven (apache-maven-x.x.x-bin.zip), extract and place the downloaded files in Program Files folder in C:\ drive.

Note: Make sure to define Apache Maven in environment system variable PATH. The same can be verified using the command mvn -version.

1.5. Install Nods.js

Download and install Nods.js.

Note: Make sure to define Nods.js in environment system variable PATH.The same can be verified using the command node --version.

Verify that Node Package Manager (NPM) is installed using command npm --version, npm gets installed along with Node.js.

Set npm for the sap registry modules with the command: npm config set @Sisn:registry https://npm.sap.com.


 

2. Build and Deploy HANA Analytic Adapter in CF

HANA Analytic Adapter (HAA) is a multi-target application deployed in SAP Cloud Platform, Cloud Foundry to connect SAP Analytic Cloud and SAP HANA Service. Find below the steps to build and Deploy HANA Analytic adapter.

2.1. Prepare & Build Multi-Target Application - HAA

2.1.1. Download HANA Analytic Adapter (HAA) Project

Download HAA Package from git repository and extract it to directory Drive:\HAA.

When the HAA project is extracted, the below files and directories exist in in it.

2.1.2 Download the Multi-Target Application (MTA) Archive Builder

Download MTA Archive Builder (mta_archive_builder.jar) file from SAP Cloud Tools and place it in the HAA Directory. Rename the file as mta.jar for convenience to execute commands.

2.1.3 Download Analytic Adapter

Download the latest version of Analytic Adapter for SAP HANA Extended Application Service Advance Model (HANA - XS Advance) from SAP Cloud Tools.

Extract the xsahaa and place it in the directory Drive:\HAA\haa-java\target. Create target directory if it does not exist in haa-java.

2.2. Edit mta.yaml file

In HAA directory, edit the mta.yaml file with required parameter to deploy HANA Analytic Adapter in SAP Cloud Platform. Before editing mta.yaml file, collect the details of below services in your environment.

• URL of SAC Analytic Cloud.


• Name of HDI container deployed in SAP Cloud Platform.


• CF subaccount Organization Name.

The below listed parameters has to be edited in the mta.yaml code template with details of respective services.

• <HDI_CONTAINER-NAME> - Replace it with name of hdi container. Eg. hdi_smart_data.


• <URL of SAP ANALYTIC CLOUD CLOUD> - Replace it with URL of SAC. Eg. sac-global-services.eu10.hcs.cloud.sap.


• TENANT_HOST_PATTERN - Replace the <space> with the name of CF space in which the HAA application is deployed. Eg. '^(.*)-<space-name>-haa.cfapps.(.*).hana.ondemand.com'.

Note:

If organization name and subdomain name of Cloud Foundry subaccount are different then the value of the parameter TENANT_HOST_PATTERN must be defined in below format.

 Eg. (.*)-<org-name>-<space-name>-haa.cfapps.(.*).hana.ondemand.com.

In the above Eg., Replace spaces and underscores in the org name & cf space name with dash (-), all character must be in lowercase. For example if org name is "SAP_Cloud_Platform cf", it must be written as "sap-cloud-platform-cf".

Below is the code template for deploying Shared HANA Analytic Adapter.

ID: haa

_schema-version: '2.0'

version: 0.0.1



modules:



 - name: haa-java

   type: java

   path: haa-java

   parameters:

     memory: 1024M

     buildpack: sap_java_buildpack

   properties:

     TARGET_RUNTIME: tome

     SAP_JWT_TRUST_ACL: '[{"clientid":"sb-xsahaa-be", "identityzone": "*"}]'

     JBP_CONFIG_RESOURCE_CONFIGURATION: "['tomee/webapps/ROOT/WEB-INF/resources.xml': {'xsahaa-hdi-container':'<HDI_CONTAINER-NAME>'}]"

     USE_NAMED_USER: true

     SECONDARY_ROUTING_PERCENT: 0

     

   provides:

    - name: haa-java

      properties:

        url: ${default-url}

   requires:

    - name: haa-uaa

    - name: <HDI_CONTAINER-NAME>



 - name: haa

   type: nodejs

   path: haa-entry

   parameters:

     memory: 512M

     buildpack: nodejs_buildpack

   requires:

    - name: haa-uaa

    - name: haa-java

      group: destinations

      properties:

        name: haa-java

        url: ~{url}

        forwardAuthToken: true

        timeout: 600000

   properties: 

     CORS: '[{"uriPattern": "^/sap/bc/ina/(.*)$", "allowedOrigin": [{"host":"<URL of SAP ANALYTIC CLOUD>", "protocol":"https"}], "allowedMethods": ["GET", "POST", "OPTIONS"], "allowedHeaders": ["Origin", "Accept", "X-Requested-With", "Content-Type", "Access-Control-Request-Method", "Access-Control-Request-Headers", "Authorization", "X-Sap-Cid", "X-Csrf-Token"], "exposeHeaders": ["Accept", "Authorization", "X-Requested-With", "X-Sap-Cid", "Access-Control-Allow-Origin", "Access-Control-Allow-Credentials", "X-Csrf-Token", "Content-Type"]}]'

     COOKIES: '{"SameSite": "None"}'

     TENANT_HOST_PATTERN: '^(.*)-<space>-haa.cfapps.(.*).hana.ondemand.com'

     INCOMING_CONNECTION_TIMEOUT: 600000

     



resources:

 - name: haa-uaa

   type: com.sap.xs.uaa

   parameters:

     path: ./xs-security.json

     

 - name: <HDI_CONTAINER-NAME>

   type: org.cloudfoundry.existing-service

The above code template is used to deploy HANA Analytic Adapter in Shared Mode.

To deploy HANA Analytic Adapter in Dedicated mode, remove the parameters SAP_JWT_TRUST_ACL & TENANT_HOST_PATTERN from the above mta.yaml code template.

SAP_JWT_TRUST_ACL: '[{"clientid":"sb-xsahaa-be", "identityzone": "*"}]'



TENANT_HOST_PATTERN: '^(.*)-<space>-haa.cfapps.(.*).hana.ondemand.com'​

2.2.1. Edit xs-security.json file

The xs-security.json file in <Drive>:\HAA directory define the roles that gets created in SAP Cloud Platform when MTA application is deployed.

Edit xs-security.json, define the value for parameter xsappname: haa-role.

For Dedicated HANA Analytic Adapter, the value of parameter tenant-mode = dedicated.

For Shared HANA Analytic Adapter, the value of parameter  tenant-mode = shared.

2.2.2. Build HANA Analytic Adapter

Open command prompt and navigate to the directory HAA were mta.yaml file is configured.

Execute the command java -jar mta.jar --build-target=CF build to build the mtar file for HANA Analytic Adapter. The file HAA.mtar gets created in the directory.

2.3. Deploy Multi Target Application

2.3.1. Login SAP Cloud Platform Cloud Foundry Sub-account in Windows Command Prompt


Open the command prompt and login to SAP CF Sub-account, using command CF login. Provide api of the CF subaccount. You can find the api details in the overview tab of the CF subaccount.

Enter Credentials and Choose the subaccount in which HAA adapter must be deployed.

2.3.2. Deploy the HAA.mtar file

Deploy the HAA.mtar file built in step 2.2.2 in the CF subaccount using the command cf deploy HAA.mtar.

Once the HANA Analytic Adapter is successfully deployed, you can find the application running in application tab of cf-space where it is deployed.

Once haa application is successfully deployed, the same can be confirmed by launching the URL of haa application in browser it will open up as in below screen shot.

You can find the xsuaa service (haa-uaa) created, haa-java bound to the hdi container & xsuaa service in the service instance tab of the space.

 

3. User Permission and Role Collection in SAP Cloud Platform Cloud Foundry

3.1. Define Role & Role Collection in SAP Cloud Platform

Open the subaccount where the HAA application is deployed. Navigate to Security -> Role, the role haa-role defined in xs-security.json gets created here as part of deployment.

 Navigate to Role Collection menu, Click New Role Collection and Enter a role collection name.

Now select the created role collection, in next screen add role to role collection haa_role!23123.

3.2. Define HDI_User Permission

Provide select access on _sys_bi schema to hdi user, run the command grant select on schema _sys_bi to <hdi_user>; in SQL console or HANA Studio.

Find the hdi user details by navigating to Service Instance in space of subaccount, click on hdi_container, where you can find hdi_user details.

3.3. Trust Configuration & Role Assignment in SAP Cloud Platform Cloud Foundry Sub-account

Navigate to Security -> Trust Setting in CF Sub-account, Deactivate the default SAP Id Service.

Click on Custom IdP, Navigate to Role Collection Assignment menu, Enter the email address and click Show Assignment button and add user to the Custom IdP. Once the user is added to custom IdP, click on Assign Role Collection button and add the role SACtoHANA Role to CF user id.

 

3.4. Trust between XSUAA and HANA Database Container

The XSUAA internal identity propagation is possible when there is a trust relation from XSUAA to HANA database where the schemas of hdi containers exist.

Follow the steps in below note, section b) No trust has been established between XSUAA and the Hana Database -> b) Sap Cloud Platform (SAPCP) in Note 2470084 - XSUAA metadata for XS_APPLICATION USER trust creation.

 

4. Identity Provider Configuration for SCP CF and SAC

To setup SSO between SAC and SAP HANA Service, both the cloud service should have same identity provider. Here SAP Cloud Identity Authentication Service is used as common identity provider for both SAC and SAP CF.

Note: SSO between SAC and SAP CF can be established only with a common identity provider(IdP) for both the services. If distinct IdP is used for both services, SSO cannot be configured and each time when SAC dashboard is accessed, the source system credentials (SAP CF Cloud Platform credentials) will be requested.

4.1. Setup Custom IdP for SAP Cloud Platform – Cloud Foundry

Login to SAP Cloud Identity Authentication Service as administrator, navigate to Application & Resources -> Tenant Setting -> SAML 2.0 Configuration.

Login to SAP Cloud Platform Cloud Foundry subaccount, with administrator access, Navigate to Security -> Trust Configuration.

Click New Trust Configuration button and add the metadata downloaded from SAP Cloud Authentication service.

Click Browse button to add metadata downloaded from SAP Cloud IdP and Save the configuration.

Once custom IdP is added, disable default SAP ID service available for user logon and so user login to application deployed SAP Cloud Platform, Cloud Foundry subaccount only using SAP Custom IdP.

Now Click SAML Metadata button to download the metadata of SAP Cloud Platform, Cloud Foundry subaccount. The metadata can be download from the below URL as well.

URL: https://<subaccount>.authentication.<region>.hana.ondemand.com/saml/metadata

Again, Login to SAP Cloud IdP as administrator, navigate to Application & Resources -> Application ->  Click +Add button to add an application.

Click the application navigate to SAML 2.0 Configuration and upload the metadata downloaded from SAP Cloud Platform, Cloud Foundry Sub-account.

Define Subject Name Identifier as Email ID & save the configuration.

4.2 Setup Custom IdP for SAP Analytic Cloud

Download the metadata of SAP Cloud Identity authentication service as explained in the step 4.1.

Login to SAP Analytic Cloud as Administrator, navigate to System -> Administration -> Security Tab, Switch Authentication Method to SAML Single Sign-On (Steps), upload the metadata downloaded from SAP Cloud Identity Authentication Service.

Define Email id as user attribute in SAC and Save the configuration.

Download the metadata from SAC and create a new application in SAP Cloud IdP, upload metadata in SAP Cloud Identity Authentication Service.

Define e-mail address as unique identifier attribute in SAP IdP and save the configuration.

 

5. Role & JWT identity authentication in SAP HANA Service

Login to HANA Cockpit with HANA DB user, make sure the user has User Admin and Role Admin system privilege. Click User Management.

Create a New HANA User, scroll down to define JWT identity authentication. Check the JWT box and click on “Add JWT Identity”, Map the JWT Provider to match the External SAML IdP Email ID attribute and save the configuration.

Click Assign Roles and Add the Access Role and External Privilege role created as part of HDI container deployment and save the configuration. The role name will have naming convention <hdi_schema_name>::access_role.

Click on Assign Privilege, Select Object Privilege tab, add object EXECUTE_MDS with Execute Privilege and _SYS_BI with Select Privilege and save the configuration.

5.1. Test SSO Connection HAA in CF Space and SAP HANA Service

To test SSO configured between HANA Analytic Adapter and SAP HANA Service, add the suffix /sap/bc/ina/service/v2/HeartBeat path to haa application URL deployed in CF Space.

URL : https://<cf-subaccount-org-name>-<space>-haa.cfapps.eu10.hana.ondemand.com

On accessing the haa URL, connection results similar to below screen shot which has User Created in SAP HANA Service, then the SSO between SAP HANA Service and HAA application connection is successful.

On accessing the URL if we get FORBIDDEN error, then the SAP CF Sub-account Role connection is not mapped to the user mapped to Custom IdP in SAP CF subaccount Trust Configuration.

On accessing the URL if we get “authentication failed” error, means the issue is in accessing SAP HANA Service. In this case check the SAP HANA Service JWT identity authentication settings of HANA Service user, role and privilege assigned to SAP HANA Service user.

 

6. Configure Live Connection in SAP Analytic Cloud

Login to SAP Analytic Cloud, Navigate to Connection. Click + to create new connection and Choose Live Connection to SAP HANA.

A pop-up for new live connection appears, provide the following details to setup live connection between SAC and SAP HANA Service, click Ok to Test and Save the configurations.

Name               : <Name of Live Connection> Eg. CFHANA

Description        : Description to Live connection

Connection Type    : Direct

Host               : <cf-subaccount-org-name>-<space>-haa.cfapps.eu10.hana.ondemand.com

Port               : 443

Authentication Type: SAML Single Sign On

URL of host can be obtained from the CF subaccount space where HANA Analytic Adapter haa is deployed.

 

Conclusion

In this blog post I have explained in detail on creating Live Connection between SAC and SAP HANA Service using HANA Analytic Adapter. I have used SAP Cloud Identity Authentication service to establish SSO between SAC and HANA Analytic Adapter deployed in CF Sub-account. You Can also use other identity providers but make sure the identity provider supports SAML protocol.

[former_member685204]

Excellent Post Arvind 🙂

[clebermoraes]

Great information Arvind!
Quick question, the same applies for the new SAP CP HANA Cloud?

[arvind_barathwaj]

Thanks Cleber. The same steps applies to SAP CP HANA cloud, as SAP CP HANA Cloud is created in Cloud Foundry.

[arvind_barathwaj]

Thank you Barath.

[former_member626137]

Good information bro 🙂

[former_member692559]

glad you have shared such a great information

[former_member95956]

Great and very useful information.

One question, should all users must completed above steps, then they can see the BI report? For example if customers have 10 licenses includes 2 BI admin and 8 BI content view, due to it is live connection, whether all users must completed above configs or only BI admin should do?

[arvind_barathwaj]

Hi Shi-Jie,

The above mentioned configuration steps are one time activity, in general done by administrator. But users must have corresponding privileges in platform and DB to view the report.

[srikanthin]

Hi,

We have configured SAP identity authentication service between the SAP cloud foundry and SAP Analytics cloud.

When we are try to access this link  “https://fxxxxxxxxxxxxxx10.hana.ondemand.com/” it is redirecting to SAP identity authentication service login page, but when we are entering the url with SAP cloud HANA service path  “https://fxxxxxxxxxxxxxxxxxxx10.hana.ondemand.com/sap/bc/ina/service/v2/GetResponse” it is not redirecting to SAP identity authentication service login page. can you please help me out with this.

Thanks & Regards,

Srikanth

[arvind_barathwaj]

Hi Srikanth,

If the URL https://subaccount.authentication.XX10.hana.ondemand.com/ is redirecting to SAP Identity Authentication service then obviously any suffix with this url either /sap/bc/ina/service/v2/HeartBeat or /sap/bc/ina/service/v2/GetRespose will also get redirected to SAP Identity authentication service.

Because when you hit URL of the cloud foundry subaccount or URL of any application deployed in cloud foundry subaccount, it will first redirect to URL https://subaccount.authentication.XX10.hana.ondemand.com/login, this URL only in turn will hit the Identity provider configured to the subaccount.

[alexander_bruch]

Can you elaborate more on: "Map the JWT Provider to match the External SAML IdP Email ID" Does this mean we need to manually map each externally provided Email ID of an external user to the one or many HANA technical users?

[Ivo101]

Hi Arvind

we are currently in the same situation, but we noticed that users that have an account on the SAP Cloud Platform tenant but no account in the HANA database are able to connect to the SAP HANA database and they get data. Do you have any experience in this situation?

We are also preparing an OSS call for this.