The whitepaper entitled Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques outlines several techniques to optimize performance of m2m dimensions. One technique is the matrix relationship optimization technique. Analyzing the data in a m2m relationship to determine whether it can be compressed significantly requires building a complex SQL query. This BI Developer Extensions feature automates this process and returns a report showing how much each m2m relationship can be compressed.
In the Dimension Usage tab of the cube designer, click the button in the toolbar:
Then the following window pops up. BI Developer Extensions begins running one SQL query at a time in the background.
This window has the following columns:
Note: The SQL query is based upon the DSV table tied to the measures in the intermediate measure group. Any partition SQL queries are ignored.
Note: If an error occurs, then the row is highlighted red. Mousing over that row shows the error message in the tooltip.
Note: For additional information on building an incremental SSIS package to implement the matrix relationship optimization technique, see this blog post.
Note: The code for this BI Developer Extensions feature was written independent of the CompressManyToMany by Eugene A. Asahara (which he described further here. This tool could be complementary to BI Developer Extensions tool for m2m relationship compression.