Chris Webb's BI Blog

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

Introduction to MDX for PowerPivot Users, Part 2: Basic Sets

with 10 comments

In the first post in this series I looked at how the objects in a PowerPivot model mapped onto MDX objects; now, I’m going to start looking at some practical uses for MDX with Excel’s named sets functionality. Once you’ve created a PivotTable on top of your PowerPivot model, you can find this functionality on the PivotTable Tools/Options tab on the ribbon by clicking on the Fields, Items and Sets button:

image

What it does is allow you fine control over what appears on the rows and columns axes of your PivotTable. Now you don’t need to know any MDX to use this functionality to do fairly basic things like delete rows or to change the order, but with MDX you can do some pretty amazing things!

Consider the following simple PowerPivot model built from the Adventure Works DW database:

image

From this you can create a PivotTable with Calendar Years on rows and a measure that sums up the SalesAmount column:

image

With this PivotTable created, you can now go to the ribbon and you’ll see that there is the option to create a named set based on the selection you’ve made on rows:

image

Clicking on the Create Set Based on Row Items menu item opens the New Set dialog:

image

It’s here you can add, delete, copy and move rows; if you click the Edit MDX button then you can see the MDX representation of the selection you’ve made:

image

Here’s the actual MDX from this screenshot:

{([DimDate].[CalendarYear].&[2005]),([DimDate].[CalendarYear].&[2006]),([DimDate].[CalendarYear].&[2007]),([DimDate].[CalendarYear].&[2008]),([DimDate].[CalendarYear].[All])}

This is an MDX set expression: a set is just an ordered list of things, and there’s no equivalent object in DAX or SQL. Sets are written as comma delimited lists of members or tuples (we’ll come to what a tuple is later!) surrounded by curly brackets or braces, ie {}. This example is a set of Calendar Years in the following order: 2005, 2006, 2007, 2008 and a member called “All” which returns the Grand Total value. Sets can be given names – and so become named sets – and this means that anywhere MDX expects a set expression you can use the name of the set you’ve defined to return that set.

If, at this point, you click OK, a new named set called “Set1” will be created and that will be used as the selection on the rows axis of the PivotTable; since you haven’t changed any of the MDX, though, the PivotTable itself will look identical. The fact that the set is now used to control what’s on rows can be seen in the field list:

image

You can now go back and edit the set by clicking on the Manage Sets menu option under Fields, Items and Sets, selecting Set1 from the list of named sets and clicking Edit.

Rearranging and deleting items in the set expression in the Modify Set dialog that then appears (which looks the same as the New Set dialog above) changes the contents of the named set and so changes what is selected on the rows axis of the PivotTable. You can find the unique names of members and other objects by dragging them from the Fields, Items and Sets pane on the left hand side into the Set Definition text box on the right hand side.

It’s important, if you want the order of items in your set to be maintained (and you almost always do when writing your own MDX), that you also uncheck the Automatically Order and Remove Duplicates From the Set option at the bottom of the dialog:

image

For example, using the following set expression with automatic ordering turned on:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

Gives you the set of years 2008, 2005 and 2007 in the order 2005, 2007 and 2008 and results in a PivotTable looking like this:

image

With automatic ordering turned off you get the set of years 2008, 2005, 2007 in that order, which is of course the order they are listed in the set:

image

MDX is a language plagued with brackets and commas and it’s very easy to make syntax errors when writing it. To check that your MDX is syntactically correct you can click the Test MDX button in the Modify Set dialog.

Now let’s talk about tuples. A tuple is another MDX concept that doesn’t have an equivalent in DAX or SQL; you can think of it as a kind of co-ordinate. Tuples are written as comma delimited lists of members surrounded by round brackets, ie (). As I said before, you can have sets of members or sets of tuples (well, strictly speaking all sets are sets of tuples but I won’t go there…) and the two sets you’ve seen above are sets of tuples. So the set:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

…contains three tuples, and this:
([DimDate].[CalendarYear].&[2008])

…is a single tuple containing one member, the member for the year 2008 which has the unique name:
[DimDate].[CalendarYear].&[2008]

The set:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

…is a set of three members – notice that the round brackets have disappeared – but will return the same three rows in a PivotTable as the previous set. In these two examples, each tuple or member (ie each item) in the set becomes a single row in the PivotTable.

Tuples can have more than one member in them though. Consider the following set:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

It still contains three tuples, but this time each tuple is composed of a year and a day name. Each tuple still becomes a row in the PivotTable (something which is best observed if you choose Show in Tabular Form on the PivotTable Design tab of the ribbon), but now each row has two levels of nesting, a year followed by a day name:

image

You can have as many members as you like in a tuple, so for example you could add some quarters too:

{([DimDate].[CalendarYear].&[2008],
[DimDate].[EnglishDayNameOfWeek].&[Monday],
[DimDate].[CalendarQuarter].&[1]),
([DimDate].[CalendarYear].&[2005],
[DimDate].[EnglishDayNameOfWeek].&[Friday],
[DimDate].[CalendarQuarter].&[4]),
([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])}

image

I’ll finish off this post by mentioning the two important rules that you have to remember when defining sets and tuples, namely:

  • Each item in a set has to be the same type of thing. So, if you have a set of members, each member has to come from the same hierarchy; if you have a set of tuples, each tuple has to contain the same number of members and each tuple has to contain members from the same hierarchy in the same position.
  • Each item in a tuple has to be a member from a different hierarchy.

For example:

This is a valid set containing three members, because each member comes from the CalendarYear hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

This is not a valid set however, because it consists of two members from the CalendarYear hierarchy and one member from the CalendarQuarter hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1]}

This is a valid tuple because it contains three members from three different hierarchies:

([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])

This is not a valid tuple because it contains two members from the CalendarYear hierarchy:

([DimDate].[CalendarYear].&[2007],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1])

This is a valid set of tuples because each of the three tuples consists of a member from the CalendarYear hierarchy followed by a member from the EnglishDayNameOfWeek hierarchy:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

Whereas this is not a valid set of tuples, even though each tuple on its own is valid, because the final tuple in the set contains a member from the EnglishDayNameOfWeek hierarchy followed by a member from the CalendarYear hierarchy, rather than a CalendarYear followed by an EnglishDayName of week:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[EnglishDayNameOfWeek].&[Tuesday], [DimDate].[CalendarYear].&[2007])}

This is not a valid set of tuples either, because the final tuple (which again is valid in its own right) contains a CalendarYear followed by a CalendarQuarter:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[CalendarQuarter].&[1])}

Phew! I know the theory behind MDX can be very heavy going, but I promise you it’s important to learn it in order to be able to get the most out of the language. Next time, in part 3, I’ll look at some functions that returns sets and see how they can be used to construct more complex selections.

Written by Chris Webb

October 11, 2012 at 10:51 pm

Posted in MDX, PowerPivot

10 Responses

Subscribe to comments with RSS.

  1. Good article! Thanks !

    ravi

    October 16, 2012 at 7:17 am

  2. [...] Part 2 I’ll introduce the concept of sets and how they can be used. Share this:FacebookTwitterLike [...]

  3. [...] the previous post in this series I looked at how MDX set expressions could be used inside Excel to give you total [...]

  4. [...] describe this technique I’m going to use same PowerPivot model I describe here, which is based on the AdventureWorks DW sample database. First of all, create a new PivotTable [...]

  5. [...] a simple example. Consider a simple PivotTable (using my example model, described here) with FullDateAlternateKey on rows and the Sum of SalesAmount measure on [...]

  6. Chris, this has been really helpful thanks.

    Is it possible to add values in a set? so in the example above there’s a set with 3 lines 2008, 2005 and 2007, would it be possible to have another set with two lines, one containing 2008 and one containing 2005 plus 2007.

    Thanks again

    Mike Malone

    December 6, 2012 at 5:25 pm

    • Hi Mike,

      The answer is yes and no: no, you can’t add items in a set, but yes there is a way to do what you want. What you will need to do is to create a new calculated member on the year hierarchy that returns the sum of 2005 and 2007, and put that in your set.

      Sorry it’s been so long since the last post in this series… I’ve been a bit distracted! I’ll write some new posts soon, and cover this specific scenario in one of them.

      Chris

      Chris Webb

      December 10, 2012 at 3:37 pm

      • Chris

        Thanks for the reply, been busy on other areas of work lately so not followed this up.

        I’ll have a look at what you’ve suggested and check out the next posts.

        Thanks Again

        Mike

        January 8, 2013 at 1:18 pm

  7. [...] axis or a columns axis or a rows axis, and the way you define what appears on an axis is using a set, an object we’ve seen a lot of in the last few posts in this series. Each MDX query also needs a [...]

  8. Hi Chris. Great series. You know what sets remind me of? The ‘Custom Views’ functionality in Excel. Among other things, that functionality allowed you to name, save and later recall autofilter settings. Unfortunately it’s incompatible with Excel Tables (aka ListObjects) and so has effectively been depreciated.

    This is similar…want to save your PivotTable settings and later on recall them with the push of a button? Create a set.

    Awesome.

    jeffrey Weir

    April 13, 2014 at 11:17 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: