Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Dynamic named sets in AS2008 – not as fun as you might think

leave a comment »

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:

WITH
MEMBER [Measures].[Employee Rank] AS RANK([Employee].[Employee].CurrentMember, OrderedEmployees)
SELECT
[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.

Written by Chris Webb

January 14, 2008 at 9:59 pm

Posted in MDX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers

%d bloggers like this: