Art of BI: Flatten Parent Child Vertical Table to Horizontal Generations
Author: Christian Screen | | June 26, 2009
This is just another post that I’ve been meaning to put up for a while but it just became relevant again on an OBIEE project I am working on. In Essbase I am generally a fan of loading data in using Parent/Child builds rules but as we know with OBIEE when looking at the relational datasets a parent/child table can prove to be an annoyance. And, thus the need to flatten a parent-child table into a more OBIEE-friendly horizontal table. Basically how do we get a table schema like:
CREATE TABLE olap.vDimAccounts(
, child nvarchar(50)
to look like
CREATE TABLE olap.ParentChildFlattened(
This can, of course, be accomplished using several different techniques. I am also contemplating writing a simple C# console application to handle this in a more robust command-line interface manner but that can wait. On this project, I have an Account dimension. It has ragged hierarchies, of course. Here’s my solution:
LEFT OUTER JOIN olap.vDimAccounts lev02 ON lev01.child = lev02.parent
LEFT OUTER JOIN olap.vDimAccounts lev03 ON lev02.child = lev03.parent
LEFT OUTER JOIN olap.vDimAccounts lev04 ON lev03.child = lev04.parent
LEFT OUTER JOIN olap.vDimAccounts lev05 ON lev04.child = lev05.parent
LEFT OUTER JOIN olap.vDimAccounts lev06 ON lev05.child = lev06.parent
LEFT OUTER JOIN olap.vDimAccounts lev07 ON lev06.child = lev07.parent
LEFT OUTER JOIN olap.vDimAccounts lev08 ON lev07.child = lev08.parent
LEFT OUTER JOIN olap.vDimAccounts lev09 ON lev08.child = lev09.parent
LEFT OUTER JOIN olap.vDimAccounts lev10 ON lev09.child = lev10.parent
LEFT OUTER JOIN olap.vDimAccounts lev11 ON lev10.child = lev11.parent
LEFT OUTER JOIN olap.vDimAccounts lev12 ON lev11.child = lev12.parent
lev01.parent = 'Accounts'-- IS NULL
How does it work?
I can tell from Essbase (or an import of my Essbase cube into OBIEE) that I have 12 generations so I know that I should have at least 11 columns in my final horizontally flattened table.
Next, I create a left outer join on the same table or view object containing my parent-child hierarchy each time referencing the previous instance of the said table so that I capture the true parent/child relationship.
The final where clause really just tries to determine where to kick this puppy off. In most parent/child hierarchy tables the first parent is Null to indicate that the first records child does not have a parent. I would do that for a parent/child table when building an MS Analysis Services cube or just a relational report but in Essbase, we typically fill that first parent with the name of the dimension if doing a full build so the load rule knows where to start the hierarchical build.
What other techniques exist?
Well this topic is actually somewhat obfuscated since few implementations require it and it is somewhat of a technical work-around. Here are few worthy links on twists on this adaptation:
Props On This Knowledge
I have to give mad props to Janne P. on this one because originally his post a year or so ago really helped me out. I thought I’d share the knowledge.
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.