Create a Database Multi-Target Application with SAP HANA service for SAP BTP

Click File->New and choose Project from Template.

Choose SAP HANA Database Application and click Next.

Call the project DM and click Next.

Remove the namespace

Choose version 2.0 SPS05 and click Finish

This project will combine artifacts from an HDI container and a classic, replicated schema.

You will first create a table to hold data from performance evaluations received by employees and the rating they give the company in terms of satisfaction.

These are sample records that will be stored in this table:

Right-click on the src folder, choose New -> File

Use the following name

data/performance.hdbtable

This will create both a folder and a file. Paste the following content into the hdbtable file to define a new table:

column table "PERFORMANCE" (
	"ID" INTEGER  unique not null COMMENT 'Employee ID',
	"EVALUATION_RATING" DOUBLE COMMENT 'Evaluation results',
	"REPORTS_TO" INTEGER not null COMMENT 'Reports to Manager',
	"FEEDBACK_COMMENT" NVARCHAR(512) COMMENT 'Feedback comment',
	"SATISFACTION_INDEX" DOUBLE COMMENT 'Employee given rating',
	PRIMARY KEY ("ID")
)
COMMENT 'Performance evaluation record'

Save the file.

What is going on?   You have created a database module and a design time artifact representing a table. When this module is built, Web IDE will automatically create an HDI container and bind it as resource to the database module. It will also create the runtime object (a physical table) in the schema associated to the HDI container.   This approach allows you to modify the structure of the table without worrying about underlying adaptation operations.

The table you have just defined will have a large text field with comments from the employees. You want to enable a text index to perform text search and mining operations.

Create a new database artifact under the data folder

Use the following name

comment_text

And choose hdbfulltextindex

Paste the following code into it:

FULLTEXT INDEX "COMMENT_TEXT"
on "PERFORMANCE"("FEEDBACK_COMMENT")
FAST PREPROCESS OFF
TEXT ANALYSIS ON

Save the file.

You will be referring to this service later for integrations. For that reason, you will specify a name for the HDI container when it is created by SAP Web IDE.

Open the file mta.yaml on the MTA Editor tab.

Open the Resources tab. Under Parameters, use the + sign to create the following key-value pair.

Save the file.

Right-click on the database module and choose Build.

Getting an error?

If the build throws an error about the builder being outdated, right-click on the project and navigate into the Cloud Foundry menu. Make sure the settings are mapped to an instance of service of type hana-db and click Reinstall Builder. Once the builder is reinstalled, try the build again.

For testing and development purposes, you can use a local file to load data into an HDI container.

Create the configuration for the upload first. In the data folder, create a file called:

loads/dataload.hdbtabledata

Paste the following content into it:

{
	"format_version": 1,
	"imports": [{
		"target_table": "PERFORMANCE",
		"source_data": {
			"data_type": "CSV",
			"file_name": "performance.csv",
			"has_header": true,
			"dialect": "HANA",
			"type_config": {
				"delimiter": ","
			}
		}
		}]
}		

Download this csv file – https://github.com/SAPDocuments/Tutorials/blob/master/tutorials/haas-dm-create-db-mta/performance.csv into your computer. Upload it into the loads folder using the Import option

Build the loads folder

Right click on the db module and choose Open HDI Container.

The editor will open the Database Explorer. Select Tables

Right-click on the table PERFORMANCE and choose Open Data

Open the SQL editor for this query

And execute the following SQL statement to complete the validation below:

SELECT AVG("SATISFACTION_INDEX")
FROM "DM_HDI_DB_1"."PERFORMANCE";