I was just thinking today that I ought to cannibalise the presentation I gave at PASS Europe for a blog posting, and now I find that Steve McHugh has beaten me to it:
http://stevemchugh.blogspot.com/2005/05/sql-pass-2005-new-cube-design-features.html
http://stevemchugh.blogspot.com/2005/06/new-cube-design-features-part-2-more.html
Well, it saves me the effort! The original presentation is available to download for PASS members at
http://www.sqlpass.org/
But just to reiterate the most important point – build those relationships between your attribute hierarchies, because they’re the only way that AS can make use of aggregations at ‘lower’ levels. Not having them there could impact query performance in a big way. And one more thing – when Dave Wickert covered some of the same subject matter in one of his presentations he made a point I didn’t know about: that when the aggregation design wizard runs it favours long, complete chains of relationships. I guess this is because there’s more chance that a given aggregation can be reused by queries higher up the relationship chain.
If it’s not too late it would be great if the dev team could insert a message in the dimension wizard reminding people to build these relationships, and maybe even make it a bit more obvious where to do this in the Edit Dimension tab (it’s a bit hidden away if you ask me). I’m sure that otherwise people will forget to do it and wonder why their queries are crawling.
Ssshhhh. That\’s what people need consultancies for 😉
Often overlooked, but relationships between attributes help performance of calculations probably even more then they help performance of regular aggregations. The reasons are too complex to explain them here, but usually I can speed up calculations in the cube by 40% by simply defining proper relationships.
I\’m intrigued! Perhaps this is something for your blog, Mosha?