MDX Basic Concepts

26 October 2012 · 6 comments · 17851 views

Loading the player...

If this message stays on, check that you have the latest Flash Player installed (http://get.adobe.com/flashplayer) and that your browser supports JavaScript.

Unique Names, Tuples & Sets

Having a good understanding of the basic theory of MDX is essential if you want to be able to write your own calculations, and queries, for SQL Server Analysis Services and even Power Pivot in Excel. In this 29-minute video, part of our MDX online course, world-renowned SSAS expert Chris Webb introduces three basic concepts: unique names, tuples, and sets. 

All objects in MDX, from cubes to dimensions to hierarchies, levels and members, have unique names (1:08). 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 (1:57). 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 (13:35). 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 (15:02), and then a more complex one (17:28). 

A set (19:45) 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 (21:04). For a set of tuples (24:06), 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 MembershipChris 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, and by other leading SQL Server experts, through Technitrain.

Comments

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 .!!!
Thanks,
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:

EXISTS(
[Period].[Calendar].Members,
{ [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:
2013
Nov
30
Dec
 31

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

2013
Nov
30
Dec
 31

My googling skills haven’t found anything regarding this.

Thanks,
 Andri

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

Cheers,
 Andri