THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Many-to-Many Dimensions: Query Performance Optimization Techniques

More than one year ago I published a paper about design patterns for many-to-many dimension relationships. Since then, I built other models and I got more experience about possible performance optimizations. Unfortunately, I still hadn't time to write about it and this topic requires verbose document and analysis to be reproducible and understandable by everyone. Luckily, now there is a white paper (Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques) downloadable from Microsoft site that explore this world and offers an interesting set of scenarios and possible optimizations, with numbers that explain when, what and how you can expect to optimize a model based on one or more many-to-many dimension relationships.

I already read the document and it is pretty good. Unfortunately, in the real world there are still complex scenarios that cannot be optimized with the techniques used here. I hope that in future versions the SSAS engine will provide some more advanced optimizations for similar cases. One of the first step could be simply automating the optimizations made "by hand" like the Matrix Relationship Optimization shown in the paper. Another would be the simplification for defining efficient aggregations when many-to-many relationships are involved (now you could create a lot of aggregations that are unused when M2M are involved, and sometimes users query a cube only using M2M relationships - you have to tune the aggregations manually).

Published Saturday, December 22, 2007 4:51 PM by Marco Russo (SQLBI)
Filed under: ,



furmangg said:

I agree that would be a great feature to have SSAS internally optimize m2m storage structures to mimic the Matrix Relationship Optimization technique. Good idea.

I just posted a blog post about my suggested step-by-step instructions for building m2m aggs. They're a bit challenging, to say the least, so hopefully that will help:

January 9, 2008 2:57 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement