polkadot (3/22/2013)
Yes, except that my question isHas 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
Change is inevitable... Change for the better is not.