Declaring Named Sets Inline

I mentioned in my last entry on the ‘expensive topcount’ that I’d originally tried to solve the problem by declaring a set inside the axis definition, and after a chat about this with my colleague Jon I thought it might be worth going into more detail on what I was trying to do and why it failed. What I was talking about was the fact that MDX allows you to declare a named set anywhere a set definition appears, not just in a WITH SET or CREATE SET statement, and this ability is very important when it comes to optimising queries which use complex set operations.

Part of the reason why I’ve not blogged about this before is because two short articles I wrote on this subject a while ago are now publicly available on the ‘BI Best Practices’ blog:
http://blogs.msdn.com/bi_systems/articles/162840.aspx
covering the performance benefits, and
http://blogs.msdn.com/bi_systems/articles/162850.aspx
covering their use in solving the problem of currentmembers from different contexts. Between them they cover the basics of the subject; in the first you can see how you can use them to optimise queries very similar to the ones in the ‘expensive topcount’ problem. But why couldn’t I use named sets to solve that particular problem, then? The answer is because in that case I was trying to reuse a set between an axis definition and a calculated member and this just isn’t possible. The following query is what my first, unsuccessful attempt at the problem looked like:

WITH
SET MYDATES AS ‘{[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5]}’
MEMBER PRODUCT.OTHERS AS ‘([Product].[All Products], TIME.CURRENTMEMBER) – SUM(STRTOSET("MYTOPCOUNT"), (MEASURES.[UNIT SALES], TIME.CURRENTMEMBER))’
MEMBER Measures.Test as ‘SetToStr(STRTOSET("MyTopCount"))’
SELECT {MEASURES.MEMBERS, Test} ON 0,
GENERATE(
MYDATES,
CROSSJOIN(
{TIME.CURRENTMEMBER},
{ [Product].[All Products], TOPCOUNT([Product].[PRODUCT NAME].MEMBERS, 3, (MEASURES.[UNIT SALES])) as MyTopCount, PRODUCT.OTHERS}
) )
ON 1
FROM SALES

You can see that I’m declaring the set MyTopCount in the rows axis definition and then attempting to refer to it in the Product.Others calculated member. Once I’d got round the fact that the query wouldn’t initially run because MyTopCount hadn’t been declared when the calculated member was parsed (by wrapping it in a StrToSet), I found that by the time Product.Others got calculated the contents of MyTopCount had been lost. Similarly, if you turn the query round the other way as follows:

WITH
SET MYDATES AS ‘{[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5]}’
MEMBER PRODUCT.OTHERS AS ‘([Product].[All Products], TIME.CURRENTMEMBER) – SUM(TOPCOUNT([Product].[PRODUCT NAME].MEMBERS, 3, (MEASURES.[UNIT SALES])) as MyTopCount, (MEASURES.[UNIT SALES], TIME.CURRENTMEMBER))’
MEMBER Measures.Test as ‘SetToStr(MyTopCount)’
SELECT {MEASURES.MEMBERS, Test} ON 0,
GENERATE(
MYDATES,
CROSSJOIN(
{TIME.CURRENTMEMBER},
{ [Product].[All Products], MyTopCount, PRODUCT.OTHERS}
) )
ON 1
FROM SALES

…you can see that while the values for Product.Others are correct, and you can see the contents of MyTopCount in Measures.Test, the members that make up MyTopCount don’t appear on rows. The reason why neither of these queries work is fairly obvious when you think about it: AS has to fully evaluate the sets that appear on both axes before the values of the cells in the cellset can be calculated, so we have two separate iterations not one, and therefore no opportunity to reuse the sets (incidentally, if you’re interested in finding out more about the steps that AS goes through when running a query take a look at MDX Solutions chapter 5, ‘Cell Context and Resolution Order in Queries’).