Manipulating WBS trees in HANA

Greetings,

During my latest development, I came upon an interesting scenario that had to deal with manipulating data linked to WBS trees.

My first impression, was to use Analytical Views to take advantage of MDX plugins that nicely pivot and showcase hierarchies in MS Excel.

We have been doing UI5 webapps for our customer and they wanted to use a similar data consumption tool instead of Excel.

This was going to be challenging project since it required to present the WBS hierarchies as flat data as I had done for previous projects.

Time to start working…..

First order of business, had to deal with getting the WBS tree ready for manipulation.

Consider many unbalanced WBS trees of similar fomat:

Each node will posses a value(s), some of those values will be aggregated to it’s parent, and to it’s grandparent, etc. some won’t.

I needed a view that would show the full tree as shown above.

Tables PRPS and PRHI contain the information I needed.

PRPS will contain the WBS element

PRHI will contain the siblings, parent, first child of the node, and conveniently the Hierarchy pointer (PRHI-PSPHI) which accompanies nodes that belong to the same tree.

For my tree, I needed each node to also showcase the root node, which the PRHI table does not provide.

I created a view that would map each WBS element to it’s root node.

How to achieve this?

 

Join the Hierarchy table to the WBS table with only the root nodes, (Root nodes are the ones where UP=’0000000′, similarly leaves have DOWN=’0000000′)

 

See below:

 

Now each WBS element would have a root and a hierarchy pointer attached to it.

To finish building the tree, I needed to add the rest of the information that the PRHI provides (siblings, parent, first child)

Index this view to the PRHI table and now I have the WBS tree that I want.

Notice how the root node shows up(parent as NULL) this can be useful if you are building some sort of recursive functionality. As of now, I’m not quite sure we can do recursion (e.g. WITH RECURSIVE or something similar in HANA), but I left it there for demonstrative purposes.

So far I just have plain mapping of data, I still need to actually give it a hierarchy.

Time to start taking care of your kids….

To create a truly useful mapping. I need a parent-child hierarchy. PSPNR and UP provides such mapping.

Create the hierarchy as seen below.

In this case, I’ve used POSID, and UP_POSID as my hierarchical mapping for business reasons. PSPNR and UP_PSPNR are better candidates since they actually show a numerical order.

Note: Up to this point what I have described can also be used for MDX, if that is what your business case is. You just need to adapt the hierarchy with whatever values you are aggregating in an Analytic View.

Once we have built the hierarchy, we are presented with a columnar hierarchical view in the SYS_BIC schema. This will nicely show you numerical ordinals and paths as seen below:

For the continuation of this example, I will only be interested in the path.

Mapping your family tree

Recall that only some values need to be aggregated for each node subtree. The path field from the hierarchy will aid me in that process, since recursion is not doable.

I need to add the path to the hierarchical tree that already has the values (values are calculated in another view with the hierarchy included)

I cannot do this join in HANA graphical views, but I can do it in a procedure. Which is what is done below:

I highlighted Path and root since these are the values that will give me the full aggregation without recursion. Note that I’m still using POSID as my main index since that is what I used for my hierarchy. If you are using PSPNR then adapt the code to use that field.

Next, I just wrap this procedure in a scripted calc view so I can analyze the data, which will now include root and path.

Looking for your descendants

At this point I posses all the values and path and root for each WBS node, so how do I aggregate each node subtree?

I deliberately expand each node to include the path via the root node. Then I weed out the records where the WBS element is not included in its path

When joining via the root node, each WBS element will have the maximum number of records for that tree.

For example,

POSID = 2101CRS will map to 255 records as it is the root (full tree)

POSID = 2101CRS_53 will also map to 255 records, but I need to reduce to only 7 since only 7 records have this POSID in its path.

the diagram below goes into it graphically.

Here’s how it looks in the HANA views

For POSID=2101CRS

For POSID =2101CRS_53

As you can see, both will have 255.

To reduce I just need to see if the POSID is included in the path and filter out those that aren’t. Now for this step, you can use whatever methods you want. I utilized a calc column as a flag and used the instr() function to identify if that substring (POSID) is in the PATH.

with this flag I can weed out unwanted records.

POSID=2101CRS will still have 255 since it is the root node, All WBS paths will have it.

POSID=2101CRS_53 will have 7 records as only those have this POSID in its path.

Now to get the aggregated node subtree values, I just need to aggregate by the POSID since it will now have all of the values of its descendants.

A nice and happy family

At this point I have the values that I wanted aggregated for each node. Without the use of any recursive functionality, I was able to compress the data that I wanted. Now if your business case requires you to have some values aggregated while others that don’t, Just join to the view that has the tree with the values since now you will have a one-to-one mapping.

Afterthoughts

Now, I made this design using SPS09. Reading the HANA literature, SPS10 will have a nice feature that will allow you aggregate by the hierarchy directly from SQL (i.e. no need to expand and reduce as I did). I’ll stay tuned for that feature and try this design with that in mind.

Thank you for reading and leave you comments and thoughts below.

Best,

Luis Zelaya