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

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
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


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:

• Ryan Farley on .Net…
• StackOverflow.com Forum
• Janne Pyykkö

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.