Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Flattening a Parent Child Hierarchy Expand / Collapse
Author
Message
Posted Wednesday, July 18, 2012 3:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1331849
Posted Wednesday, August 8, 2012 7:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1342339
Posted Thursday, August 9, 2012 1:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1342428
Posted Saturday, January 12, 2013 2:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:38 PM
Points: 5, Visits: 54
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
Post #1406411
Posted Sunday, January 13, 2013 7:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1406466
Posted Sunday, January 13, 2013 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:38 PM
Points: 5, Visits: 54
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
Post #1406568
Posted Monday, January 14, 2013 12:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1406582
Posted Monday, January 14, 2013 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:38 PM
Points: 5, Visits: 54
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


Post #1406976
Posted Monday, January 14, 2013 4:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1406982
Posted Tuesday, January 15, 2013 1:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1407074
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse