How to build a basic solution with SAP HANA – part 1 – “The Data Model”

This tutorial is regarding to develop an end to end SAP HANA solution using several skills around HANA technologies. Today SAP HANA are a lot of stuff and are required different abilities to create a complete solution.

The solution will be split in 3 parts (model with HANA DB, controller with XS Engine and views with FIORI), this first part is calledThe Data Model“.

In this episode we are going to use the SAP HANA DB as data repository (columnar tables), SAP HANA XS Engine as connector with the data base, eclipse IDE as development environment and the SAP HANA Cloud Platform as Database server. 

Prerequisites: 

Step-by-Step

01


The first step once the eclipse have been opened is the system configuration.

You need to select the SAP HANA Development perspective.

 

02


In the Systems view:

  1. Click in the arrow.
  2. Select Add Cloud System.

 

04


1) Fill the fields as the image shown. You’ve received your account code in an email (eg: p0000trial), your user is like the account but without “trial” word (eg: p0000), and the password is the same that you wrote in the account creation. Press Next.

2) Select your database, type your user and password and press Finish.

 

05


The first time is necessary change the user password.

Set the original password, a new password and press OK.

 

06


You should to see your HANA DB Schema. We will use MYUSER instance.

Now we can create our columnar data model inside SAP HANA. All the SQL sentences are available in the appendix of this tutorial.


 

We need to GRANT some privileges to our Schema because we will create XS views and without this statement the views can’t be created.07

  1. Right click on the schema and press Open SQL Console.
  2. Put the sentence using your schema name.
  3. Execute the sentence.

 


08

  1. Put the create sentence for each table.
  2. Execute the sentence.
  3. Check the Tables folder in your schema if the table was created, (right click and refresh the folder).

09

  1. Put the insert sentences for each table.
  2. Execute the sentence.

10

  1. Right click on the table and select Open Definition.
  2. Check the table structure.

11

  1. Right click on the table and select Open Data Preview.
  2. Check the data inserted.


Appendix:

 – – GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;

GRANT SELECT ON SCHEMA MYUSER TO _SYS_REPO WITH GRANT OPTION;

– – SPFLI TABLE/DATA – –
CREATE COLUMN TABLE MYUSER.T_SPFLI(
                CARRID VARCHAR(3),
                CONNID INTEGER,
                COUNTRYFR VARCHAR(3),
                CITYFROM VARCHAR(20),
                AIRPFROM VARCHAR(3),
                COUNTRYTO VARCHAR(3),
                CITYTO VARCHAR(20),
                AIRPTO VARCHAR(3),
                FLTIME INTEGER,
                DEPTIME TIME,
                ARRTIME TIME,
                DISTANCE DOUBLE,
                DISTID VARCHAR(5),
                PRIMARY KEY (CARRID, CONNID)
);
insert into MYUSER.T_SPFLI values(‘AC’,820,’DE’,’FRANKFURT/MAIN’,’FRA’,’CA’,’MONTREAL-DORVAL’,’YUL’,28800,’10:00:00′,’12:00:00′,’5400′,’km’);
insert into MYUSER.T_SPFLI values(‘AF’,820,’DE’,’FRANKFURT/MAIN’,’FRA’,’MQ’,’FORT DE FRANCE’,’FDF’,39600,’10:00:00′,’15:00:00′,’7233′,’km’);
insert into MYUSER.T_SPFLI values(‘LH’,400,’DE’,’FRANKFURT/MAIN’,’FRA’,’US’,’NEW YORK’,’JFK’,30240,’10:10:00′,’11:34:00′,’7865′,’km’);
insert into MYUSER.T_SPFLI values(‘LH’,454,’DE’,’FRANKFURT/MAIN’,’FRA’,’US’,’SAN FRANCISCO’,’SFO’,44400,’10:10:00′,’12:30:00′,’5743.82′,’mi’);
insert into MYUSER.T_SPFLI values(‘LH’,455,’US’,’SAN FRANCISCO’,’SFO’,’DE’,’FRANKFURT’,’FRA’,48600,’15:00:00′,’10:30:00′,’5743.82′,’mi’);
insert into MYUSER.T_SPFLI values(‘LH’,3577,’ITA’,’ROM’,’FCO’,’DE’,’FRANKFURT’,’FRA’,7200,’07:05:00′,’09:05:00′,’1870′,’km’);
insert into MYUSER.T_SPFLI values(‘LH’,9981,’DE’,’FRANKFURT’,’FRA’,’DE’,’DRESDEN’,’DRS’,3300,’16:40:00′,’17:35:00′,’3000′,’km’);
insert into MYUSER.T_SPFLI values(‘SQ’,26,’DE’,’FRANKFURT’,’FRA’,’US’,’NEW YORK’,’JFK’,30000,’08:30:00′,’09:50:00′,’7865′,’km’);
– – SFLIGHT TABLE/DATA – –
CREATE COLUMN TABLE MYUSER.T_SFLIGHT(
                CARRID VARCHAR(3),
                CONNID INTEGER,
                FLDATE DATE,
                PRICE DOUBLE,
                CURRENCY VARCHAR(5),
                PLANETYPE VARCHAR(10),
                SEATSMAX INTEGER,
                SEATSOCC INTEGER,
                PAYMENTSUM DOUBLE,
                PRIMARY KEY (CARRID, CONNID, FLDATE)
);
insert into MYUSER.T_SFLIGHT values(‘AC’,820,’20.12.2002′,1222,’CAD’,’A330-300′,320,12,1600);
insert into MYUSER.T_SFLIGHT values(‘AF’,820,’23.12.2002′,2222,’EUR’,’A330-300′,320,32,2250);
insert into MYUSER.T_SFLIGHT values(‘LH’,400,’28.02.1995′,899,’EUR’,’A330-300′,320,3,2639);
insert into MYUSER.T_SFLIGHT values(‘LH’,454,’17.11.1995′,1499,’DEM’,’A319′,350,2,2949);
insert into MYUSER.T_SFLIGHT values(‘LH’,455,’06.06.1995′,1090,’USD’,’A319′,220,1,1499);
insert into MYUSER.T_SFLIGHT values(‘LH’,3577,’28.04.1995′,6000,’LIT’,’A319′,220,1,600);
insert into MYUSER.T_SFLIGHT values(‘SQ’,26,’28.02.1995′,849,’DEM’,’DC-10-10′,380,2,1684);
– – SBOOK TABLE/DATA – –
CREATE COLUMN TABLE MYUSER.T_SBOOK (
                CARRID VARCHAR(3),
                CONNID INTEGER,
                FLDATE DATE,
                BOOKID INTEGER,
                CUSTOMID INTEGER,
                CUSTTYPE VARCHAR(1),
                SMOKER VARCHAR(1),
                LUGGWEIGHT DOUBLE,
                WUNIT VARCHAR(5),
                INVOICE VARCHAR(1),
                CLASS    VARCHAR(1),
                FORCURAM VARCHAR(5),
                FORCURKEY VARCHAR(5),
                LOCCURAM VARCHAR(5),
                LOCCURKEY VARCHAR(5),
                ORDER_DATE DATE,
                PRIMARY KEY (CARRID, CONNID, FLDATE, BOOKID)
);
insert into MYUSER.T_SBOOK values(‘AF’,820,’23.12.2002′,1,1,’P’,”,250,’kg’,’X’,’F’,440,’DEM’,440,”,’21.11.2002′);
insert into MYUSER.T_SBOOK values(‘AF’,820,’23.12.2002′,2,2,’B’,”,250,’kg’,’X’,’F’,430,’DEM’,430,”,’01.11.2002′);
insert into MYUSER.T_SBOOK values(‘AC’,820,’20.12.2002′,1,1,’B’,”,200,’kg’,’X’,’C’,670,’DEM’,670,”,’26.11.2002′);
insert into MYUSER.T_SBOOK values(‘AC’,820,’20.12.2002′,2,2,’B’,”,200,’kg’,’X’,’F’,640,’DEM’,640,”,’16.11.2002′);
insert into MYUSER.T_SBOOK values(‘LH’,400,’28.02.1995′,1,2,’B’,”,200,’kg’,’X’,’C’,899,’DEM’,899,”,’26.02.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,400,’28.02.1995′,2,2,’P’,”,200,’kg’,’X’,’F’,899,’DEM’,899,”,’27.02.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,400,’28.02.1995′,3,3,’P’,’J’,300,’kg’,’X’,’Y’,850,’DEM’,850,”,’27.02.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,454,’17.11.1995′,1,3,’P’,’J’,200,’kg’,’X’,’Y’,1450,’DEM’,1450,”,’01.11.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,454,’17.11.1995′,2,1,’B’,”,200,’kg’,’X’,’C’,1499,’DEM’,1499,”,’02.11.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,455,’06.06.1995′,1,1,’B’,”,200,’kg’,’X’,’C’,1090,’USD’,1499,”,’01.06.1995′);
insert into MYUSER.T_SBOOK values(‘LH’,3577,’28.04.1995′,1,3,’P’,”,200,’kg’,’X’,’Y’,6000,’LIT’,600,”,’20.04.1995′);
insert into MYUSER.T_SBOOK values(‘SQ’,26,’28.02.1995′,1,2,’P’,”,200,’kg’,’X’,’F’,849,’DEM’,849,”,’21.02.1995′);
insert into MYUSER.T_SBOOK values(‘SQ’,26,’28.02.1995′,2,1,’P’,”,200,’kg’,’X’,’C’,835,’DEM’,835,”,’22.02.1995′);

 

spanish-version

 

 

Advertisements

One thought on “How to build a basic solution with SAP HANA – part 1 – “The Data Model”

  1. Pingback: How to build a basic solution with SAP HANA – part 2 – “Flow Controller” | Innovators at Heart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s