Chris Webb's BI Blog

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

Using Dynamically-Generated Sets in the MDX Script

with 3 comments

Even more fun: I’ve just found that you can use the technique for dynamically generating sets within a cube’s MDX Script. For example, add the following set declarations to the end of the Adventure Works cube’s MDX Script:

create hidden set myyears as [Date].[Calendar].[Calendar Year].members;

create hidden set mytest as
generate(myyears,
strtoset("
intersect({},
{topcount(descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Date]), 10,[Measures].[Internet Sales Amount])} as
[Top days for "
+ myyears.current.item(0).item(0).name + "])"
));

Then connect to the cube with your favourite client tool and, bingo! you see all the sets available to use in your queries:

namedsets

The only thing to watch out for is that, for some reason, you need to declare the set you pass in to the first parameter of the Generate function separately as I have above, rather than inline.

I can see this as being quite useful – if you need to create a large number of very similar sets on your cube it’s much more manageable than declaring each set individually since you only need to write the set expression once.

Written by Chris Webb

June 26, 2007 at 10:32 pm

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. I agree Chris. This is useful.

    Thomas

    June 27, 2007 at 9:39 am

  2. Chris,  I would have liked to do something similar with calculated members (dynamically generate).  Still didn\’t find any technique to do that.  Do you happen to know?

    Harsh

    June 30, 2007 at 12:55 am

  3. Hi Harsh,
     
    The only thing you can do (and I\’m not even sure this is possible in AS2005) is use the CreatePropertySet function as I do here:

    http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!133.entry

    But I agree, it would be great to have a supported way of doing this; it\’s not possible in a stored procedure.

    Chris

    July 1, 2007 at 9:34 pm


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

%d bloggers like this: