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

How to report on historical movements within a changing hierarchy Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 9:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
Yes, except that my question is
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?


I will create a model of this environment and use it to make concrete headway...I think at this point I need to provide some ddl for there to be a concrete way to help me! But, thanks for your ideas
Post #1434325
Posted Saturday, March 23, 2013 7:35 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:25 AM
Points: 35,959, Visits: 30,253
polkadot (3/22/2013)
Yes, except that my question is
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?


I will create a model of this environment and use it to make concrete headway...I think at this point I need to provide some ddl for there to be a concrete way to help me! But, thanks for your ideas


No. I don't believe anyone has been able to solve the problem without SCDs other than making a copy of the whole hierarchy. Of course, that's not a bad idea either. Except for the MLM world and huge parts houses, even fairly large hierarchies don't take up much room.

I'll also say that I believe you're making a performance mistake by using recursion rather than storing a nice, tight Nested Sets hierarchy. See the article I pointed you to as to why.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1434646
Posted Sunday, March 24, 2013 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
OK, will do. I've got a few more points of reference then I did when you first directed me to that article. So, I'm heading in the direction to ingest and make sense of:

"I'd like to recommend a different approach to hierarchies. Please see the following 2 articles...
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/"

Post #1434675
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse