Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).

http://www.microsoft.com/downloads/details.aspx?FamilyID=3494E712-C90B-4A4E-AD45-01009

About the Co-Author

Erik Veerman, a SQL Server MVP and mentor for Solid Quality™ Mentors, is focusing on training, mentoring, and architecting solutions on the SQL Server business intelligence (BI) platform. Erik has designed dozens of BI solutions across a broad business spectrum, including marketing, retail, real estate, finance, telecom, supply chain, and IT. He led the ETL architecture and design for the first production implementation of SQL Server Integration Services (SSIS) and helped develop the SSIS ETL standards and best practices for Microsoft’s Project REAL SQL Server 2005 reference initiative. Erik is co-author of Expert SQL Server 2005 Integration Services (Wrox) and Professional SQL Server 2005 Integration Services (Wrox) and is lead author for Microsoft Press’s newly released MCTS Self-Paced Training Kit (Exam 70-445): Microsoft SQL Server 2005 Business Intelligence Implementation and Maintenance.