Archive for January 2008
I got all excited when, last summer, Mosha blogged about dynamic named sets in Katmai: just when it looked like there weren’t going to be any cool new features to play with in AS2008, here was a juicy new MDX thing. However I didn’t really play with them properly until a bit later when I came to prepare my presentation for last autumn’s SQLBits conference, and that’s when I realised that they weren’t quite as cool or useful as I had thought they were going to be.
The key thing I hadn’t picked up on from my initial reading of Mosha’s blog entry was that they are evaluated once per query. While that means they are still useful in some scenarios, one of the key examples that Mosha describes in his blog entry is a bit misleading and that’s the ranking example. We all know, or at least we should all know, that the key to optimising rank calculations is to declare a named set which gets ordered just once and which is referenced inside the calculated member that returns the rank. Mosha accurately points out that the big drawback to this approach is that "It can only be used when the user can write his own MDX query" but then says that dynamic named sets are a solution – and my point here is that, in my opinion, they aren’t really.
The problem can be seen if you change Mosha’s example query slightly by adding Ship Date Calendar Years to the Columns axis:
MEMBER [Measures].[Employee Rank] AS RANK([Employee].[Employee].CurrentMember, OrderedEmployees)
[Ship Date].[Calendar].[Calendar Year].MEMBERS
[Measures].[Employee Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]
If you run this query, you’ll see that instead of seeing different ranks for different years, you get the same ranks repeated across every year – which is what you’d expect, because remember our dynamic named set is only evaluated once per query. I’m not saying this is a bug or something that should be fixed, however, because if the set was not evaluated once per query and evaluated every time it was called you’d be back where you started with poor performance; it’s just that dynamic sets aren’t very useful in this particular scenario. If the user can’t write their own MDX then it follows that they’re going to be in the situation where both the dynamic set and the rank calculated member are defined on the server and they’ll be querying with a tool like Proclarity or Excel, so you’d expect them to be able to generate whatever query they wanted and have it work as they would expect, but as you can see it isn’t going to.
Incidentally, if you’re playing around with this there is a bug in the November CTP that Mosha told me about: if you have a calculated member that references a dynamic named set then it should appear in the MDX Script before the dynamic named set definition. If the calculated member definition comes after the named set definition you seem to get some problems with caching and strange results are returned.
One of my new year’s resolutions last year was to learn PerformancePoint, which I’ll be honest I’ve completely failed to do. I mean, I’ve played around with it, went to an airlift and seen more presentations on it than I can shake a stick at but I’ve not done anything serious with it yet; perhaps that’s because only a few projects are actually using it at the moment and in my line of work, I only get called in at the end of a project when things have gone wrong ;-)
Anyway, to save my blushes the first time I need to work with it, Nick Barclay sent me a copy of one of the books he co-wrote with Adrian Downes on the subject, "The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007". I liked the book he and Adrian wrote on Business Scorecard Manager and a lot of the things that were good about that book can be repeated for this one too: it’s clear, it’s concise (like all the Rational Guide series), it’s well-written and it tells you just about everything you need to know. I guess no-one can claim to be a complete PerformancePoint guru simply because it’s a new product and best practices only emerge after a year or so of use in a lot of different projects, but Nick and Adrian have clearly been using the betas a lot and have already got some good practical tips to offer (such as the odd RTM bug). All in all, if you’re about to embark on your first PerformancePoint project you’ll probably want this book by your side; oh, and if you want a second opinion on it, Teo Lachev liked it too.
You can buy the book from Amazon UK here. There’s also a companion book on the planning side of PerformancePoint too coming, but I’m not sure when – Nick, Adrian, perhaps you can comment?