Welcome to the SAP HANA Academy. My name
is Jamie and in this video we'll look at
the hierarchy span tree function in SAP
HANA SQL. It is important to note that
this particular function is new to HANA
2.0 SPS 02 and therefore will not
work on earlier versions. This function
generates
a hierarchy based on data with the
parent-child relationship and will
return the minimum spanning tree or
forest versus that of the hierarchy
function where all edges or
relationships in the original data are
returned. In cases where the data does
have a simple tree relationship then the
span tree will be the same as a regular
hierarchy. If you have not seen our
videos on hierarchies it is recommended
that you watch the first two videos on
the main hierarchy function as the span
tree function shares a lot of the same
options as this function. Those will not
be covered here. Links to those two
videos are in the description of this
video if you're watching this on the HANA
Academy YouTube channel. There you can
also find a link to the code used in
this video. Should you wish to copy the
syntax and follow along.
Looking at the span tree hierarchy
function it has four inputs where the
first and only required one is to
specify the source data. The source
requires two input columns which define
the hierarchies parent-child
relationship. The other specifications
are optional and consists of an ability
to filter the hierarchy and how to
handle cashing of the span tree data. The
span tree will return a table set of
eight columns just like the regular
hierarchy function. After copying the
syntax in a new SQL console we see
that we are creating a new table in
adding in some hierarchical data. There's
also a bunch of queries which we'll use
to see how the span tree function is
different from the regular hierarchy
function. If you run all of this we’ll
return four result tabs and the last one
simply returns the records from the
table we just created. Noted that there
are 15 records. In one tab over we are
returning the results using the regular
hierarchy function. Note that there are
20 records in this case. If we look at
the results visually they look like this.
Our hierarchy data consists of two
parent nodes A and X. We also see that
under parent A there is a complex
relationship as node G has three parents
C, D and F and two descendants I and J. In
the next tab over we are returning the
results using the span tree function and
in this case this function returns 14
records. As mentioned earlier the span
tree function will return the minimum
spanning tree or forest. If we look at
these results against what we saw
earlier we see that starting at parent X
it is the same as before. However in the
other tree we notice that there are a
couple less edges than before in
particular relating to no G where we
previously noted the complex
relationships. If we look at the first
result tab which is essentially checking
my work it's saying that the difference
between the original data
and the span tree function is that the span
tree function has eliminated edges D G
and F G. This means that any descendant
edges from those are also eliminated.
Going back to the data returned from the
hierarchy function the edge is
eliminated by the span tree function are
highlighted here.
Note that the edges underneath D G and
F G in particular GI and GJ are still
represented in the minimum tree
structure
but they will only be present in the
path that includes node C. Once again
this is because the span tree returns
the minimum tree or forest and in our
example this is the reason for the span
tree function returning six less records
than the regular hierarchy function. This
now concludes the lesson on the
hierarchy span tree function in SAP
HANA. Thanks for watching.