Friday, 10 April 2020

Migrate Calculation Views from On-Premise SAP HANA to SAP HANA Cloud – Part 1(EN)

Overview:


SAP HANA Cloud was released in March 2020 and many of you, especially SAP HANA On-Premise users, are interested in the next generation “Database as a Service” product. The objective of this blog is to share the experience of testing migration from SAP HANA On-Premise to SAP HANA Cloud.

Please note that SAP has plan to release migration tool, this means the steps described in this blog will be obsolete in the future.

Steps:


There are 3 tables (SO_HEADER, SO_ITEM and MD_PRODUCTS) in a classical DB schema “SALES” and 2 Calculation Views (CV_SALES and CV_PRODUCTS) in package “SALES”. Steps to migrate them are as follows.

Step1. Migrate classical Calculation Views to XSA HDI Container objects
Step2. Import the migrated objects to SAP HANA Cloud
Step3. Transfer data from On-Premise to Cloud.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Environment:

On Premise : SAP HANA 2.0 SPS04 Revision 42
Cloud: SAP HANA Cloud 4.00.000.00.1583243463 (Last Update March 3, 2020)

So let’s start the migration! The first step proceeds as follows.

1. Check the data model
2. Prepare the migration tool
3. Convert the Calculation Views to XSA (HDI) model
4 Import the migrated model into HDI Container
5. Migrate tables into HDI Container

1. Check the data model


There are 3 tables in “SALES” schema.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

There are 2 Calculation Views in “SALES” package, DIMENSION type “CV_PRODUCTS” and CUBE with Star Join type “CV_SALES”.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Package “SALES” is assigned to a Delivery Unit “SALES”.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

2. Prepare the migration tool


First, download “XSAC Migration 1” from SAP Support Portal.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Then, unzip the ZIP file and place it to any folder.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Set the environment variables.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

3. Convert the Calculation Views to XSA (HDI) model


Convert the classical Calculation Views to XSA model using the installed migration tool.

First, create a folder where migrated objects are exported. In this blog, I created “C:\tmp\xs-migration”.

Then, execute the command below to convert the objects.

xs-migration --target-dir "C:\tmp\xs-migration\Sales" SALES,SAP_JAPAN
* Please note that the target folder (“Sales” in this case) must not be existed
* “SALES,SAP_JAPAN” is a name of Delivery Unit and the vendor of it, this means the tool converts each Delivery Unit.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Successfully finished! I found files are generated in the specified folder.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Now, let’s see “report.html”. We can see the details of the generated files.
SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Finally, compress the generated source file (folder db > src) into a ZIP file.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

4. Import the migrated model into HDI Container


4.1 Import the converted objects into a HDI Container

Logon to XSA using Web IDE for SAP HANA and then import the ZIP file, which was created in the previous step, into “src” folder under Project “SALES”.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Choose the ZIP file.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Click “Import”.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

The models are successfully imported into XSA.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

4.2 Create Synonym

The new Calculation Views are now ready to be deployed to the HDI Container. However tables are not converted yet, this means tables and data are still in the classical schema “SALES”. Therefore we need to create synonym so that Calculation Views in HDI Container can access to the classical schema.

4.2.1 Create Role & User

Create two roles and a DB user for Grant service using SAP HANA Studio. Then, assign the roles to the user.

CREATE ROLE "sales::source_schema";
GRANT SELECT, EXECUTE ON SCHEMA SALES TO "sales::source_schema";
CREATE ROLE "sales::source_schema_g";
GRANT SELECT, EXECUTE ON SCHEMA SALES TO "sales::source_schema_g" WITH GRANT OPTION;
CREATE USER USR_GRANTOR password XXXXXXX no force_first_password_change;
GRANT "sales::source_schema","sales::source_schema_g" to USR_GRANTOR with admin option;

4.2.2 Register Grantor service to XSA

Register Grantor service to XSA using XSA CLI. To do this, logon to DB server and execute the command below.

xs cups EPM_XXX-table-grantor –p "{"host":"hostname.com","port":"30015","user":"USR_GRANTOR","password":"XXXXXX","driver":"com.sap.db.jdbc.Driver","tags":["hana"]}"

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

4.2.3 Edit mta.yaml

Open mta.yaml with Code Editor and add the grantor-service which was registered above.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

4.2.4 Create .hdbgrants file

Create a new file with extension “.hdbgrants” under “src”.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Insert the definition as follows, then save and deploy the file.

{
"grantor-service": {
"object_owner": {
"roles": [
"sales::source_schema_g"
]
},
"application_user": {
"roles": [
"sales::source_schema"
]
}
}
}

4.2.5 Configure Synonym

Open the “.hdbsynonym” file under the “src” folder to configure synonym definition. This file was created by the Migration tool. Click “…” in the “Object Name” column.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

For “External Services”, select the grantor service which was created in the previous step. Then input (part of) the table name and choose the actual table from the “Results” list. (In this case, choose “MD_PRODUCTS” table in “SALES” schema)

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

Repeat this for all tables, and then save and build the “.hdbsynonym” file.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

You can confirm that synonyms are created and they can be accessed with SQL statement using Database Explorer.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

4.3 Build the imported models

Finally, build the imported models.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

You can confirm that Calculation Views are created and you can access them with SQL statement from Database Explorer.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

The Calculation Views has been successfully migrated to the XSA HDI Container!!

5. Migrate the tables into HDI Container


It is OK to keep the tables in classical schema, however this blog will also migrate the tables into the HDI Container (the schema managed by the container).

5.1 Create table using HDBTable (.hdbtable)

Create HDBTable DDL files (.hdbtable) and build them. 

* Do not use CDS Table because SAP HANA Cloud does not support CDS.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP HANA Cloud

5.2 Transfer Data

Transfer data from a table (synonym) in the classical schema to a table in HDI Container using SQL statement.

insert into "SALES_1"."SALES::HDI_MD_PRODUCTS" select * from "SALES_1"."SALES::MD_PRODUCTS";

5.3 Edit synonym definition

Edit “.hdbsynonym” file as same as step 4.2.5, and then save and build the file. Synonyms needs to refer to the tables which are created step 5.1.

Check the Calculation View again and confirm that the data can be retrieved.

No comments:

Post a Comment