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
To import the sample database follow the below documentations:
- HOWTO – Import SFLIGHT sample data into HANA from a local computer
- HOWTO – Import SFLIGHT sample data into SAP HANA from a local computer
- SFLIGHT import in HANA Express 2.0 causes Error: cannot load table due to old persistence format
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 OPTIONWhen 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
