Understanding the implementation of value based hierarchy (parent-child) in OBIEE 11G

Hello

As we all know, from 11g onwards, the feature of implementing value based hierarchy is available. There are several great articles out there that explains the steps of such implementation in 11G, therefore I am not going to repeat these things. What I want to talk about is more of a concept of value based hierarchy implementation, which will hopefully answer the questions that a lot of beginners reading about this topic may have as such "How do I know the right table structures for this type of hierarchy?"

As we know, employee dimension is a very typical example of value based hierarchy, but I don't want you to stop right here. There could be so many other tables that can be designed to support value based hierarchy based on reasons we will touch upon.

Conceptually, we all know what value based hierarchy is from looking at sample diagrams of hierarchical trees where branches are labelled with employee names. Now, if you look at the physical table where this type of hierarchy is supported, it will look like this:

Employee                 Manager                   Level (or other names)                other attributes
Me :)                                                                8                                            phone number, address etc
Superlek                     Me                              7                                             ......
Attachai                     Superlek                        6                                            .......
Sombat                      Attachai                         5                                            .......
Dekkers                     Sombat                         4                                            .........
Orono                        Dekkers                        3                                            ...........
Yodsanan                   Orono                          2                                             ...........
Mick                          Yodsanan                     1                                             ...........
Gina                           Mick                            0                                             ..........
Decharwin                  Me                               7                                             ............


From the above example, it clearly stores Employees in relation to the immediate boss in a value based hierarchical fashion. The level (or whatever name it can be) will keep an integer that indicates the person's level in this hierarchy. This table can have other attributes that handles changes and updates of employee depending on how the business wants to keep track of historical record. But the idea is pretty clear here.

Now if you want to redesign this table to store structure based hierarchy similar to time dimension, then this table structure will look like this:

Employee            Manager           Sr Manager        Director         ......       CEO
Gina                      Mick                Yodsanan            Orono                       Me :)

The reason structure based hierarchy isn't the best for employee dimension is that it's very rigid. All of the columns are fixed and levels are pretty much hard coded. In the future, if someone gets promoted, demoted, retired, or given a new rank that didn't exist before, it will become very difficult to maintain this kind of records in structure based hierarchy. The reason why time dimension is great for structure based hierarchy is that, the calender system that we use in our daily life doesn't change. In other words, 'February' will always be in 'Month' column, it will never get 'promoted' and become 'quarter' nor will be demoted to be 'Week'.

Based on the simple concept, you should be able to think of other dimensions that can be good candidates for either structure based hierarchy or value based hierarchy. It can also depend on the business process of each individual company or industry, it can also base on determining factors, such as pricing based hierarchy or promotion based hierarchy and so on.

Therefore, when it comes to determining what type of hierarchy should be implemented in OBIEE, the decision should be made way prior to it gets to OBIEE. It should be a decision made during the designing of project roadmap.

Thanks

Until next time