Select Page

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(
parent nvarchar(50)
, child nvarchar(50)

to look like

CREATE TABLE olap.ParentChildFlattened(
level1 nvarchar(50)
,level2 nvarchar(50)
,level3 nvarchar(50)
,level4 nvarchar(50)
,level5 nvarchar(50)
,level6 nvarchar(50)

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:

USE MyDataBaseName

lev01.child level_01,
lev02.child level_02,
lev03.child level_03,
lev04.child level_04,
lev05.child level_05,
lev06.child level_06,
lev07.child level_07,
lev08.child level_08,
lev09.child level_09,
lev10.child level_10,
lev11.child level_11,
lev12.child level_12
olap.vDimAccounts lev01
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.

Oracle BI Publisher (BIP) Tips: Functions, Calculations & More

Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.

Sherry Milad | January 15, 2018

How to Index a Fact Table – A Best Practice

At the base of any good BI project is a solid data warehouse or data mart.

Christian Screen | March 16, 2010

Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?

Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.

Tom Hoblitzell | June 6, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.