Compress Many to Many Relationships (Matrix Solution) C# Application
The SQL Customer Advisory Team released a few items on Codeplex yesterday including a little ditty I wrote:
Compress Many-to-Many C# Utility - This project creates a utility that enables you to easily implement the many-to-many compression technique discussed in the Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques best practices white paper. You can download this white paper from the SQL Server Best Practices web site at: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
The symptoms for which this application may be useful is where there are many to many relationships in your cube resulting in significant if not downright terrible query performance. Such many to many relationships usually involve a very large intermediate dimension which is most likely a degenerate dimension. Meaning, the intermediate dimension is as large as the fact table. This can result in an intermediate measure group which will probably have more rows than the fact table.
There are a few known limitations that I do intend to address eventually (some are harder than others):
· Inability to apply this technique to cascading many to many relationships.
· The underlying data source must be SQL Server.
· Inability to handle underlying tables from data sources other than the primary data source.
· Inability to apply necessary schema changes to “intermediate fact tables” that are not real tables; ie views of the relational data source or named queries.
· The key of the “Many” dimension must be of type int.
Even if this application of no use to you (either because you're not experiencing the problem or the known limitations are a roadblock), there are some crazy examples of AMO coding you may enjoy in this C# application.
