Greetings Fellow DV-ers!
I need to implement a hierarchy that would be a recursive parent-child situation in an ODS situation. I haven't been able to find any examples for a parent-child of unknown depth, they have all been typical 2-level structures like invoice & line number.
I would like to avoid using recursion because of the typical depth and performance risks. I'm drawn to the idea of something link the hierarchical table in MS SQL ( https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15 ) where the "hierarchyID" is essentially a delimited string of all the levels above the current record.
The ultimate goal being that I can generate a list of all the subordinate levels and branches that exist under the top level by passing a LIKE function in the where clause; no recursion required. "where hierarchyID like '/level1%';
I'm having difficulty imagining the table(s) required to pull this off in a data vault kind of way beyond stringing the related hub hashes together, which seems difficult to parse and even more difficult to represent at the info mart layer. Any recommended approaches or best-practices of this situation greatly appreciated!