SAP HANA, express edition and SFLIGHT demo database, modeling – Complete Tutorial

Task

Try the SAP HANA Modeling functions and possibilities with the SFLIGHT demo database.

Reason of the Article

To illustrate the process by a concrete example based on the SFLIGHT database. During the process there are many additional steps and setups are necessary. There are also some helpful documentation is available, which can be used during the process. I am trying to collect these information also inside this post. Also, this is a step-by-step guide which goes through the process.

Definitions

SFLIGHT is a sample database. Official documentation can be found here: Flight Model

Architecture

In my scenario I am using Windows 10 operating system on my laptop. I am running SAP HANA, express edition (preconfigured) Server + applications virtual machine on VMware Workstation.

Prerequisites

◉ Hypervisor (VMware Workstation Player 16)

◉ SAP HANA, express edition (version: 2.00.057)

◉ Install SAP HANA 2.0, express edition on a Preconfigured Virtual Machine (with SAP HANA XS Advanced)

◉ SAP HANA Studio (version: 2.3.63)

Import SFLIGHT database

I have done the SFLIGHT database import with SAP HANA Studio. The database had been imported into HXE tenant. The SFLIGHT schema appeared under the Catalog folder.

Also the imported tables can be seen under Tables folder.

Explore the database 

From my perspective the most important tables are SFLIGH (key: MANDT, CARRID, CONNID, FLDATE) and SBOOK (key: MANDT, CARRID, CONNID, FLDATE, BOOKID). From modelling perspective I will mainly focus on these tables.

Based on an example SFLIGHT record I check the relevant SBOOK records.

SELECT * FROM "SFLIGHT"."SBOOK" WHERE MANDT = '300' AND CARRID = 'AA' AND CONNID = '0017' AND FLDATE = '20100421'

Fetched 428 row(s) in 6 ms 71 µs (server processing time: 0 ms 567 µs)

There are some “cancelled” records also listed, so I filter them out to receive the necessary records.

SELECT * FROM "SFLIGHT"."SBOOK" WHERE MANDT = '300' AND CARRID = 'AA' AND CONNID = '0017' AND FLDATE = '20100421' AND CANCELLED <> 'X'

Fetched 414 row(s) in 4 ms 947 µs (server processing time: 0 ms 423 µs)

Now I compare the data in SBOOK and SFLIGH table.

SELECT COUNT(*), SUM(LOCCURAM) FROM "SFLIGHT"."SBOOK" WHERE MANDT = '300' AND CARRID = 'AA' AND CONNID = '0017' AND FLDATE = '20100421' AND CANCELLED <> 'X'

SELECT * ,

(SEATSMAX + SEATSMAX_B + SEATSMAX_F) AS SEATSMAX_ALL, 

(SEATSOCC + SEATSOCC_B + SEATSOCC_F) AS SEATSOCC_ALL

FROM "SFLIGHT"."SFLIGHT" WHERE MANDT = '300' AND CARRID = 'AA' AND CONNID = '0017' AND FLDATE = '20100421'

Conclusion: The active records in the SBOOK table represents the data in the SFLIGH table.

Creating a database view from the active records in the SBOOK table.

CREATE VIEW "SFLIGHT"."VW_SBOOK_ACTIVE" ( "MANDT",

"CARRID",

"CONNID",

"FLDATE",

"BOOKID",

"CUSTOMID",

"CUSTTYPE",

"SMOKER",

"LUGGWEIGHT",

"WUNIT",

"INVOICE",

"CLASS",

"FORCURAM",

"FORCURKEY",

"LOCCURAM",

"LOCCURKEY",

"ORDER_DATE",

"COUNTER",

"AGENCYNUM",

"CANCELLED",

"RESERVED",

"PASSNAME",

"PASSFORM",

"PASSBIRTH" ) AS select

T0."MANDT",

T0."CARRID",

T0."CONNID",

T0."FLDATE",

T0."BOOKID",

T0."CUSTOMID",

T0."CUSTTYPE",

T0."SMOKER",

T0."LUGGWEIGHT",

T0."WUNIT",

T0."INVOICE",

T0."CLASS",

T0."FORCURAM",

T0."FORCURKEY",

T0."LOCCURAM",

T0."LOCCURKEY",

T0."ORDER_DATE",

T0."COUNTER",

T0."AGENCYNUM",

T0."CANCELLED",

T0."RESERVED",

T0."PASSNAME",

T0."PASSFORM",

T0."PASSBIRTH" 

from "SFLIGHT"."SBOOK" T0 

where T0."CANCELLED" <> 'X'

Create Package

I have created a separate package for the SFLIGHT views.

Create Attribute View

I have created an Attribute View AT_SFLIGHT from the SFLIGHT table and added some extra column also.

SEATSMAX_ALL = “SEATSMAX”+”SEATSMAX_B”+”SEATSMAX_F”

SEATSOCC_ALL = “SEATSOCC”+”SEATSOCC_B”+”SEATSOCC_F”

RETURN_INDEX = “PRICE”*”SEATSMAX”

The RETURN_INDEX is a Calculated Column which represents the logic: “the actual flight is financially economical if all the general seats are sold”. This logic also can be more complicated e.g., 80% of the general seats or any other logic can be used. This is just an example.

When I wanted to activate it is failed with the following error message.

Repository: Encountered an error in repository runtime extension; Deploy Attribute View: SQL: insufficient privilege: Detailed info for this error can be found with guid ’06EEA404354D1B4D8AD50A3A42B6FDCD’

I have found some documentation regarding the issue:

◉ Activation error for attribute view

◉ GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS

Check the insufficient privilege:

CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('06EEA404354D1B4D8AD50A3A42B6FDCD', ?)

Solution for the error message is to give select access on this new schema to _SYS_REPO:

GRANT SELECT ON SCHEMA SFLIGHT TO _SYS_REPO WITH GRANT OPTION

When I run the Validation and the Activation again it was run successfully.

Now I wanted to add the SCARR table because of the CARRNAME column. I have connected the key columns MANDT and CARRID. When I wanted to activate it is failed with the following error message.

Repository: Encountered an error in repository runtime extension; No central table found. No attribute has been specified as a key.

I had to modify the Key property of the SFLIGHT table key fields as you can see below.

The Attribute view appeared under the proper folder.

Open the Attribute view with Data Preview option, drag and drop the labels and values. We can see the same data which we selected earlier.

Create Analytic View

I have created an Analytic View AN_SBOOK from the BOOK table with the required filter option (CANCELLED <> ‘X’).

The Analytic view appeared under the proper folder.

Open the Analytic view with Data Preview option, drag and drop the labels and values. We can see the same data than we see earlier in SFLIGHT table. This means that the SFLIGHT and the SBOOK tables are in synch.

Create Calculation View

I have created a Calculation view based on the Attribute View and the Analytic View.

Aggregation_1

There is a new Calculated column: USAGE_PCT = “SEATSOCC_ALL”/”SEATSMAX_ALL”

Projection_1

Union_1

Projection

After  Validate and Activate the Calculation view appeared under the proper folder.

Open the Calculation view with Data Preview option, drag and drop the labels and values. But error occurs.

Error: SAP DBTech JDBC: [258]: insufficient privilege: Detailed info for this error can be found with guid ‘5E19756C4DC68E44A0C82E137E3AC829’

CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('5E19756C4DC68E44A0C82E137E3AC829', ?)

I had to create an Analytic Privilege and grant the SYSTEM user the proper permission.

Analytic Privilege created:

Open the Calculation view with Data Preview option, drag and drop the labels and values.

Tha data can be checked on MANDT, CARRID, CONNID, FLDATE level.

The data can be checked on MANDT, CARRID, CONNID level.