Art of BI: Hierarchical Columns Require Underlying Level Columns in Presentation Layer
Christian Screen | | April 11, 2011
Those tricky developers at Oracle have provided the masses with a beautiful front-end interface for OBI 11g. They even answered the call for provided native, intuitive drill-down functionality right there in the GUI. I only have positive things to say about it after working with OBI 11g since during the Beta release. Ahhhhhh!
At a high-level, a hierarchical column is created from a logic dimension’s logic hierarchy. Clearly you can have more than one logical hierarchy under one logical dimension which means you could have more than one hierarchical column. A hierarchical column is only called by that name once it resides in the presentation layer (ultimately the Subject Area, right?). So, by dragging over a logical hierarchy (at any level, but I won’t go into that now) into a Subject Area’s Presentation Table, a Presentation Hierarchical Column is created. Ok. So, the end-users from the front-end GUI actually seen a new icon in their Analysis Subject Area selection list which differs from the Presentation Column icons they are used to seeing.
This blog post is just about one gotcha that may or may not catch a few metadata developers when attempting to leverage the new hierarchical columns when developing in the RPD. This has to do with the fact that for any level in a level-based hierarchy, the key column representing the level, must also be represented in the Presentation Layer (Subject Area) in order for that hierarchy level to be visible in the Subject Area.
Here is an illustrated example using the SampleApp repository that comes from Oracle. I’ll let you do the testing for yourself.
Illustration of Gotcha
- The A-Sample Sales Subject area has a presentation table called Products which stems from the logical table of a similar name. The H1 Product Logical Dimension is where we want to get or Hierarchical Column from. The current logical hierarchy has four levels not including the Total level. Only two presentation columns represent any portion of the H1 Product Logical Hierarchy’s level keys. Drag over H1 Product into the Products presentation table.
- What happened? The hierarchical column was created but it only has Products Total, Products Type, and Products Detail. That is because in the entire A – Sample Sales subject area the only presentation columns representing the keys for the levels in the logical hierarchy are P1 Product and P2 Product Type. Right, so there is an immediate association
- So now let’s drag in the columns from the logical table that represent the logical keys in the logical hierarchy into the Products presentation table.
- Grab the P3 LOB and P4 Brand logical columns and drag them into the presentation table for Products.
- Now that those columns are safe in the Products presentation table go ahead and drag over the H1 Products logical hierarchy once more into the Products presentation table and watch the gotcha get got.
- It’s like magic in the RPD! Sweet, as you can see in the second instance of the H1 Products hierarchical column not only did it suffix it with an increment digit like any other presentation object duplicate but all of the levels we created in the logical layer action come through to the presentation layer. As a clean up you can always create a sub-presentation table and using the -> just hide any of the standard presentation columns you don’t want your users to see in it.
RPD develop is always fun, don’t even get me started on the joys of RPD migration. But this is one of those gotchas that is actually listed in the Oracle documentation but easy to overlook if just trying this out on the fly. Hierarchical columns are a fantastic way to represent data and present an intuitive control to the functional end-user base. I trust this was helpful in nothing more than insightful.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.