In this third installment of my Intro to MDX Decathlon, we cover the hugely important Tuple.
Before we get into the details, I want to make a quick note about pronunciation. There is some controversy over how to properly say the “tup” in Tuple. In one camp, there are the folks that say it must rhyme with “cup.” The other camp insists it must rhyme with “loop.” I poked around a bit online concerning this topic and found roughly equal numbers of people in each camp declaring with absolute certainty that the pronunciation they prefer is the right one. I, personally, rhyme with “cup,” but make no claim of correctness. It just feels right to me and was the first pronunciation I heard. I don’t think it really matters. In a world in which we have television shows that start with “The Real Housewives of” ANYTHING, we have much bigger problems than how to pronounce this word.
When it comes to Tuples, a key concept to cover first is that of a cube Cell.
Cell: The logical intersection of a member of the Measures dimension with a member of each attribute hierarchy in the cube
Now that we have defined a cell, let’s look at one from my first post in this series, Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10). Figure 1 shows four dimensions, each named with a letter, A thru D.
Figure 1
The circle in the center, highlighted in blue and a bit larger than the others, can be considered a cell. It is the intersection of all of the dimensions in the figure. We can refer to this cell according to its coordinates; the member of each dimension present at that location. You can see this in Figure 2.
Figure 2
We can use the coordinates of this cell as its name. This is just how it works in the cube as well. We reference a particular cell in the cube by specifying the members that intersect at that location. The coordinate name for that location is a Tuple. That brings us to the definition below.
Tuple: The unique coordinates of a cell within the cube space
One key thing to keep in mind about a tuple is that it cannot include more than one member of the same hierarchy. That makes perfect sense. Think of a Calendar Year attribute hierarchy. Can there be any date that is simultaneously in both 2010 and 2011? (Hint: No.) In addition, you can only reference each dimension once in the tuple.
To specify a tuple in MDX, you separate dimension references with commas and surround the entire structure with parentheses. So, a tuple of the cell in Figure 2 would look like this:
(
[A].[3]
, [B].[5]
, [C].[7]
, [D].[4]
)
NOTE: The particular style of having the comma at the front of each line, the line breaks, and the indentation in this series is just my preference. I find it the most organized and readable. You need not follow my exact formatting. As long as you have the commas and parentheses in the proper location relative to the dimension references, you can format it how you like.
Now, let’s look at a tuple from my Borg cube.
Query 1
SELECT
(
[Series].[Series Name].[Star Trek: The Original Series]
, [Season].[Season Name].[All]
, [Date].[Date Hierarchy].[All]
, [Measures].[Episode Count]
) ON COLUMNS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 1
Notice in the result above that all of the labels are returned on the Columns axis since that is the only one specified in the query.
Since you have been following this series with such rapt joy, you will have noticed that the Result above, 80, has the same value as one from the previous post in this series, Intro To MDX Decathlon–The Basic MDX Query (2/10).
That previous post included this query.
Query 2
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 2
You will notice that both queries return a cell with the value of 80. It is actually the same cell that is just reached using a slightly different syntax.
What we see in Query 2 is an example of what is called a Partial Tuple. A partial tuple is a feature of Analysis Services that allows you to specify the parts of the tuple you wish to while letting SSAS take care of the rest. Note that when you are using a Partial Tuple and specifying only one hierarchy, as in Query 2, you need not use parentheses to surround the Tuple.
When we just specify just [Series].[Series Name].[Star Trek: The Original Series] on the Rows axis in Query 2, SSAS follows the decision tree in Figure 3 to determine how to properly identify the cell.
Figure 3
When I chose not to specify either the Season or the Date dimension in the partial tuple on the Rows axis in Query 2, SSAS looked to see if either of those had a Default Member. My Season Name attribute hierarchy has a Default Member specified in my Borg cube: The [All] member. So that member is used. My Date Hierarchy does not have a Default Member. But it does an [All] member. So that member is used. If neither a Default Member nor an [All] Member are present, then the first member found (according to the hierarchy sort order defined in the cube) is used.
The benefit of Partial Tuples is that it allows for cleaner code and less typing in many cases. This allows the following two queries return the exact same cell.
Query 3 (Full Tuple)
SELECT
(
[Series].[Series Name].[Star Trek: The Original Series]
, [Season].[Season Name].[All]
, [Date].[Date Hierarchy].[All]
, [Measures].[Episode Count]
) ON COLUMNS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 3 (Full Tuple)
Query 4 (Partial Tuple)
SELECT
(
[Series].[Series Name].[Star Trek: The Original Series]
, [Measures].[Episode Count]
) ON COLUMNS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 4 (Partial Tuple)
In this post we introduced the concept of the Tuple, including defining a cube Cell. We also showed the power of Partial Tuples. In the next post, we really start having some fun with Sets.
Mark Vaillancourt 
2 replies »