Chris Webb's BI Blog

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

Declaring Named Sets Inline

with one comment

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’).  

Written by Chris Webb

April 5, 2005 at 5:06 pm

Posted in MDX

One Response

Subscribe to comments with RSS.

  1. Hi Chris,This isn\’t quite equivalent, but you can use VisualTotals() to "craft"Calculated Members which incoporate each instance of the InlineNamed Set. But each such member needs to be named distinctly:>>WITH SET MYDATES AS \’{[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5]}\’SELECT {MEASURES.MEMBERS} ON 0,GENERATE(MYDATES,CROSSJOIN({TIME.CURRENTMEMBER},{ [Product].[All Products],TOPCOUNT([Product].[PRODUCT NAME].MEMBERS, 3, (MEASURES.[UNIT SALES])) as MyTopCount,Head(VisualTotals({[Product].[All Products], [Product].[PRODUCT NAME].MEMBERS – MyTopCount},"*.OTHERS-" + TIME.CURRENTMEMBER.Name))}) )ON 1FROM SALES>> – Deepak

    Deepak

    April 6, 2005 at 3:56 am


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,299 other followers

%d bloggers like this: