Hi Jeff,

Thanks for your time.

Answer: Hierarchichal IDs and Hierarchical Paths. Today, the idea of using hierarchyID was tossed, because it was advised one can't join to the hierarchyID table across databases residing on seperate instances (something to do with the binary code column corresponding to hierarchy path perishing when moved across instances and CLR).

So, we are examining use of recursive queries through which we will also be able to generate enumerated paths.

We don't like the idea of using slowly changing dimension and wondering if a better solution.

Here's the scenario: Company manages facilities requests for a bunch of buildings. The hierarchy looks like this: Portfolio-> Region -> Functional Area ->Building (the hierarchy is actually more complex than this). Frequently buildings get reassigned to different functional areas so the parent child relationship between functional area and buildings changes. It is desired that we report on historical trends of facilities requests, for that building, regardless of functional area.

The trouble is that when a subtree is moved, and the table containing enumerated paths is refreshed (via stored procedure to reorder data), the original paths are recalculated to reflect the new parent/child relationships. The slowly changing dimension offered here as a way to persist records of old relationships has been been considered an undesireable solution because of the complexity of queries that would be required to retrieve associated metrics with those entities at each level.

Has anyone else had a similar problem and solved it with something other than slowly changing dimension?