Hero Backgroud Elements 2
Reading Time: 2 Min

Creating a Live Connection Between SAP HANA Cloud and SAP Analytics Cloud

  • In this tutorial, you will see how to set up a live connection between your SAP HANA Cloud database and SAP Analytics Cloud. This connection gives SAP Analytics Cloud users access to data within a specific HDI container inside your SAP HANA Cloud database. This HDI container must have a calculation view inside, which determines which data is made available to SAP Analytics Cloud.

    This tutorial uses a single sign-on approach to authenticate users between SAP HANA Cloud and SAP Analytics Cloud.

    Pre-requisites:

    • SAP HANA database admin privileges (DBADMIN user)
    • An identity provider
    • An SAP Cloud Platform Admin & Security Admin privileges
    • SAP Cloud Platform Organization, Subaccount, Space, and SAP HANA Cloud instance running
    • A Neo environment with WebIDE Full-Stack enabled
    • An HDI container with a Calculation View built and deployed to Cloud Foundry

    Tutorial Overview:

    1. Creating a user-provided service that connects to the SAP HANA Cloud host
    2. Deploying the HANA Analytics Adapter (HAA)
    3. Creating a database user that can access the SAP HANA Cloud host from SAP Analytics Cloud
    4. Establishing trust between XSUAA and SAP HANA Cloud
    5. Granting the appropriate user permissions to an SAP Cloud Platform account
    6. Creating the live data connection from SAP Analytics Cloud
    Next Section: Step 01: Creating a User-Provided Service that Connects to the SAP HANA Cloud Host
  • Step 01: Creating a User-Provided Service that Connects to the SAP HANA Cloud Host

    Note: This step assumes that you are relatively familiar with SAP Cloud Platform and have a space that contains an SAP HANA Cloud instance. If you would like to become more familiar with SAP HANA Cloud and provisioning instances, check out the Getting Started learning track.

    1. Let’s start inside the SAP Cloud Platform space where an SAP HANA Cloud instance is provisioned. Click on SAP HANA Cloud in the left-side navigation menu.
    2. On the SAP HANA Cloud page you can find the SAP HANA Cloud instance’s endpoint. Copy this endpoint, it will be used in our User-Provided Service (UPS) to connect directly to your SAP HANA Cloud host.
    3. Once the endpoint is copied, click on User-Provided Services under Services, on the left-side menu.
    4. Click on Create a New Instance.
    5. Give the instance a name and the following credentials:
      Note: Please make sure to include your endpoint exactly as it’s copied from your instance tile, including the port number.
      "url": "jdbc:sap://<HANA_CLOUD_ENDPOINT>?encrypt=true&validateCertificate=false&databaseName=H00&currentschema=DBADMIN"

    6. Click on Save.
    Prev Section Next Section: Step 02: Deploying the HANA Analytics Adapter (HAA)
  • Step 02: Deploying the HANA Analytics Adapter (HAA)

    The HANA Analytics Adapter is a multi-target application that acts as middleware between SAC and an HDI container.

    1. Open SAP Web IDE Full-Stack connected to your SAP HANA Cloud instance. If you are unsure how to do this, check out this tutorial.
    2. On a separate browser tab, open this GitHub project: https://github.com/saphanaacademy/haa
    3. On GitHub, click on Code and then on the copy icon to copy the project URL for cloning.
    4. On the SAP Web IDE, right-click on Workspace, then click on Git, and finally on Clone Repository.
    5. When prompted to commit, click on Do It Later.
    6. After the clone is completed, right-click on the mta.yaml file, then click on Open Code Editor.
    7. Replace every instance of <hdi-container> (there are 3 total) with the name of the user-provided service that you created in step 1. You can use Ctrl+F to search the file, but don’t forget to remove the brackets.
    8. Delete the following lines of code:
      – Line 18 (number may vary slightly): — SAP_JWT_TRUST_ACL: ‘[{“clientid”:”sb-haa-java”, “identityzone”: “*”}]’
      – Line 45 (number may vary slightly): — TENANT_HOST_PATTERN: ‘^(.*)-<space>-haa.cfapps.(.*).hana.ondemand.com’
    9. Replace <sac-host> with the SAP Analytics Cloud host URL, without brackets. You can find this by logging into the SAP Analytics Cloud homepage and copying it from the address bar.
      Note: Don’t include “https://” or any / symbol in the host name. The URL will most likely look like this: <account_name>.<region>.sapanalytics.cloud
    10. Save the changes in the mta.yaml file by clicking on the Save button on the top left-hand corner of the screen.
    11. Next, right-click on the xs-security.json file and click on Open Code Editor.
    12. Change the value of “tenant-mode” from “shared” to “dedicated”.
    13. Now, download the latest xsahaa-release zip file from here: https://tools.hana.ondemand.com/#hanatools
    14. Extract the files from the zip to your local machine.
    15. Locate the java-xsahaa .war file.
    16. On the SAP Web IDE, expand your cloned repository and find the /haa-java/target directory.
    17. Right-click on this directory, click on Import, then on File or Project.
    18. Browse to the .war file on your local machine.
    19. After importing the file successfully, build the application by right-clicking the haa project and clicking on Build.
    20. After the build is successfully completed, a folder called mta_archives file is generated. Inside this folder there is a .mtar application that can be deployed to Cloud Foundry. In order to deploy the application, right-click on the .mtar file, click on Deploy, then on Deploy to SAP Cloud Platform.
    21. You will be required to select the correct Organization, Subaccount and Space where you want to deploy the application.
    22. When the deploy is successful, you can see two applications running by going to the SAP Cloud Platform cockpit, under the space you previously selected. They will be called: haa and haa-java.
    23. On the SAP Cloud Platform cockpit, click on the haa application.
    24. Then click on the link under Application Routes. This will open a new browser tab.
    25. On the URL, remove the /index.html and instead insert /sap/bc/ina/service/v2/HeartBeat to the URL. Press Enter. You should see a login prompt. Do not log in at this time, but this is confirmation that the application is running correctly.
    Prev Section Next Section: Step 03: Configure or check the JWT identity provider in SAP HANA
  • Step 03: Configure or check the JWT identity provider in SAP HANA

    Now it’s time to configure the JWT identity provider in SAP HANA Cloud. This will allow SAP Analytics Cloud to identify you. Most likely, you don’t have a JWT identity provider set up yet. But in this tutorial, you will see how to check for that as well.

    The first step is to obtain the certificate for your account, as you will need it to set up the trust configuration between HANA Cloud and SAP Analytics Cloud.

    1. On the SAP Cloud Platform cockpit, navigate to the Applications inside your space, then click on the haa application.
    2. On the left-side menu, click on Environment Variables.
    3. Look for the first instance of the value “url”. This is the URL for your application. Copy it and paste it on a new tab.
    4. Add the following to the end of your URL: /sap/trust/jwt
    5. You will see not only your certificate, but also the issuer of the certificate. Please keep this tab open, as you will need it later in the process.
    6. Go to the SAP HANA Database Explorer or open the Explorer from your SAP Web IDE environment.
    7. Make sure you have the correct database selected here – it should be your SAP HANA Cloud database in the same space where you created the user-provided service – and then open the SQL Console by clicking on the button at the top left-hand side of the screen.
    8. Run the following statements to check if you have a JWT provider already running and if there is a certificate for the PSE:
      SELECT * FROM SYS.PSES where purpose = 'JWT';
      SELECT * FROM SYS.CERTIFICATES;
      SELECT * FROM SYS.JWT_PROVIDERS;

    Depending on the results of this query, you need to follow separate paths. Please proceed to the next step that corresponds to the results you received from the query above.

    Prev Section Next Section: Step 04a – You already have a JWT provider in SAP HANA Cloud
  • Step 04a – You already have a JWT provider in SAP HANA Cloud

    If you have results after running the query, then you already have a JWT provider in SAP HANA Cloud. You can now check if the certificate you see listed on your query results matches the certificate you got from your application. Compare the values to make sure it’s the exact same certificate. Please also check that the issuer is the same.

    To better compare the results, download the certificate from your query results to your local machine.

    If everything matches, please proceed to step 05.

    If it does not match, please use the instructions on step 04b.

    Prev Section Next Section: Step 04b – You need to set up a JWT provider and a PSE certificate
  • Step 04b – You need to set up a JWT provider and a PSE certificate

    If you received an error or no results from the query in tutorial Step 03, then you don’t have a JWT provider already running in SAP HANA Cloud. To create one, follow these steps:

    1. On the SQL Console, run the following statement to make sure there are no providers running. The statement should return an error confirming that there is no JWT provider available.
      DROP PSE SAPXSUAAJWT;
    2. Next, it’s time to create your certificate. On the application tab, copy the whole certificate syntax (including the “—Begin Certificate” and “End certificate—) and paste into the statement below, between single quotes, as the example below. Press Run or F8 to run the statement.
      CREATE CERTIFICATE FROM '<PASTE_YOUR_CERTIFICATE_HERE>';
    3. Now you need to find out your certificate ID by running the following statement:
      SELECT CERTIFICATE_ID FROM SYS.CERTIFICATES;
    4. Copy your certificate ID and save it somewhere on your local machine.
    5. Now create your JWT provider and add your certificate ID to it with the following statements. Make sure to replace <<certificate id>> with your certificate ID without brackets.
      CREATE PSE SAPXSUAAJWT;
      ALTER PSE SAPXSUAAJWT ADD Certificate YOUR_CERTIFICATE_ID_HERE;
    6. Now run the following statement to define the JWT provider in the SAP HANA Cloud database.
      CALL SYSTEM.set_pse_purpose('SAPXSUAAJWT', 'JWT', ARRAY());
    7. Now run the following statement. Make sure to replace YOUR ISSUER URL HERE with the issuer you can find on your application tab, no brackets.
      CALL system.create_jwt_provider( 'JWTPROVIDER_HC_PROV', 'YOUR ISSUER URL HERE', 'user_name', TRUE);
    8. Now let’s check if the provider and the certificate was created correctly:
      SELECT * FROM SYS.PSES;
      SELECT * FROM SYS.CERTIFICATES;
      SELECT * FROM SYS.PSE_CERTIFICATES;
      SELECT * FROM SYS.JWT_PROVIDERS;
    9. From the results, click on Result 4.
    10. Copy the name of the JWT identity provider, as you will need this information to continue.
    Prev Section Next Section: Step 05 – Creating a user for the connection between SAP HANA Cloud and SAP Analytics Cloud
  • Step 05 – Creating a user for the connection between SAP HANA Cloud and SAP Analytics Cloud

    Now it’s time to create a new user and associate this user with the email address of the person who will be creating the connection on SAP Analytics Cloud. It’s crucial that you use the same email address to be able to be signed on.

    1. In the SQL Console, use the following statement to create your new user. If you want to use an existing user, you can simply skip the first line of the statement below and make sure your username is correct.
      CREATE USER <USERNAME> PASSWORD <PASSWORD> NO FORCE_FIRST_PASSWORD_CHANGE set usergroup default;
      GRANT EXECUTE ON SYS.EXECUTE_MDS_DEV to <USERNAME>;
      ALTER USER <USERNAME> ENABLE JWT;
      ALTER USER <USERNAME> ADD IDENTITY '<your.email@sap.com>' FOR JWT PROVIDER <JWTPROVIDER_HC_PROV>;
    2. Once the user is successfully created, go to the SAP Cloud Platform cockpit.
    3. On your Subaccount, click on Security on the left-hand side menu.
    4. Then click on Role Collections on the left-hand side menu.
    5. Click on the plus icon to add a new role collection.
    6. Give your new role collection a name and then click on Create.
    7. Click on the name of your new role collection to select it.
    8. Click on Edit on the top right-hand side of the screen.
    9. In the Roles area, click on the plus icon.
    10. Select the role associated to your haa application. You will recognize it be checking the application name next to the role name.
    11. In the Users area, click on the plus icon.
    12. Type in your user ID, typically the email address. As you type, if the user is recognized you will see the name appear on the suggestion area below the field. Select the correct user.
    13. On the top right-hand side of the screen, click on Save.
    14. Now go back to your Subaccount level by clicking on the subaccount name on the breadcrumbs at the top middle of the screen. Let’s check that your user has the appropriate role.
    15. Then, on the left-hand side menu, click on Trust Configuration.
    16. Click on your default identity provider name.
    17. Type in your user’s email address and then click on Show Assignments. Make sure the Role Collection you created previously is listed here as a role for this user.
    18. To test if the user is working go back to the Space in which you deployed the application and click on Applications.
    19. Open the haa application.
    20. Click on the application route URL. This will open a new tab.
    21. Remove the /index.html from the URL and replace it with /sap/bc/ina/service/v2/HeartBeat. You will see a login prompt.
    22. Now use your email and password to login. Please use the email and password you use to login to the SAP Cloud Platform. For SAP employees, make sure to add your 2-factor authentication code to the end of your password.
    23. You will see a confirmation after logging in.
    Prev Section Next Section: Step 06: Give your new user access to an HDI container
  • Step 06: Give your new user access to an HDI container

    Now that your connection is almost ready, it’s time to give the new user access to an HDI container. Please beware that this container must contain a cube, which means, a fully built calculation view.

    1. On the SAP Web IDE, locate the project that contains your calculation view.
    2. Right-click on your database module and click on Open HDI container. This will take you to a Database Explorer view, with your container open.
    3. Right-click on your container name and then click on Open SQL Console (Admin).
    4. To get the name of your schema, right-click on any table or view. Then click on Generate SELECT statement.
    5. Save the name of your schema, as you will soon need it.
    6. If you have a role ( .hdbrole file) within your HDI container, then use the following statement. Replace DB with the name of your schema, and make sure to replace YOUR_USER with the user name you wish to use.
      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;
    7. If you do not have a role, use the statement below to grant SELECT access to your user. Add “#DI” to the schema name you got from the previous select statement and replace “DB#DI”. Also replace YOUR_USER with the user name you wish to use.
      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;

    For more information, please see the HANA Deployment Infrastructure Reference.

    Prev Section Next Section: Step 07: Creating the Connection in SAP Analytics Cloud
  • Step 07: Creating the Connection in SAP Analytics Cloud

    Note: The SAP Analytics Cloud user must have admin privileges to add connections.

    1. Go to your SAP Analytics Cloud tenant and log in.
    2. On the homepage, click on the side navigation menu icon on the top left-hand side of the screen, then click on Connection.
    3. On the Connection page, click the plus icon to add a new data source.
    4. Expand Connect to Live Data. You can also filter your options by clicking on Cloud as your data source type.
    5. Click on SAP HANA.
    6. Give the connection a name.
    7. Go back to the SAP Cloud Platform cockpit to get the address of your host. On the space level, click on Applications.
    8. Then, click on the haa application.
    9. Next, on the application’s overview page, copy the link in the Applications Routes section. This is the host URL that is needed in SAP Analytics Cloud.
    10. Go back to SAC and paste the URL on the host. The port is 443.
    11. Next, select SAML Single Sign-on as the authentication method.
    12. Click on OK. A pop-up will appear and authenticate you. If prompted, fill in the credentials of the SAP Cloud Platform.
    13. Your connection is now working!

    You can now use SAP Analytics Cloud to create data models and stories based on the data coming from the new connection. To learn how to create a data model from a live connection, please see this tutorial. You are done!

    Prev Section Completed