Tuples Sets & Cells

Objective: To understand Tuple & Sets

In this lesson you will be introduced to the three most important topics in MDX

  1. Tuples
  2. Sets
  3. Cells

select
[Measures].[Internet Sales Amount] on columns,
[Date].[Calendar Year].&[2012] on rows
from [Adventure Works]

Lets make a slight change to our query.

select
[Measures].[Internet Sales Amount] on columns,
([Date].[Calendar Year].&[2012]) — notice the parentheses
on rows
from [Adventure Works]

We have made a slight change by wrapping the date with () parentheses. The result however remains same. So exactly what did we achieve?

Tuples

In the above query, the date was used as a tuple.
A tuple is a combination of members from one or more dimensions. A tuple may contain a single member like “[Date].[Calendar Year].&[2012]” or may contain may contain multiple members. In case if it has multiple members, you must enclose it within parentheses. Individual members should be separated with “,”. A dimension hierarchy within a tuple cannot be repeated.
Lets try another query

select
[Measures].[Internet Sales Amount] on columns,
([Date].[Calendar Year].&[2012],[Date].[Month of Year].[Month of Year].members)
on rows
from [Adventure Works]

In the above query, we used the tuple “([Date].[Calendar Year].&[2012],[Date].[Month of Year].[Month of Year].members)” to return the “Internet Sales Amount” breakup for all months of 2012.Till now our tuple members have being from the same dimension.
Lets try another query.

select
[Measures].[Internet Sales Amount] on columns,
([Date].[Calendar Year].&[2012],[Date].[Month of Year].[Month of Year].members,[Product].[Category].&[1])
on rows
from [Adventure Works]

In the above query we enhanced our tuple to be multidimensional. It now returns “Internet Sales Amount” for Year 2012 broken by months, for Bikes only.
Lets try another query.

select
[Measures].[Internet Sales Amount] on columns,
([Product].[Category].[Category]members,[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
on rows
from [Adventure Works]

In the above query we returned all categories, plus we re-arranged the order of the members. This resulted in the grouping being changed.
Notice the position of a member within a tuple dictates the grouping.
Now if we want to restrict our result to just Bikes and Clothing.
Lets try another query.

select
[Measures].[Internet Sales Amount] on columns,
([Product].[Category].&[1],[Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
on rows
from [Adventure Works]

Oops, remember we just mentioned that a member of a tuple cannot repeated. Lets try to make a tuple of tuples.
Lets try another query

select
[Measures].[Internet Sales Amount] on columns,
(
([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year]),
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year])
)
on rows
from [Adventure Works]

Oops again, a collection of tuples is not a tuple. We have Sets for this.

Sets

A set is a collection of tuples. A set may have one or more than one tuples or even be empty. In case of multiple tuples, a “,”is used to separate them. A set may contain the same tuple more than once.Syntactically a set is enclosed within “{}”. The ordering of tuples in the set dictates the result.
Time to fix our above query.

select
[Measures].[Internet Sales Amount] on columns,
{
([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members),
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
}
on rows
from [Adventure Works]

Notice we enclosed our tuples within “{}” and we were good to go.
Lets try another query.

select
[Measures].[Internet Sales Amount] on columns,
{
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members),
([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
}
on rows
from [Adventure Works]

In the above query we simply switched the positions of the tuples(clothing and bikes) and the result followed the order. However notice that the Months did not have any effect on their order. Since we are not explicitly specifying the order of months hence they are returned in their default order.
Lets try another query.

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States]),
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members),
([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
}
on rows
from [Adventure Works]

In the above query, on the columns axis, we provided a duplicate tuple within a set. This resulted in a copy of the column in the result.
Lets try another query

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members),
([Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members)
}
on rows
from [Adventure Works]

Oops, we have a problem! From our second tuple, we removed
“[Product].[Category].&[1]” and our query broke.

Hierarchality

Hierarchality is one of the two fundamental requirements for a set to be valid. It dictates that all tuples within a set must consists of the same hierarchies. In the last query, we removed
“[Product].[Category].&[1]” from our second tuple. This violated Hierarchality.
Lets fix this and try another query

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members),
([Date].[Calendar Year].&[2013],[Date].[Month of Year].[Month of Year].members,[Product].[Category].&[1])
}
on rows
from [Adventure Works]

Oops, we have a problem again! Although we have included
“[Product].[Category].&[1]” but the query is still not good.We just changed the position of “[Product].[Category].&[1]” and we get an exception.

Dimensionality

Dimensionality, is the second fundamental requirement for the set to be valid. It states that not only must the tuples have common hierarchies in them, tuples should also have them in the same order. In the above query, although we did include
“[Product].[Category].&[1]” but it was not consistent with its position in the earlier tuple.

select
[Measures].[Internet Sales Amount] on columns,
{[Date].[Calendar Year].&[2012],[Date].[Calendar Year].&[2013]}
on rows
from
[Adventure Works]

Up till now, in the above queries we have returned all the months, lets say we just want to return the first three months.
Based on this requirement, lets try another query.

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].&[1]),
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].&[2]),
([Product].[Category].&[3],[Date].[Calendar Year].&[2013],[Date].[Month of Year].&[3])
}
on rows
from [Adventure Works]

In the above query, we used our understanding of Dimensionality and Hierarchality to construct the set. But it is still cumbersome. Lets suppose we want the result for the first six months, does that mean we copy the tuple six times and change the value for months.
Lets try another query.

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
(
[Product].[Category].&[3],[Date].[Calendar Year].&[2013],
{[Date].[Month of Year].&[1],[Date].[Month of Year].&[2],[Date].[Month of Year].&[3]}
)
}
on rows
from [Adventure Works]

Did we just broke the basic rule for tuples, which states that a dimension hierarchy cannot be repeated within a tuple?
No we didn’t break the basic rule. Remember this notation from the last section. Multiple members of a hierarchy are separated by “,” and enclosed in “{}”.
Lets try another query

select
{ ([Measures].[Internet Sales Amount],[Customer].[Country].&[United States]) }on columns,
{
–first tuple
(
[Product].[Category].&[3],
{[Date].[Calendar Year].&[2013],[Date].[Calendar Year].&[2014]},
{[Date].[Month of Year].&[1],[Date].[Month of Year].&[2],[Date].[Month of Year].&[3]}
),
–Second tuple
(
[Product].[Category].&[4],
{[Date].[Calendar Year].&[2012],[Date].[Calendar Year].&[2013]},
{[Date].[Month of Year].&[5],[Date].[Month of Year].&[6],[Date].[Month of Year].&[7]}
)
}
on rows
from [Adventure Works]

In the above query, we had two tuples.
The first tuple showed us “Internet Sales Amount” for “Clothing” for “2013” and “2014” for first three months.
The Second tuple showed us “Internet Sales Amount ” for “Accessories” for “2012” and “2013” for fourth to six months.
This was all done within the principles of sets and tuples. If you consider for a moment, the “.members” that we have being using all along has being doing this too. If substitutes each value within the hierarchy, effectively creating a copy of the original tuple for each available value in hierarchy. So when we write “([Date].[Calendar Year].&[2012],[Date].[Month of Year].[Month of Year].members)” it basically creates twelve copies of this tuple, one for each month.
Lets try another query

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
(
[Product].[Category].&[3],
{[Date].[Calendar Year].&[2013]},
{[Date].[Month of Year].&[1]:[Date].[Month of Year].&[6]}
)
}
on rows
from [Adventure Works]

In the above query we used the range “:” operator from the last section.
{[Date].[Month of Year].&[1]:[Date].[Month of Year].&[6]}.
Lets re-execute the last query and double click one of result cells.

So what exactly does this window tells us. Up-till now we have been compiling tuples and set, which are distributed on rows and columns.The result is a two dimensional result set. For instance in the result above we have one column with six rows. Each of this row and column combination is called a cell. Cells have a number of properties, by default VALUE and FORMATTED_VALUE are returned. In the sub-window above we see some properties being returned. For instance the value property in the above window shows 2915.25 which is the aggregation of the current measure. Similarly FORMATTED_VALUE represents the string representation of Value property. To return additional properties we use “CELL PROPERTIES” keyword.
Lets try another query

select
{
([Measures].[Internet Sales Amount],[Customer].[Country].&[United States])
}on columns,
{
(
[Product].[Category].&[3],
{[Date].[Calendar Year].&[2013]},
{[Date].[Month of Year].&[1]:[Date].[Month of Year].&[6]}
)
}
on rows
from [Adventure Works]
CELL PROPERTIES VALUE

Notice the values in result above are not formatted, Since we queried VALUE property of the result.

Summary

In this topic we explored the following concepts.

  1. A tuple is a combination of members from one or more dimensions.
  2. A Set is a collection of tuples.
  3. A Set of tuples must observe hierarchility and dimensionality .
  4. Hierarchility states that all tuples within the set should contain the same hierarchies.
  5. Dimensionality states that all tuples within the set should contain the hierarchies in the same order.
  6. The position of a member within a tuple dictates, its position in grouping.
  7. The position of a tuple within the set determines its ordering.