Track ISS with SAP Data Intelligence and SAP HANA: Store and analyze data

This example is used as a demo in a session INT105 – Build Data Pipelines with SAP Data Intelligence at SAP TechEd 2020. You are more than welcome to join this session and to watch that demo. But it is not a prerequisite, if you want to continue with this post.

In the previous post we started building a data pipeline in SAP Data Intelligence to ingest data from an API end-point and to transform the data from the TLE encoded format to a readable JSON payload. Now it is time to load this data, and we will use SAP HANA, express edition, for that.

Ok, let’s continue our journey…

Add SAP HANA connection configuration

Let’s go to Connection Management application in SAP Data Intelligence.

In the default tenant of a trial instance you may a connection HANA_LOCALHOST among others. It might be a good option to play with if you want to read/write data from SAP HANA from within Data Intelligence trial instance. But in our case I want to be able to have access to data in SAP HANA db from other external clients too.

Let’s create another connection. As I’ve mentioned it will be cloud-hosted instance of SAP HANA, express edition. I will call it MyHXE_HXE_SYSTEM.

You may see I am turning Use TLS option on. It is better be safe, than sorry.

Test Connection was successful, so let me Create this configuration.

Check objects in the Metadata Explorer

Now that the connection has been created, let’s check it in the Metadata Explorer application. If you are not familiar with the Metadata Explorer, than I would recommend to check tutorials first:

• Use, Discover, and Profile Data with SAP Data Intelligence, trial edition

In the application go to Catalog > Browse Connections…

… and then to our connection (in my case it is MyHXE_HXE_SYSTEM) and to the SYSTEM schema. In my system it is empty for the moment.

Add SAP HANA operator to the data pipeline

Back to the Modeler application and our graph created in the previous post let’s add a SAP HANA Client operator to the data pipeline.

Connect the last Wiretap’s out port to HANA Client’s data port.

Let’s configure the operator by defining following parameters:

• Name: ISS locs to SAP HANA
• Connection: MyHXE_HXE_SYSTEM from the Connection Manager
• Table name: "ISS_TRACK"
• Table columns: [{"name":"TSTMP","type":"SECONDDATE"},{"name":"LAT","type":"DOUBLE"},{"name":"LON","type":"DOUBLE"},{"name":"ALT","type":"INTEGER"}]
• Input format: JSON
• Insert mode: INSERT
• Table initialization: Create
• Decimal output: Floating-point
• Terminate on error: False

The JSON configuration of table columns should allow us to see nice form view, when opened in Table Details preview.

Once this additional configuration is completed it is time to save and execute the graph.

And once it is running…

Check the ISS_TRACK object in the Metadata Explorer

Go back to the Metadata Explorer. If needed refresh the screen to see changes in the SYSTEM schema of MyHXE_HXE_SYSTEM connection.

You should see ISS_TRACK object of the type “Table” there.

Go to the Fact Sheet of this object and switch to Data Preview view. You should see data inserted by the running graph.

Let the graph run for at least 10 minutes to collect some data.

Exploration of data in SAP HANA

Once there is at least 10 minutes of data collected we can stop the graph’s execution and move to exploration of data in SAP HANA.

If you followed my previous posts, then you know that thanks to Mathias Kemeter I like using DBeaver database manager thanks to its nice built-in visualization of spatial data.

Let’s have a look what path the ISS made while I was running a graph collecting the data.

SELECT UTCTOLOCAL("TSTMP") AS "TSTMP", "LON", "LAT", "ALT", 
SECONDS_BETWEEN (UTCTOLOCAL("TSTMP"), NOW()) AS "Sec_Ago", 
NEW ST_POINT('Point Z('||"LON"||' '||"LAT"||' '||"ALT"||')',4326) AS "Loc3D"
FROM "ISS_TRACK";

Let’s calculate the satellite’s “ground speed” — as if it moved at the surface of the Earth — at the last recorded timestamp.

SELECT TOP 1
  UTCTOLOCAL ("TSTMP", 'CET') as TIMECET,
  ROUND(IFNULL(NEW ST_Point('POINT ('||"LON"||' '||"LAT"||')', 4326).ST_Distance
  (NEW ST_Point('POINT ('||LAG("LON", 1, "LON") OVER (ORDER BY "TSTMP")||' '||LAG("LAT", 1, "LAT") OVER (ORDER BY "TSTMP")||')', 4326), 'kilometer')/
  SECONDS_BETWEEN (LAG("TSTMP", 1) OVER (ORDER BY "TSTMP"),"TSTMP"), 0), 2) AS "KMpS"
FROM
	(SELECT TOP 2 
		UTCTOLOCAL("TSTMP") AS "TSTMP", "LON", "LAT", "ALT", 
		SECONDS_BETWEEN (UTCTOLOCAL("TSTMP"), NOW()) AS "Sec_Ago", 
		NEW ST_POINT('Point Z('||"LON"||' '||"LAT"||' '||"ALT"||')',4326) AS "Loc3D"
	FROM "SYSTEM"."ISS_TRACK"
	ORDER BY "TSTMP" DESC)
ORDER BY "TSTMP" DESC

6.33 kilometers per second. Without getting a speeding ticket!

It is all for now covering what was included in my session’s demo. I am looking forward to virtually see you at SAP TechEd this week!

But I would like to build on this demo after the conference is over. I have some ideas, but please include yours in the comments. Much appreciated!

Stay healthy ❤️ and stay curious ? everyone,
-Vitaliy (aka @Sygyzmundovych)