How would you go about an OLAP dimension with a variable depth hierarchy?

Question

In most cases, each level of a hierarchy within a dimension represents different concepts (i.e. country->region->city, year->month->day) and this is simple enough to use in a cube.

What I'm interested in are variable depth hierarchies which tend to consist of instances of the same concept, i.e. nodes in a computer network where the "depth" is the hop count from a specific point of observation.

I'm considering setting up an arbitrary list of synthetic levels (level 1 = 1 hop away, level 2 = 2 hops away etc.), but then I probably have to pad the shorter branches all the way down to the lowest levels, which doesn't seem like a very elegant solution.

I'm looking for comments, ideas, suggestions and best practices.

Answer 1

Sounds like a parent-child hierarchy, implemented as a single dimension table where each record has a self-referencing key to its parent.

In MS Analysis Services, more details are here http://technet.microsoft.com/en-us/library/ms174846.aspx