Performance considerations for many-to-many dimension relationships

As you learned in Chapter 3, Creating Analysis Services Cubes, many-to-many dimension relationships allow modeling complex business requirements, but this design option should not be abused because it is associated with a performance penalty. When your query involves many-to-many dimension relationships, Analysis Services retrieves data from both measure groups (data measure group and intermediate measure group) as well as the intermediate dimension; subsequently SSAS joins the results of these queries in memory before deriving the result set returned to the requesting application. Since, we cannot materialize many-to-many relationships, we must try to minimize their usage and look for opportunities to tune performance using other methods. The best way to optimize many-to-many relationships is to minimize the size of the intermediary measure group, thereby reducing the footprint required for retrieving intermediary measure...