Skip to Content
Technical Articles
Author's profile photo Markus Fath

Database object lineage using SAP HANA Graph

In SAP HANA Cloud, you find database objects like tables, views, procedure, functions etc. These objects do not “stand alone”, but rather depend on each other. SQL views are built on tables, and are used in procedures. When migrating or changing database objects, it is often required to understand the “dependency tree“.

In this blog post, I will briefly describe how you can use the SAP HANA Graph engine to explore these dependencies. Note that this is just an example to showcase the graph engine’s capabilities – it is not an official database operation. The code runs on SAP HANA Cloud QRC2 (including SAP HANA Cloud Trial) and can be found on github.com.

SAP HANA exposes its database objects and dependencies via two system views: SYS.OBJECTS and SYS.OBJECT_DEPENDENCIES. From a graph perspective, these two datasets represent a graph’s vertices and edges. So, the only thing we need to do to explore database objects graph is to create a GRAPH WORKSPACE, pointing to the OBJECTS and OBJECT_DEPENDENCIES.

The GRAPH WORKSPACE can be visualized using the SAP HANA Database Explorer. In the above image, the green vertices are database views, the light blue vertex is the graph workspace, and the red ones are procedures. We see that the procedure GS_SPOA depends on the V_OBJECT_GRAPH workspace, which in turn depends on the V_VERTICES view and so on.

Now, to retrieve depending and dependent objects programmatically, we can run a piece of GraphScript code that calls the built-in algorithm SHORTEST_PATHS_ONE_TO_ALL. The code block returns a set of “upstream” or “downstream” vertices, including their hop-distance to the start object. In the example below, we started at the view V_EDGES and found 10 object depending on the view.

You can find the simple script on github.com.

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      Hello Markus,
      I have tried your script in on on-premise HANA system (rev55) and I get the following error:

      Could not execute 'DO(
      IN i_start BIGINT => 201917,--201935, -- the key of the start vertex
      IN i_dir NVARCHAR(10) ...'
      Error: (dberror) [7]: feature not supported: Anonymous blocks with GraphScript are currently not supported

      Any idea what should be changed into the script to make it working ?

       

      Author's profile photo Markus Fath
      Markus Fath
      Blog Post Author

      Hej Michael,

      yes, anonymous blocks using the GRAPH language are only supported in HANA Cloud. To run the logic in a HANA on-prem system, you need to put the code into a procedure. I've added such a procedure to the script on github.

      Regards, Markus

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      Thanks Markus, the procedure is working fine.
      Would it be possible to get a screenshot of the setting you applied to get different colors depending on the OBJECT_TYPE. I have tried but it is not working.

      Author's profile photo Markus Fath
      Markus Fath
      Blog Post Author

      you need to click on the settings icon

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      there is no dropdown list to select color in my case, It is a free text. I have tried to type GREEN but it is not working.
      I'm in revision 55. Is it a known issue ?

      Author's profile photo Markus Fath
      Markus Fath
      Blog Post Author

      sorry, I don't know. Maybe you can try on a different browser? The drop-down ultimately select HEX values. You might try with #F0F0F0.

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      Thanks, working fine this way