MDX Series Pt 3: The Second Dimension is the Hardest

MDX Series

This is post number three 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

So far, our rows and columns have used only one dimension, and our POV had the rest. But most Hyperion Smart View retrieves have multiple dimensions on the rows or the columns, or both. How can we make that happen in MDX?

There are two main methods to have multiple dimensions on an axis (such as rows or columns):

• Tuples, which are inherently multi-dimensional
• Sets that are linked by a CrossJoin function

Tuples First

Tuple may be an unfamiliar word. We’ll give a more precise definition of a tuple in the next post. Meanwhile, the most important thing to understand about a tuple is that it lets us define multiple dimensions at once, but it limits us by only allowing us to use one member per dimension.

The POV that we have been using is an example of a tuple:

As a reminder, our first blog post used this as our first query:

Here’s an example where we’ve swapped the columns and the POV:

Note that ON COLUMNS and ON ROWS always need a set as input, which is why the {curly brackets} are still needed when we put the tuple in the ON COLUMNS section. This works fine because (as we’ll discuss in the next blog post), a tuple is also a special kind of set.

This gives the output:

Tuples are great when you are defining something that has very few members in the query. Usually the Scenario, Version and Year dimensions are good candidates for including in a tuple. Tuples can also be combined with sets, which is something we’ll look at the end of this post.

The CrossJoin function will be a part of almost every real query you ever write. What this function allows is for you to start with two sets, and join them together in a way that they are effectively multiplied, so that all the resulting combinations get shown. (The technical term for this multiplication of sets is a “Cartesian product”.)

In Oracle/Hyperion Smart View terms, you make this kind of query all the time in ad hoc mode. It happens any time that you have two dimensions on the rows, and you zoom in on both of them. For example, zooming in on Market and Measures in a normal Hyperion Smart View retrieve gives this result:

How can we get the same result with MDX? We use CrossJoin for our ON ROWS section:

The full query looks like this:

Sets of Sets

It is also valid to use a single member inside the set, instead of a function. Here’s an example, and the resulting Hyperion Smart View retrieve:

Why would you want to do this? Sometimes you are trying to display things in a very particular way, and using the functions alone does not achieve the right result. In that case, very often you can get what you need by defining multiple sets and joining them together, and that is perfectly valid.

For example, let’s say that the most important value in the report is the Market, Profit amount. Let’s also say that at the Market member, we do not care about Inventory or Ratios and do not want to display them. How can we achieve that? Here’s one way:

Note that syntax-wise, we needed to add {curly brackets} to the ON ROWS section, and that the two CrossJoins are linked by a comma; the arrows point these features out.

Results:

Tuples and Sets Together At Last

The CrossJoin function always requires a set as a parameter. This is similar to ON ROWS and ON COLUMNS mentioned above. Once again, we can use a single tuple because it is really just the simplest kind of a set.

Example:

Technically, if a function is used, like CHILDREN, then the {curly brackets} can be left off because MDX automatically interprets the result of that function as a set. In practice, I find that it is often best at first to include the {curly brackets} anyway, since it is easier to remember to always include them than to remember when they are and are not required.

Brick by Brick

The CrossJoin function always takes only two parameters. What if we need to join three or more dimensions and each one uses a function?

Ultimately, each CrossJoin returns a set. And the CrossJoin function takes two sets as an input. This means that CrossJoins can be nested, using one CrossJoin as a input to another. You can nest CrossJoins as many times as needed, to get the desired result.

We’ll close with a simple nested example:

Results:

Up Next

The next post will dive into a deeper understanding of tuples and sets.