MDX Series
This is post number four in a six post series. A brief overview of the series:
- Post #1: Connect to Sample.Basic and execute two basic queries
- Post #2: Introduce functions using a single dimension
- Post #3: Introduce multiple dimensions and CrossJoin
- Post #4: Define sets and tuples to add a deeper understanding of how MDX works
- Post #5: Discuss how to avoid almost all frustration using MDX in Smart View
- Post #6: Provide a strategy for refreshable MDX using a macro
Note: This post defines sets and tuples in order to get at *what* MDX is really doing and *why* the syntax works the way it does in different situations. You actually can work in MDX without understanding these–and I feel that I did for a long time, and I still got a lot of value from from MDX–so if you’re not interested or are having a hard time with this section, that’s OK and you still can use MDX. But if you come to understand the terms here, it’ll make the whole topic of MDX easier to understand.
I had never heard the word tuple before I encountered it while learning MDX.
It turns out though that “tuple” is a standard concept in mathematics. (As a result, you may have run into the word before–or possibly, like me, you hadn’t.) The basic definition for mathematics is that a tuple is “a finite, ordered list”. Finite just means there is some number of elements; and order means that the order or the elements matters.
In Essbase terms, a tuple is a list of members from different dimensions; it also shares with the standard definition that (in some way) the number and order of members matter–but we’ll get to that in a bit.
First, let’s take a look at a simple tuple. The POV that we have been using so far illustrates a tuple in its most basic form:
This is a single tuple, comprised of three members, in three dimensions.
Tuple and Sets
In an Essbase tuple, it is possible to have as few or as many dimensions as you want, but only a single member from each dimension can be present. So this is valid:
But this is invalid:
The latter is invalid because Market and East are from the same dimension. When we need multiple members from one dimension, what we need is a one-dimensional set, which was covered in the second blog post. This is a one-dimensional set:
Maybe what we really wanted was the combination of Product with both Market and East. In that case, in MDX terms, we are talking about a multi-dimensional set. In the last blog post, we used the CrossJoin function to create multi-dimensional sets, but it can also be achieved by joining two tuples together, using the {curly brackets} that we have seen elsewhere when we work with sets. The tuples are separated by a comma.
It would be possible to list additional combinations of members in the Product dimension and members in the Market dimension, if we wanted.
In practice, we do not often build lists of tuples into sets like this, unless there are very few of them. The CrossJoin function that we covered last time is the more typical approach Still, what the CrossJoin is doing behind-the-scenes is simply expanding its inputs into a list of tuples like this one.
Wait, So What Were Those Single-Dimension Sets?
Ultimately, the real definition of a set in MDX is “a collection of tuples”. When we talk about single-dimensional sets, we’re really just using a shorthand for saying “a set that is comprised of tuples that have only one dimension”. A tuple *can* have multiple dimensions, but the smallest possible tuple in Essbase has just one dimension, and it is perfectly valid.
For convenience, when this happens, we are able to omit the tuple (parentheses), but it would be equally valid to say:
Or:
The second example is including syntax that is valid, but which is optional.
Order Matters, But Not Yet
Above we said “it is possible to have as few or as many dimensions as you want in a tuple.” That is true in the abstract, but for a Smart View query, we actually need at least one dimension on the rows and one dimension on the columns. As long as that condition is met, though, it is fine to have any of these combinations (which are only examples, and not exhaustive):
- 2 dimensions on rows; 2 dimensions on columns; 1 dimension on POV
- 1 dimension on rows; 1 dimensions on columns; 3 dimensions on POV
- 1 dimensions on rows; 4 dimensions on columns
- 3 dimensions on rows; 2 dimensions on columns
Assume that three dimensions are on the columns. Our first example again:
It would be equally valid to change the order:
OK, so what does it mean then to say that order matters in tuples? Next section.
Tuple Number and Order
When you are creating a tuple, you can start by including as many dimensions as you need. (As long as at least one dimension is on the rows and one on the columns.) You can also put these dimensions in whatever order you want them to appear on the Smart View retrieve. *But* once you have selected a number of dimensions and an order, you must keep to that number and order.
In many ways, this makes intuitive sense. On a normal Smart View retrieve, you cannot do this:
Even though this is an intelligible query in one way (both B5 and C5 are fully-qualified, with every member defined), it changes the order of dimensions, and Essbase made the correct (in my opinion) design decision to not allow this. It would offer more opportunities for confusion than any benefit it would provide.
By the same principle, you cannot do this in MDX:
Of course, MDX could choose to solve this problem for you, by implicitly re-ordering the dimensions to the order you used first. But the benefit would be very small, and the potential for confusion would be similar to the Smart View example. Therefore, the only valid ways to do this are like this:
Or this:
You might ask yourself, “When would this ever even come up?” In my experience, the answer is very rarely, but it does happen occasionally. When I have seen it, it is because I was joining two sets, one that was very simple, and one that was complicated. In that situation, I created the simple set first, putting the dimensions in whatever order I thought of them, but then with the complicated set, I thought through the order more carefully based on the functions I needed to use and how I wanted the data to appear. Then I had to go back and change the simple set to match the order of the complicated one.
Up Next
The next post will talk about the good and the bad of using MDX in Smart View, and discuss strategies to avoid the problems that can come up due to certain Smart View behaviors.
