How would you rate an OLAP variable depth dimension?
In most cases, each level of the hierarchy in a dimension represents a different concept (for example, country-> region-> city, year-> month-> day), and this is easy enough to use in a cube.
I am interested in variable depth hierarchies, which tend to be made up of instances of the same concept, that is, nodes in a computer network where "depth" is a hop from a specific viewpoint.
I am thinking of creating an arbitrary list of synthetic levels (level 1 = 1 hp away, level 2 = 2 jumps, etc.), but then I probably have to push short branches down to the lowest level, which doesn't seem like a very elegant solution ...
I am looking for comments, ideas, suggestions and best practices.
a source to share
Sounds like a parent-child hierarchy implemented as a single dimension table in which each record has a key to bind itself to its parent object.
For MS Analysis Services see http://technet.microsoft.com/en-us/library/ms174846.aspx for more details
a source to share