“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.”
A very intresting whitepaper about the solutions based on many-to-many dimension relationships with Analysis Services 2005 (2008). Read the entire whitepaper at sqlcat.