December 2007 - Posts

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).



Cross-posted from SQLBlog! - http://www.sqlblog.com

posted Saturday, December 22, 2007 4:51 PM by marcorusso with 0 Comments

Free Introducing LINQ book

Usually I write in this blog about Business Intelligence without too much consideration for "traditional" access to SQL Server. However, in a parallel universe I also wrote a book about LINQ and now that we are in full Christmas mode, I hope you may be interested that you can download the Introducing Microsoft LINQ book for free!!

I wrote this book with Paolo based on Beta 1. Some contents are now outdated, but general concepts and 80% of the syntax is still valid. And, remember, now it's free, so you shouldn't claim too much! We are currently working on a more advanced and complete book about LINQ (Programming Microsoft LINQ), which is expected to be available by mid-May 2008.

Have a good read, a Merry Christmas and a Happy New Year!



Cross-posted from SQLBlog! - http://www.sqlblog.com

posted Saturday, December 22, 2007 11:54 AM by marcorusso with 0 Comments

Real Report Builder 2008 improvements for Analysis Services

Brian Welcker (who is leaving SSRS team - good luck Brian!) has announced a long explanation of the Report Builder 2008 positioning.

For me (and all of you who live in the multidimensional business...) the most important part of the post is this simple sentence:

[...] However, for folks building reports against Analysis Services cubes, auto-generating a model is no longer required.

This is really a good news who will drive Report Builder adoption in SSAS shops. I still haven't tested Report Builder 2008 very much, but this news will help me to find the time to do it.



Cross-posted from SQLBlog! - http://www.sqlblog.com

posted Wednesday, December 19, 2007 12:38 AM by marcorusso with 0 Comments