Skip to Content
Previous

SAP HANA XS Advanced, Creating an HDI Module

By Craig Cmehil

Part 3 of 3, Create your first HDI module for database content within your XSA application

You will learn

You will now create the HTML5 module to add basic web based content to your XSA application.

Details


The goal of HDI is to manage database artifacts from design time objects but in a way that allows multiple copies/versions of the same core objects to be used on the same HANA database at the same time.

HDI introduces the concept of the container as an abstraction of the Schema. The container in turn dynamically generates the Schema, a container-specific Database User who owns all objects, and a password for that database user. XS Advanced based services then only need access to the container and never need to know the actual Schema, technical user, or password. All of that information is stored within the container definition.

HANA Deployment Infrastructure was introduced with SPS11, and is thoroughly explained here: SAP HANA SPS 11: New Developer Features; HDI

Step 1: Create HDB Module
  1. Begin by selecting your project and then choosing New -> HDB Module

    New Module
  2. Name this new module db. Then press Next.

    Create module
  3. Leave the Namespace blank (they are not of any use anymore) and enter a name for the schema and check the Build Module after creation tick box. Press Next.

    Login
  4. Press Finish to complete the creation of the HDB Module.

    Login
  5. The wizard has created the db folder as well as the hdi-container resource and the db module in the mta.yaml file for you.

    Login

    You will be able to see some of the additional files that the module creation wizard created if you choose View->Show Hidden Files

    Show hidden files
Please log in to access this content.
Step 2: Create CDS Table

The db/src folder is where your actual database development objects belong. There are two configuration files in the root of this folder. The .hdiconfig file maps the file extensions to the specific server side activation plug-ins. This way you can choose any file extensions you wish to use as long as you map them to the correct plug-ins. However we will use the default mappings for now.

In the src folder we will create several development objects. First create a folder called data in the src folder.

Then New->CDS Artifact to create the core database tables and views in our application.

Login

Name the new CDS file PurchaseOrder and press Create

Login

As of SPS12, hdbcds is the new file extension replacing hdbdd. It contains the table and view definitions. We are creating a simple Purchase Order Header and Item data model.

The syntax is the same as CDS-based development objects previously. You will review this content in the next step.


context PurchaseOrder { type BusinessKey : String(10); type SDate : LocalDate; type CurrencyT : String(5); type AmountT : Decimal(15,2); type QuantityT : Decimal(13,3); type UnitT: String(3); type StatusT: String(1); Type HistoryT { CREATEDBY : BusinessKey; CREATEDAT : SDate; CHANGEDBY : BusinessKey; CHANGEDAT : SDate; }; Entity Header { key PURCHASEORDERID: BusinessKey; ITEMS: Association[*] to Item on ITEMS.PURCHASEORDERID = PURCHASEORDERID; HISTORY: HistoryT; NOTEID: BusinessKey null; PARTNER: BusinessKey; CURRENCY: CurrencyT; GROSSAMOUNT: AmountT; NETAMOUNT: AmountT; TAXAMOUNT: AmountT; LIFECYCLESTATUS: StatusT; APPROVALSTATUS: StatusT; CONFIRMSTATUS: StatusT; ORDERINGSTATUS: StatusT; INVOICINGSTATUS: StatusT; } technical configuration { column store; }; Entity Item { key PURCHASEORDERID: BusinessKey; key PURCHASEORDERITEM: BusinessKey; HEADER: Association[1] to Header on HEADER.PURCHASEORDERID = PURCHASEORDERID; PRODUCT: BusinessKey; NOTEID: BusinessKey null; CURRENCY: CurrencyT; GROSSAMOUNT: AmountT; NETAMOUNT: AmountT; TAXAMOUNT: AmountT; QUANTITY: QuantityT; QUANTITYUNIT: UnitT; DELIVERYDATE: SDate; } technical configuration { column store; }; define view ItemView as SELECT from Item { PURCHASEORDERID as "PurchaseOrderItemId", PURCHASEORDERITEM as "ItemPos", HEADER.PARTNER as "PartnerId", PRODUCT as "ProductID", CURRENCY as "CurrencyCode", GROSSAMOUNT as "Amount", NETAMOUNT as "NetAmount", TAXAMOUNT as "TaxAmount", QUANTITY as "Quantity", QUANTITYUNIT as "QuantityUnit", DELIVERYDATE as "DeliveryDate1" } with structured privilege check; };

Save the artifact after entering this code.

Please log in to access this content.
Step 3: Review CDS Entities and Types

Look at the syntax you just entered into the PurchaseOrder.hdbcds file in more detail.

  1. First you need to define some reusable elemental types. These will later be used to define the data type of individual columns in our tables. Within the PurchaseOrder context, create element types for BusinessKey, SDate, CurrencyT, AmountT, QuantityT, UnitT, and StatusT.

    context PurchaseOrder {
    type BusinessKey : String(10);
    	type SDate : LocalDate;
    	type CurrencyT : String(5);
    	type AmountT : Decimal(15,2);
    	type QuantityT : Decimal(13,3);
    	type UnitT: String(3);
    	type StatusT: String(1);
    
  2. You can also create reusable structures with multiple fields. This is useful when the same sets of fields are repeated in multiple tables. Create a reusable structure for History – with CREATEDBY, CREATEDAT, CHANGEDBY, and CHANGEDAT fields.

    Type HistoryT {
          CREATEDBY : BusinessKey;
          CREATEDAT : SDate;
          CHANGEDBY : BusinessKey;
          CHANGEDAT : SDate;
        };
    
  3. The syntax for creating Entities is similar to types. Entities will become database tables when activating the hdbcds file.

    Entity Header {
        key  PURCHASEORDERID: BusinessKey;
        ITEMS: Association[*] to Item on ITEMS.PURCHASEORDERID = PURCHASEORDERID;        
        HISTORY: HistoryT;
        NOTEID: BusinessKey null;
        PARTNER: BusinessKey;
        CURRENCY: CurrencyT;
        GROSSAMOUNT: AmountT;
        NETAMOUNT: AmountT;
        TAXAMOUNT: AmountT;
        LIFECYCLESTATUS: StatusT;
        APPROVALSTATUS: StatusT;
        CONFIRMSTATUS: StatusT;
        ORDERINGSTATUS: StatusT;
        INVOICINGSTATUS: StatusT;
      } technical configuration {
          column store;
      };
    

We are now ready to Build. Right click on the project and on the Build button. This process technically executes a node.js application which will call over to HANA and deploy these database artifacts into their container.

Build Project

The log should say that the Build of your project has completed successfully:

Build Project - Console

Hint: As of HANA 2.0, you can choose to build just this one file

Please log in to access this content.
Step 4: Create Sequence for Primary Key

With the tables we created, you use a unique order id number as the primary key. Therefore you need a sequence to have an auto incrementing unique id generated when new data is inserted. Create a new sequence by right-clicking on the data folder and choosing “New”, then “File”.

Login

Enter the name of the file as orderId.hdbsequence. Click “OK”.

Login

Create a non-cycling sequence within your schema starting from 200000000 and ending with 299999999. Make it dependent upon your header table with the full package id on the front of the header table name. Save your sequence file.

	SEQUENCE "orderId"
	INCREMENT BY 1 START WITH 200000000
	MINVALUE 1 MAXVALUE 2999999999
	NO CYCLE
	RESET BY
	SELECT IFNULL(MAX(PURCHASEORDERID), 0)+1
	FROM "PurchaseOrder.Header"

Build the orderId.hdbsequence file by right-clicking on it and selecting Build Selected Files.

Please log in to access this content.
Step 5: Check the Database Explorer

It is now time to check what you have created so far. Open the Database Explorer from the Tools -> Database Explorer menu.

In SPS12, this was known as the HANA Runtime Tool and it was separate from the Web IDE. You can open a new browser tab and navigate to http://<hostname>:51006 to access this tool. For SAP HANA, express edition you will need to navigate to http://<hostname>:51018

Click on the + sign:

Login

Search for your container based on your user name or the repository name. Select it, insert db as a display name and click OK.

Login

You can now see the container, table definitions and contents you have created so far:

Login
Please log in to access this content.
Step 6: Create and Upload Data

You may want to deliver an initial set of data within a table – particular a configuration table. In this exercises we will learn how to create automatic data load configuration and the accompanying CSV files for just such a situation.

The data load for table requires two files:
1. A csv (comma separated) file which holds the data you want to load.
2. An hdbtabledata file which specifies the target table for a source csv file.

You also have the hdbtabledata development object. This is the replacement for the old hdbti development object. Although the syntax of this object is new, the purpose is the same – to allow the loading of initial data from CSV files it target tables during their creation.

Create a file named Purchase.hdbtabledata and enter this text into it. Don’t forget to save the file afterwards.

	{
	"format_version": 1,
	"imports": [{
	"target_table": "PurchaseOrder.Header",
	"source_data": {
	"data_type": "CSV",
		"file_name": "header.csv",
		"has_header": false,
		"dialect": "HANA",
		"type_config": {
		"delimiter": ","
		}
	},
	"import_settings": {
	"import_columns": [
	"PURCHASEORDERID",
		"NOTEID",
		"PARTNER",
		"CURRENCY",
		"GROSSAMOUNT",
		"NETAMOUNT",
		"TAXAMOUNT",
		"LIFECYCLESTATUS",
		"APPROVALSTATUS",
		"CONFIRMSTATUS",
		"ORDERINGSTATUS",

	"INVOICINGSTATUS"]
	},
	"column_mappings": {
		"PURCHASEORDERID": 1,
		"NOTEID": 6,
		"PARTNER": 7,
		"CURRENCY": 8,
		"GROSSAMOUNT": 9,
		"NETAMOUNT": 10,
		"TAXAMOUNT": 11,
		"LIFECYCLESTATUS": 12,
		"APPROVALSTATUS": 13,
		"CONFIRMSTATUS": 14,
		"ORDERINGSTATUS": 15,
		"INVOICINGSTATUS": 16
		}
	},
	{
	"target_table": "PurchaseOrder.Item",
	"source_data": {
		"data_type": "CSV",
		"file_name": "item.csv",
		"has_header": false,
		"dialect": "HANA",
		"type_config": {
		"delimiter": ","
		}
	},
	"import_settings": {
	"import_columns": [
	"PURCHASEORDERID",
		"PURCHASEORDERITEM",
		"PRODUCT",
		"NOTEID",
		"CURRENCY",
		"GROSSAMOUNT",
		"NETAMOUNT",
		"TAXAMOUNT",
		"QUANTITY",
		"QUANTITYUNIT" ]
		},
	"column_mappings": {
		"PURCHASEORDERID": 1,
		"PURCHASEORDERITEM": 2,
		"PRODUCT": 3,
		"NOTEID": 4,
		"CURRENCY": 5,
		"GROSSAMOUNT": 6,
		"NETAMOUNT": 7,
		"TAXAMOUNT": 8,
		"QUANTITY": 9,
		"QUANTITYUNIT": 10
		}
	}]
	}

You need some CSV files to hold some initial test data to be loaded by the hdbtabledata configuration file. Enter this data into a file named header.csv and save it.

0500000000,0000000033,20120101,0000000033,20120101,9000000001,0100000000,EUR,13224.47,11113,2111.47,N,I,I,I,I
0500000001,0000000033,20120102,0000000033,20120102,9000000001,0100000002,EUR,12493.73,10498.94,1994.79,N,I,I,I,I

And data for the item table named item.csv. Don’t forget to save.

0500000000,0000000010,HT-1000,,EUR,1137.64,956,181.64,1,EA,20121204
0500000000,0000000020,HT-1091,,EUR,61.88,52,9.88,2,EA,20121204
0500000000,0000000030,HT-6100,,EUR,1116.22,938,178.22,2,EA,20121204
0500000000,0000000040,HT-1000,,EUR,2275.28,1912,363.28,2,EA,20121204
0500000000,0000000050,HT-1091,,EUR,92.82,78,14.82,3,EA,20121204
0500000000,0000000060,HT-6100,,EUR,1116.22,938,178.22,2,EA,20121204
0500000000,0000000070,HT-1000,,EUR,2275.28,1912,363.28,2,EA,20121204
0500000000,0000000080,HT-1091,,EUR,61.88,52,9.88,2,EA,20121204
0500000000,0000000090,HT-6100,,EUR,1674.33,1407,267.33,3,EA,20121204
0500000000,0000000100,HT-1000,,EUR,3412.92,2868,544.92,3,EA,20121204
0500000001,0000000010,HT-1100,,USD,213.96,179.8,34.16,2,EA,20121204
0500000001,0000000020,HT-2026,,USD,35.69,29.99,5.7,1,EA,20121204
0500000001,0000000030,HT-1002,,USD,3736.6,3140,596.6,2,EA,20121204
0500000001,0000000040,HT-1100,,USD,213.96,179.8,34.16,2,EA,20121204
0500000001,0000000050,HT-2026,,USD,71.38,59.98,11.4,2,EA,20121204
0500000001,0000000060,HT-1002,,USD,3736.6,3140,596.6,2,EA,20121204
0500000001,0000000070,HT-1100,,USD,320.94,269.7,51.24,3,EA,20121204
0500000001,0000000080,HT-2026,,USD,107.06,89.97,17.09,3,EA,20121204
0500000001,0000000090,HT-1002,,USD,3736.6,3140,596.6,2,EA,20121204
0500000001,0000000100,HT-1100,,USD,320.94,269.7,51.24,3,EA,20121204

Build the hdb module and go back to the Database Explorer. Right-click on any of the tables and you will see data.

Database Explorer and

Notice the column names where you used the complex type definition called History.

Please log in to access this content.
Step 7: Create Synonyms

You also need synonyms now to access any table or view outside of our container. Therefore you will create an hdbsynonym to allow the access the dummy view.

When you click on New -> File, name the file sys.hdbsynonym. The extension will make the IDE open the synonym editor automatically. Fill in the details for the dummy view in a synonym called _DUMMY

Any synonym name, table name dummy and schema name SYS
Please log in to access this content.
Step 8: Create Procedures

Create a procedures folder in the src folder. In the procedures folder you can create an hdbprocedure file via New->Procedure

>The syntax for stored procedures hasn't changed from previous levels of HANA.
New Procedure

With name getPOItems

getPOItems

Here is the source of getPOItems.hdbprocedure

PROCEDURE "getPOItems" ( OUT ex_addresses "PurchaseOrder.Item" )
   LANGUAGE SQLSCRIPT
     SQL SECURITY INVOKER
     --DEFAULT SCHEMA <default_schema_name>
     READS SQL DATA AS
  BEGIN
     /*************************************
         Write your procedure logic
     *************************************/

     	   ex_addresses =
  	     select *
  	              from "PurchaseOrder.Item";
  END

Remember to Save.

Please log in to access this content.
Step 9: Create Roles

All access to our HDI database objects from XSA is done automatically by the HDI container technical user. However if you want to allow access via other database users (for use cases such as external reporting tools) you must create a database role. The same applies if you want to add additional privileges to the technical user. Create another folder called roles.

First, you need to create a structured privilege. This is the logical successor to the analytic privilege and allows us to perform instance filtering for our CDS view you created earlier. Enter this code into the file PurchaseOrder.hdbstructuredprivilege and save.

This will limit the access to your view to only allow users with this privilege to see items for Euros.

	STRUCTURED PRIVILEGE
	    "PO_VIEW_PRIVILEGE"
	    FOR SELECT ON
	    "PurchaseOrder.ItemView"
	    WHERE "CurrencyCode" = 'EUR'

Now create a role named admin.hdbrole and enter this code. Don’t forget to save.

  {
  	"role":{
  		"name": "admin",
  		"schema_privileges": [{
  		   "privileges": ["SELECT METADATA",
  		    			  "SELECT CDS METADATA",
  		    			  "SELECT",
  		    			  "INSERT",
  		    			  "EXECUTE",
  		    			  "DELETE",
  		    			  "UPDATE",
  		    			  "CREATE TEMPORARY TABLE",
  		    			  "TRIGGER"
  		   ]
  		}],
  		"schema_analytic_privileges": [
              {
                  "privileges":[ "PO_VIEW_PRIVILEGE" ]
              }
          ]

        }
  }

Finally, this role needs to be granted to our technical user. In order for this to be automatic, we will create a specific folder and role in the src folder.
Create a folder named defaults in src. Inside this folder, create a file named default_access_role.hdbrole with the following contents:

{
	"role":{
		"name": "default_access_role",
		"schema_roles": [{
		 "names": ["admin"]
		}]
	}
}

This is what the folder structure finally looks like:

Folder structure in Web IDE for SAP HANA

We are now ready to perform the final Build. HDB modules are now complete and can be checked in the Database Explorer.

Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

25 Min.

Beginner

Next Steps

Next
Back to top