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:

HANA_blog_Nov_19_1.png

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:


HANA_blog_Nov_19_2.png

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

HANA_blog_Nov_19_3.png

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.

HANA_blog_Nov_19_4.png

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.

HANA_blog_Nov_19_5.png

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.

HANA_blog_Nov_19_6.png

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:

HANA_blog_Nov_19_7.png

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:

HANA_blog_Nov_19_8.png

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.

HANA_blog_Nov_19_9.png

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.

IMG_0109.JPG

Here’s how it looks in the HANA views

HANA_blog_Nov_19_10.png

For POSID=2101CRS

HANA_blog_Nov_19_11.png

For POSID =2101CRS_53

HANA_blog_Nov_19_12.png

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.

HANA_blog_Nov_19_13.png

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.

HANA_blog_Nov_19_14.png

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

HANA_blog_Nov_19_15.png

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.

HANA_blog_Nov_19_16.png

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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Paulo Vitoriano

    Hi Luis,

    It would be nice to illustrate this piece of work with a good business case.  Otherwise it is bit too technical reading that leaves behind any practical applications.

    Thank you,

    Paulo

    (0) 
    1. Lars Breddemann

      Since this is a technical community, I consider this blog to be anything but “behind practical applications”.

      Maybe it’s not in your experience to have to model such views – that’s alright.

      But it doesn’t diminish the quality and relevance of this post.

      Does this blog tell you all about SAP project management data design? Nope, and that’s alright, too.

      (0) 
      1. Paulo Vitoriano

        This blog shows up in PS space too due to the PS relevant tags, and that community is a bit different.

        Anyway my suggestion is just a suggestion to make it relevant for a bigger audience, otherwise it is good enough as you can confirm it.

        (0) 

Leave a Reply