Miscellaneous Analysis Services Topics - SQL Server Analysis Services 2012 Cube Development - page 428

Performance considerations for
many-to-many dimension relationships
As you learned in Chapter 3 , Creating Analysis Services Cubes , many-to-many di-
mension 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 re-
questing 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 group's data into memory, as well as the time it
takes to join results to the primary measure group's data. Additionally, you can build
suitable partitions and aggregations on data and intermediate measure groups to fur-
ther reduce the size of data sets that will be joined during the query execution time.
Microsoft has published a white paper documenting the recommended practices for
tuning many-to-many query performance. You can download the white paper from ht-
tp://www.microsoft.com/en-us/download/details.aspx?id=137 .