Many-to-Many Matrix Compression
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 BIDS Helper 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. BIDS Helper 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
Note: The code for this BIDS Helper feature was written independent of the
CompressManyToMany by Eugene A. Asahara (which he described further
here. This tool could be complementary to BIDS Helper's tool for m2m relationship compression.
- 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.