Attributes and Relationships

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:

Well, it saves me the effort! The original presentation is available to download for PASS members at

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.

CASE statements

One of the first things I noticed when I got hold of AS2005 for the first time, well over a year ago now, was that we now had a CASE statement in MDX. The joy! And yet it’s one of those small improvements which so often gets ignored, so I thought I’d mention it. Here’s an example:

WHEN [Measures].[Internet Sales Amount]> 3780000 THEN "HIGH"
WHEN [Measures].[Internet Sales Amount]> 3700000 THEN "MEDIUM"

SELECT {[Measures].[Internet Sales Amount],MEASURES.MYTEST} ON 0,
[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]

No more nested IIFs! One of the biggest contributions to improved productivity in AS2005 in my opinion…

Project REAL

The Project REAL (a reference implementation of the full SQL2005 BI stack) website is now up:

Nothing much there yet, but definitely worth bookmarking. I heard at PASS Europe that there are some interesting white papers on the way which will be posted there. I also notice that Technet have created a SQL Server TechCenter, which brings together all the SQL Server content on Technet:

Design a BI app and win $50000!

Just spotted the Connected Systems Developer Competition:
…and the fact that one of the categories is ‘Best Application using the Business Intelligence Features of SQL Server 2005 (Reporting Services, Integration Services, Analysis Services)’. If I had the time I’d enter – I bet you could come up with something really cool using all the new functionality we’ve got, especially data mining.

Some of the ‘judging considerations’ are a bit funny: for ‘Creativity/Innovation’ there are 5 points to be awarded for ‘Head Slapper’ (bald men around the world, beware), and another 5 for ‘Solves an Illusive Problem’ (I presume that they meant Elusive, not Illusive, unless they want us to solve problems that don’t actually exist).