Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flattening a Parent Child Hierarchy


Flattening a Parent Child Hierarchy

Author
Message
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Hi Jeff,

I agree the limit of 12 levels is a bit of problem, however it can be extended if needed with a change to the Temptable and final pivot, but most of the hierarchies were dealing with are 4-7 levels, with one at 9 levels. The stats I provided are for a 7 level hierarchy and it returns 530 rows in the final output.

I'm not sure why theres a range in the Oracle table and I've only come across its use a couple of times. I suspect its just a legacy table format thats persisted though Oracle Financials since it was incepted, I've not seen the latest version of the table in Oracle Financials 12, but i dont think its changed.

I hope the code is self explanitory, as I tried to comment the important parts and what they were doing for anyone that took over the project. I look forward to reading any suggestions you might have in regards to improvements, as I'm sure there are a couple of tweaks that can be made.

Many thanks for your interest as im always keen to learn.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45062 Visits: 39904
Hooo boy! I sure dropped the basket on this one. My apologies. Are you all set or is this still a problem?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
No worries Jeff theres no rush, the code has been running for around a year within the expected parameters. I also know what its like especially if things get hectic at work and during the summer holiday's.

If you have any advice or pointers on improving the efficency then I'm all ears,

_________________________________________________________________________
SSC Guide to Posting and Best Practices
codeplexer
codeplexer
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 58
Hi someone ask me the same question: How to flatten a hierarchy self-referencing table, Google it and your thread and my first thought is Recursive CTE too, but then I'm just curios how someone do it before R.CTE. This is actually a much better way to do it and it's the fastest.

Try it on AdventureworksDW DimAccount table, the cool thing is: One line of code


SELECT
lev01.AccountKey id_01, lev01.AccountDescription name_01,
lev02.AccountKey id_02, lev02.AccountDescription name_02,
lev03.AccountKey id_03, lev03.AccountDescription name_03,
lev04.AccountKey id_04, lev04.AccountDescription name_04,
lev05.AccountKey id_05, lev05.AccountDescription name_05,
lev06.AccountKey id_06, lev06.AccountDescription name_06,
lev07.AccountKey id_07, lev07.AccountDescription name_07,
lev08.AccountKey id_08, lev08.AccountDescription name_08,
lev09.AccountKey id_09, lev09.AccountDescription name_09,
lev10.AccountKey id_10, lev10.AccountDescription name_10
FROM DimAccount lev01
LEFT OUTER JOIN DimAccount lev02 ON lev01.AccountKey = lev02.ParentAccountKey
LEFT OUTER JOIN DimAccount lev03 ON lev02.AccountKey = lev03.ParentAccountKey
LEFT OUTER JOIN DimAccount lev04 ON lev03.AccountKey = lev04.ParentAccountKey
LEFT OUTER JOIN DimAccount lev05 ON lev04.AccountKey = lev05.ParentAccountKey
LEFT OUTER JOIN DimAccount lev06 ON lev05.AccountKey = lev06.ParentAccountKey
LEFT OUTER JOIN DimAccount lev07 ON lev06.AccountKey = lev07.ParentAccountKey
LEFT OUTER JOIN DimAccount lev08 ON lev07.AccountKey = lev08.ParentAccountKey
LEFT OUTER JOIN DimAccount lev09 ON lev08.AccountKey = lev09.ParentAccountKey
LEFT OUTER JOIN DimAccount lev10 ON lev09.AccountKey = lev10.ParentAccountKey
WHERE lev01.ParentAccountKey IS NULL --AND lev01.AccountKey =1 uncomment to get just the balance sheet subtree

Found it at and courtesy of:
http://jpbi.blogspot.ca/2007/05/sql-trick-for-flattening-parent-child.html
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45062 Visits: 39904
codeplexer (1/12/2013)
This is actually a much better way to do it and it's the fastest.


Claims of performance without some form of coded proof are just hearsay. Do you have some proof of that claim?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
codeplexer
codeplexer
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 58
Oops, I should have say "I think this is a better way...." You can find your proof easily if you know copy and paste. But SQL newbie would have know straight forward SQL join or left join is faster than recursive cte and a lot of extra code.... Just saying
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Thanks for this, I did consider this as an option, but found that it wasnt that performant overall due to the structure of the source tables that I was reading from and the nature of the data.

Also (from memory) this doesnt build a ragged hierarchy which is a strict requirement for this project.

However I'm working on a alternative using one of Jeffs recent articles on Hierarchies http://www.sqlservercentral.com/articles/Hierarchy/94040/), but its slow going as i can only do that when I dont have anything else more urgent to do, once I've got the code I'll post it back here.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
codeplexer
codeplexer
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 58
I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows.

Screeshot here
http://tinyurl.com/be6yqxp

or

https://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45062 Visits: 39904
codeplexer (1/13/2013)
Oops, I should have say "I think this is a better way...." You can find your proof easily if you know copy and paste. But SQL newbie would have know straight forward SQL join or left join is faster than recursive cte and a lot of extra code.... Just saying


Tell ya what. YOU do the copy and paste to support your own claim! Build a nice little 100,000 node hierarchy and prove what even a newbie knows... that a developer must not guess like you have. ;-)

Where's the code that compares the runs and where's the code that builds the test data so that others can test?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
codeplexer (1/14/2013)
I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows.

Screeshot here
http://tinyurl.com/be6yqxp

or

https://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit





That may be true but you are using Adventure works DW as the test case, which is a pre-defined dataset with all the nasty work done for you so that you can consume the data in a nice simple way.

I'm using a completely different source object that comes from a 3rd party finance system (Oracle Financials) which has different rules and 3nf schema design.

Also the timings I gave are for building a 3700 row chart of accounts, which means that when you extrapolate out your query by around 50 times your query runs is likely to run in around 1100ms, or lets be generous and apply a 25 times assuming economies of scale its still around 500ms.

In addition to this I dont have the description on the same table as the Hierachy, and have to Join an additional 2 tables for each node in order to get that piece of information and also identify 'dead' paths, so now your query will have at least least twice the number of joins as present (one for each level in the hierarchy) to look remove dead paths, then you also have to look up the descriptions which is at least one more join if you encapsulate the original query in a CTE, or an additional join for each level.

I dont state that this is the optimal solution, but it hits and surpasses the NFR requirement (5 minutes) for this piece of code by at least one order of magnitude.

Given more time and resources (and most importantly desire from those that write my paycheque every month) I would love to rewrite it and get an optimal solution, but at the moment free time during the day is at a premium and the will is lacking from my superiors (if it isnt broken why fix it).

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search