Including Child Members Multiple Places in a Parent-Child Hierarchy

When designing your SQL Server 2005 (or 2008) Analysis Services solution, you may be faced with the design requirement to display multiple hierarchies in a parent-child dimension. A common implementation that we have seen is the use of custom rollups to accomplish this task. However, we have seen performance issues with this implementation when implemented at scale. For example, in one site that we worked with recently, certain MDX queries took almost a full minute to return results when executed against cold cache.

Marco Russo describes a different implementation in his Many-to-many revolution article—this implementation uses the many-to-many dimension relationship feature in SQL Server 2005 Analysis Services. Richard Tkachuk describes a third implementation in his Duplicate Members in Analysis Services 2005 blog. We implemented a variation of the many-to-many dimension design at the customer site to determine if its performance was significantly better. We discovered that its performance is dramatically better, particularly when the data requested is neither cached in Analysis Services nor cached in the file system.

Note: There are numerous approaches to addressing this problem space. This paper addresses only one particular approach. Other approaches could be superior based on various criteria.