Brief Description
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.
Overview
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).
About CarlRabeler
Carl is a Program Manager on the SQL Server Customer Advisory Team, Best Practices group. Carl works closely with internal and external customers to develop and validate SQL Server business intelligent best practices for working with large scale BI solutions. Carl has worked with Microsoft SQL Server and Business Intelligence for over 10 years as a consultant, mentor, writer, speaker and trainer before joining Microsoft in 2006. Carl has written several SQL Server and Business Intelligence books and numerous technical white papers, has presented at many SQL Server and BI conferences, and has provided mentoring and consulting services for numerous companies around the world.
|
sql, server, best practices, whitepapers, analysis services, data mining, olap, datawarehouse, datawarehousing, availability, clustering, capacity, collation, data types, data warehouse, database, design, index, mirroring, optimization, partitions, performance, precision, processing, querying, scalability, security, reporting services, integration services
|
|
