Dave's Adventures in Business Intelligence

This is the second of several posts that will review my presentation “Universe Models For Recursive Data” which was originally presented at the 2009 GBN conference, then at the North Texas / Oklahoma ASUG chapter meeting, and finally at the Mastering BusinessObjects conference in Melbourne. As with my other presentations there is a PDF file that can be downloaded from my conference presentations page. The first post introduced the concepts of recursive (as opposed to hierarchical) data and provided a couple of examples. In this post I will review some of the different design challenges that I have seen in working with recursive data.

I decided to identify and cover four different examples of recursive data configurations. These included Clean, Unbalanced, Ragged, and Lateral. As I mentioned in the first post, I am going to use some basic human resources (HR) data for my examples. For this post, in order to show samples of each of the four challenges, I am going to represent my recursive data using a tree. The branches of the tree show the relationships between people. The nodes of the tree contain the information about each person. The data might include their name, hire date, and position (title) within the company. In order to properly interact with my recursive data I have to be able to work with both types of information: relationships and node data as well. If you are not sure what I mean, please continue reading, this will make more sense later on.

This post will cover slides 14 through 21 from the presentation and will describe each of the different recursive challenges that I identified.

Clean Hierarchy

In my first example everything is very clean. Each branch of the tree has the same depth. Each branch follows the same path. There are no real challenges encountered in this hierarchy, pictured below.

Imagine that the top of the tree is the company president. The second level (the “B” nodes) represent vice presidents, and the third level (“C” nodes) represents divisional directors. When a hierarchy definition is very rigorous this is the type of tree I expect. For a very simple example let me suggest a product hierarchy instead of an HR chart for the moment. A product hierarchy for a food company might include a Brand Owner, the Brand, the Size, and finally the Flavor. The brand owner could be Beverages-R-Us, the brand could be Super Sports Drinks, the size is two liter bottle, and finally the flavor is Orange. Every product in the system is guaranteed to have all four of these attributes assigned, and they will all be in that exact order.

On the other hand, a human resources hierarchy is rarely as clean. Let me move on to some more interesting examples.

Unbalanced Hierarchy

An unbalanced hierarchy is one where the nodes are at inconsistent depths. Please review the tree shown below.

In the example shown above, there is one node (B1 in this case) that does not have any children while the rest of the nodes at that level (B2) do. If the A node is the company president, and the B nodes are vice presidents, it is entirely possible to have a position (perhaps “VP of Special Projects”) that does not have any additional people that report up to him or her. In that case the tree stops at the VP level and does not go down to the Divisional Director position.

Why is this a challenge? As will be seen later, one of the possible solutions to a recursive data scenario is to pivot the data (flatten) it into different columns. What happens to the missing nodes in this case?

Ragged Hierarchy

In the last example I suggested that there could be a VP of the company that does not have any direct employees. In the case of a Ragged hierarchy it’s slightly different. In this case I might see a Divisional Director who is reporting straight up to the company president without going through a VP.

Note that in the image above I am showing both an unbalanced node (B1) and a ragged node (C2). Let me focus on C2 for a moment. As I already mentioned, there is a relationship from that director position straight up to the president. It does not go through a vice president position. Why is this a challenge? Remember that earlier I mentioned there are two parts that I need to account for: the relationship and the position or node type. In this case the relationship only goes one step, but descends two levels (from president to director). I need to be able to represent both parts properly in whatever data model I come up with.

Lateral Hierarchy

If you have spent any time reviewing company organization charts you may have seen this type of relationship before: I am calling it a lateral (sideways) relationship.

It’s not uncommon to see a lateral relationship from one director to another director (C2 reporting to C1 in this example). This is one of the biggest challenges to most of the design ideas I will be sharing in my next post, because there are two things (people) occupying the same space (node type) on the tree).

Merge / Diverge

As I mentioned toward the beginning of the post that some scenarios are inherently cleaner than others because the relationships all have to exist. Unfortunately, it is quite likely to see a combination of issues. I have even seen challenges where a hierarchy does a merge / diverge relationship such as this:

SAP and other ERP vendors generally allow this sort of hierarchy to be built in order to provide the maximum flexibility to the client company. I have never tried to implement this in BusinessObjects because it simply does not work. There is no clear drill path. Suppose I drill from node B2 to C3, and then from C3 to D2. Now when I drill up, which path do I take? I can drill from D2 up to C3, and then from C3 I can drill up to either node A1 or B2. It’s ambiguous, and therefore our project team decided that we would not attempt to handle this at all. We instituted a business rule (an exception) that would kick out any hierarchy that included this sort of path.

This particular example was dropped from the presentation in the interest of time but I wanted to mention it here.

Combinations

Even without the merge / diverge issue, there are plenty of still challenges. For our project, a typical tree was both ragged and unbalanced. That meant that the solutions we discussed had to be able to handle both. We also had a number of lateral relationships that we needed to address. Our users wanted to be able to enter the tree by node type and drill by level. They wanted to see the entire tree presented as part of a prompt. And they wanted to be able to multi-select from those prompts… for any node at any level.

Next Time

Which solutions work the best? Do any solutions work for all of these different scenarios? My next post in this series will review each of the four solutions I outlined in my presentation and present a scorecard for each.

Related Links

• Universe Models for Recursive Data Part I: Introduction