Hero Backgroud Elements 2
Reading Time: 2 Min

Moving a SAP HANA on-premise XSA Project with Calculation Views to SAP HANA Cloud

Learning Article
  • If you have calculation views in your SAP HANA on-premise XSA project and you want to be able to use those calculation views within SAP HANA Cloud, in this tutorial we’ll give you a few options on how to move them.

    We will use Git to clone the project to SAP HANA Cloud. There we will create virtual tables pointing to the original tables on your on-premise database and use these tables in the project. This switch of the data sources requires only minimal changes to the project. Finally, we’ll replicate the data to SAP HANA Cloud to improve performance. This replication is transparent to the views and therefore no further changes to the project are needed to benefit from the replicated data.

    Attention!

    Although most of the features related to Calculation Views are available in SAP HANA Cloud, there are a few features that are not supported. Please check out this information node here to see the list of features and details that have been removed from SAP HANA Cloud in general. This also includes a few Calculation View features.

    Prerequisites

    To complete this tutorial, you will need:

    • Your SAP HANA Cloud instance
    • Your SAP Web IDE connected to your SAP HANA Cloud
    • Your SAP HANA on-premise with a DP Agent installed
    • To import tables into your on-premise SAP HANA using the file basketanalysisdataCSV.tar.gz. You can use this tutorial to complete this prerequisite.
    Next Section: Step 01: Move an existing XSA project involving calculation views to SAP HANA Cloud using Git
  • Step 01: Move an existing XSA project involving calculation views to SAP HANA Cloud using Git

    1. Login to your SAP Web IDE Full Stack connected to your SAP HANA Cloud instance.
    2. Right-click on Workspace in your Development area.
    3. Click on Git on the pop-up menu.
    4. Then click on Clone project.
    5. On the pop-up window, use this link to clone the project: https://github.com/SAPHANACloudLearning/basketAnalysisWithoutUPSService.git and then click on Clone.
    6. On the Git Ignore System Files, please click on Commit. You will have to insert your e-mail address and your GitHub credentials for this process to complete successfully. If you don’t have GitHub credentials, please choose Do it later instead.
    7. Right-click the cloned project, then click on Project and click on Project Settings.
    8. On the project preferences, under Cloud Foundry, make sure the correct SAP HANA Cloud API Endpoint, Organization and Space are selected. Then click on Save.
    Prev Section Next Section: Step 02: Create virtual tables in SAP HANA Cloud pointing to the original tables on your on-premise SAP HANA
  • Step 02: Create virtual tables in SAP HANA Cloud pointing to the original tables on your on-premise SAP HANA

    1. Make sure the data is correctly imported into your SAP HANA on-premise database.
    2. Create a new schema on your SAP HANA Cloud instance. To do that, simply follow the first three steps of this tutorial. You will need to remember the name of the schema later in this tutorial.
    3. Next, create virtual tables in your SAP HANA Cloud that point to the original tables on your on-premise SAP HANA instance. You can use this tutorial.
      Make sure you create the following virtual tables:
      – Your_Schema_Name.TLOGF
      – Your_Schema_Name.TLOGF_B
      – Your_Schema_Name.TLOGF_H
      – Your_Schema_Name.TLOGF_C
      – Your_Schema_Name.TLOGF_O
    4. Now you need to create a role that grants the permission SELECT WITH GRANT on your new schema. To create the role and grant the right permission, please input the following code on the SQL Console. Make sure you do this using the same user that created the virtual tables to simplify the process.
      CREATE ROLE "GRANTWithSELECTonBASKETANALYSISDATA";
      GRANT SELECT on SCHEMA "YOUR_SCHEMA_NAME" to "GRANTWithSELECTonBASKETANALYSISDATA" WITH GRANT OPTION;

    Now we will adapt the project, so the virtual tables are used. To help you do that, we will show you two different ways of how to do this. In the first option, you can do it without a user-provided service, changing the project slightly. In the second option, we will keep using a user-provided service and no changes are needed in your project. You need to choose step 03a OR step 03b, but not both.

    Prev Section Next Section: Step 03a: Adapt the project to use these virtual tables without User Provided Service
  • Step 03a: Adapt the project to use these virtual tables without User Provided Service

    1. Start by granting the role with SELECT WITH GRANT on virtual table schema to your projects. That means all object owners of projects created in SAP Web IDE will have this role. On the SQL Console, insert the following statement:
      GRANT "GRANTWithSELECTonBASKETANALYSISDATA" to "_SYS_DI#BROKER_CG"."_SYS_DI_OO_DEFAULTS";
    2. Then, redirect your existing synonyms to the virtual tables you previously created. To do this, you need to edit your . hdblogicalschema file using the Code Editor. Start by right-clicking your . hdblogicalschema file, then clicking on Open Code Editor.
    3. On your hdblogicalschema file, change the schema, which is currently “BASKETANALYSISDATA”. It needs to instead be the name of schema where your virtual tables are located.
    4. Save the file by clicking on the Save button on the top left area of the screen.
    5. Now right-click on your DB folder, then click on Build and finally click again on Build to start building your project.
    Prev Section Next Section: Step 03b: Adapt the project to use these virtual tables with a User Provided Service
  • Step 03b: Adapt the project to use these virtual tables with a User Provided Service

    The second option you can use to make your project consume the new virtual tables is to use a User-Provided Service. You need to choose step 03a OR step 03b, but not both.

    1. Import a new project that is based on a user provided service. This time, use this link to GitHub to clone the project: https://github.com/SAPHANACloudLearning/basketAnalysisWithUPS.git
    2. Create a user provided service called “UPSWithSchema” that points to the schema where the virtual tables are located and has the authorization to grant the respective role. To do this:
      1. First, go to the SAP Cloud Platform Cockpit.
      2. On the SAP Platform Cockpit, access your space.
      3. Next, on the left-hand side menu, click on Services and then User-Provided Services.
      4. Then click on New Instance.
      5. The name of your new service has to be “UPSWithSchema”.
      6. Under Credentials, please replace the standard code with the following:
        {
        "endpoint": "<your endpoint>",
        "host": "<your host>",
        "port": "443",
        "user": "DBADMIN",
        "schema": "<schema of virtual tables>",
        "password": "<password of user DBADMIN>",
        "driver": "com.sap.db.jdbc.Driver",
        "tags": "hana",
        "certificate": "-----BEGIN CERTIFICATE-----\nMIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9
        w0BAQUFADBh\nMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
        \nd3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBD\nQTAeFw0wNjExMTA
        wMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVT\nMRUwEwYDVQQKEwxEaWdpQ2Vyd
        CBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5j\nb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290
        IENBMIIBIjANBgkqhkiG\n9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsB\n
        CSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97\nnh6Vfe63SKMI2tavegw5BmV/
        Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt\n43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL
        +hqkpMfT7P\nT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4\ngdW7jVg/
        tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAO\nBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAU
        wAwEB/zAdBgNVHQ4EFgQUA95QNVbR\nTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUw\n
        DQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/Esr\nhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChz
        UyImZOMkXDiqw8cvpOp/2PV5Adg\n06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJF\n
        PnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0ls\nYSEY1QSteDwsOoBrp+uvFRTp2InBu
        Ths4pFsiv9kuXclVzDAGySj4dzp30d8tbQk\nCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=\n-----END CERTIFICATE-----"
        }
      7. Finally, click on Save.
    Prev Section Next Section: Step 04: Check your data
  • Step 04: Check your data

    After the build is successfully completed, you can check if the data you get from the project’s calculation views is correct.

    1. First, expand the SRC
    2. Then, right-click on any basketAnalysisEnduser.hdbcalculationview file and click on Data Preview.
    3. Next, filter the data to show only the product called Dark Blue Jeans.
    4. Now click on Raw Data on the top area of the data preview. This will show you the data that is retrieved from the view.
    5. Click on the SQL button with the pencil icon next to it on the right-hand top menu of this pane to see the SQL statement. Keep this tab open until the end of the tutorial, so you can easily run this statement multiple times.
    6. Click on the Run button to run the statement.
    7. Check the time it took for this statement to run in the Messages area at the bottom of the panel. The time will be displayed in milliseconds.
    Prev Section Next Section: Step 05: Replicate data to SAP HANA Cloud
  • Step 05: Replicate data to SAP HANA Cloud

    Now that you have the Data Preview of a calculation view open in SAP Web IDE, you are ready to continue the process and start replicating your data into SAP HANA Cloud.

    1. If you have closed the tool since the last step, click on the Database Explorer icon on the side menu of the SAP Web IDE.
    2. Click on the SQL Console icon on the top left-hand side of the screen.
    3. On the SQL Console, type the following SQL statement to replicate your data:alter virtual table <table_name> add shared replica.You need to do this for each of the virtual tables you wish to replicate.
    4. To test your replication, please see the SQL statement you opened on Step 04, item number 4.
    5. Click on the Run icon on the top left of the console to run the statement.
    6. If you are working with large data sets outside of this example, you will then see here, under Messages area, a significant speed improvement from previewing data directly from the virtual tables.

    That’s it! You have moved your XSA project to SAP HANA Cloud, created virtual tables and replicated data.

    Prev Section Completed