Select Page

Art of BI: Flatten Parent Child Vertical Table to Horizontal Generations

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:

[sourcecode language=’sql’]
CREATE TABLE olap.vDimAccounts(
parent nvarchar(50)
, child nvarchar(50)
)
[/sourcecode]

to look like

[sourcecode language=’sql’]
CREATE TABLE olap.ParentChildFlattened(
level1 nvarchar(50)
,level2 nvarchar(50)
,level3 nvarchar(50)
,level4 nvarchar(50)
,level5 nvarchar(50)
,level6 nvarchar(50)

)
[/sourcecode]

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 Accounts dimension. It  has ragged hierarchies, of course.  Here’s my solution:

[sourcecode language=’sql’]
USE MyDataBaseName
GO

SELECT
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
INTO
olap.dimAccountsFlattened
FROM
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
WHERE
lev01.parent = ‘Accounts’– IS NULL
[/sourcecode]

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 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 a 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 hieararchial 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 EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

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

CONTACT US

Work for Us

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

EXPLORE JOBS