Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

Laszlo_Thoma
Product and Topic Expert
Product and Topic Expert
‎04-26-2022 12:50 PM

banner.png

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

Import SFLIGHT database 
To import the sample database follow the below documentations:

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:

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.

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

Conclusion
In this tutorial I have downloaded and import the SFLIGHT demo database on my virtual environment. Based on the demo database content I have created Attribute View, Analytic View, Calculation View, Analytic Privilege. During the process I have received some error and warning message but I have found solutions which was working in my case. Also on high level I have used Calculated Columns and Filter options.

Question
Q&A link for SAP HANA, express edition: https://answers.sap.com/tags/73555000100800000651

Labels in this area
Popular Blog Posts