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:
- Checkbox: Unchecking a checkbox will cancel the SQL query for that m2m relationship.
- Status: Shows the status of the SQL query that checks the compression stats of that m2m relationship.
- Data Measure Group: The name of the data measure group
- Intermediate Measure Group: The name of the intermediate measure group in the m2m relationship. This is the bridge fact table.
- Original: The original, uncompressed rowcount of the intermediate measure group.
- Compressed: The rowcount of the intermediate measure group after you perform the matrix relationship optimization technique.
- Reduction %: (Original-Compressed)/Original
- Dimension: The size of the new compressed intermediate dimension, also known as a signature dimension.
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.