Project Botticelli

MDX Basic Concepts Get Free Access Purchase this course

26 October 2012 · 6 comments · 40584 views

Unique Names, Tuples & Sets

MDX Basic Concepts: Cubes and Tuples

MDX has the undeserved reputation of being a difficult language. Analysis Services expert Chris Webb makes it easy, introducing the three key MDX concepts: unique names, tuples, and sets, in this 29-minute, free video. Log-in or get a free account to watch it!

All objects in MDX, from cubes to dimensions to hierarchies, levels and members, have unique names. These are the names you should use when writing MDX to uniquely identify an object in a cube. You can determine the unique name of an object by clicking on it, in the metadata pane of an MDX query window in SQL Server Management Studio, and then dragging it into the query pane. Not using a unique name may seem like a good idea, because it makes your MDX code less verbose, but if you do you run the risk of Analysis Services returning a different object with the same name, which will result in errors, or unexpected results.

A tuple is a way of referring to a single value within a cube. If you think of a cube as a gigantic multidimensional array, a tuple is a co-ordinate to one cell within that array. Tuples are written as comma-delimited lists of members, from one or more different hierarchies, and are surrounded by round brackets: (). Tuples are most often used to return values in MDX calculations. You can see two examples of tuples in the demo, starting with a simpler one, and then a more complex one. 

A set is an ordered list of members, or tuples, surrounded by curly brackets: {}. Each item in a set has to be the same type of a thing: for a set of members, each member has to be from the same hierarchy—although the members can be from different levels of the same hierarchy. Chris shows a demo of a set using members of the most commonly used hierarchy: the date hierarchy. For 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 order. Sets are used to define what appears on the rows and columns of an MDX query, and are also used in calculations, for example when aggregating a large number of values.

Make sure to continue learning by watching Chris’s next video MDX Queries—free—or dive into the full course, using your Full Access Subscription. Chris Webb also provides consultancy on MDX, Analysis Services, PowerPivot and DAX through Crossjoin Consulting, and he runs public training courses in the UK, taught by him.

Log in or register for free to access this content.

Purchase This Course or Full Access Subscription

Single Course


Access this course for its lifetime*.
Subscription Best Value


Access all content on this site for 1 year.
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.


Anil · 1 November 2012

Hi Chris,
Nice to know about this site and that was a simple n great session about MDX from a MDX Master I must say :). Hope to watch more session from you .!!!
Anil Maharjan

anuradhak@navan... · 19 December 2013

Great presentation on MDX basics , it cleared many of the doubts i had in my MDX queries , now i can undestand the errros SSAS thows at me when i break those basic rules.

caryleedavis · 29 January 2014

Great Chris. So clear and concise. Thank you.

AndriP · 12 February 2014

Great video Chris.
The MDX is getting a bit clearer.

I wanted to ask you one question regarding sets.
I have a set to filter last 12 months of a calendar hierarchy:

{ [CalendarTodaySet].ITEM(0).ITEM(0).Parent.lag(12):
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.lag(1) }

When I use the set in Excel the values are just flat in one level:

Is is it possible to have the set return values with levels like if I was using the calendar hierarchy?


My googling skills haven’t found anything regarding this.


Chris Webb · 12 February 2014

Andri, do you mean you want the values indented? Which version of Excel are you using? Excel 2013 does what you want automatically, I think. But probably the best thing to do here is not to use a set. Instead, I would create a new boolean column on your dimension table that is True when the date is in the last twelve months and False otherwise; then create a new attribute on your Date dimension from this column. If you then filter by the True member on that attribute in your PivotTable you should get the filtering you want and the formatting should be preserved.

AndriP · 14 February 2014

Yes they were supposed to be indented the second time :)
Currently I'm testing with Excel 2010, I haven't tested with 2013.
The cube will be used with an SAP BO universe for reporting and there the set is returning the hierarchy correctly and I can choose what levels to display. I was hoping for consistency when using with Excel, but maybe this is just different between client tools and Excel 2013 has this solved.

I have around 12 sets for different types of dates and date ranges so I'm not sure about adding 12 boolean columns to the date dimension although that would allow me to use it as a filter (damn Excel not allowing sets as a filter).


Online Courses