Useless Use of Crossjoin

Even fans of MDX like me will agree that it isn’t the most aesthetically pleasing language, so it’s painful to see it made more convoluted than strictly necessary. This post is a grumble about a piece of redundant syntax that appears far too often, and has really begun to grate…

A quick definition of terms. A tuple represents an intersection or slice of a cube, specified by a list of members. The list of members does not need to include all dimensions, but any dimension included must be represented by only one member.

In MDX, parentheses are used to denote a tuple. Sometimes this is optional, but I prefer to include them. The following are examples of valid tuples (from Sample.Basic):

([Jan])
([Jan], [Sales])
([Jan], [Sales], [New York])

A set is a collection of tuples sharing the same dimensionality. In MDX, braces are used to denote a set. Again, this is sometimes optional, but recommended for clarity. The following are examples of valid sets:

{([Jan]), ([Feb])}
{([Jan], [Sales]), ([Feb], [Sales])}

A Crossjoin is used to generate a set which is the “cross-product” of sets from more than one dimension. For example, if we want to reference all Sales and Expenses from January and February, we can use a Crossjoin like this:

Crossjoin({[Jan], [Feb]}, {[Sales], [Expenses]})

It produces the following set:

{([Jan], [Sales]), ([Feb], [Sales]), ([Jan], [Expenses]), ([Feb], [Expenses])}

But here’s the deal:

  1. Though a Crossjoin can be used to construct a tuple, we only need it to construct a set.
  2. We don’t need to nest Crossjoins for every dimension in the set, only those from which we are choosing more than one member. All other dimensions can be represented by a single tuple.

For example, if we want to reference January Sales (from Sample.Basic), do this…

([Jan], [Sales])

…not this…

Crossjoin({[Jan]}, {[Sales]})

And if, we want to reference January and February Sales for Actuals only, do this…

Crossjoin({[Jan], [Feb]}, ([Sales], [Actuals]))

…not this…

Crossjoin({[Jan], [Feb]}, {Crossjoin({[Sales]}, {[Actuals]})})

I hope these examples show the principle, though they probably don’t convey the full horror of encountering a dozen nested Crossjoins used to construct what is actually a simple tuple.

5 thoughts on “Useless Use of Crossjoin

  1. Hey,

    Brilliant post – and definitely something I’m guilty of…but this has got to be wrong surely….

    Crossjoin({[Jan], [Feb]}, {[Sales], [Expenses}
    Should it be:
    Crossjoin({[Jan], [Feb]}, ([Sales], [Expenses]))

    Or am I misreading it?
    p

    • Thank you, Pete! Good catch – it is missing the trailing ).

      It should be (and I will change to):

      Crossjoin({[Jan], [Feb]}, {[Sales], [Expenses]})

      [Sales] and [Expenses] have to be in braces not parentheses because they comprise a set not a tuple.

  2. Pingback: The EPM Week In Review: Week Ending February 27, 2016 – Hyperion EPM

  3. Tim,

    Does it also mean that you could never have an asymmetric report such as:

    R2016 R2016 R2017
    Nov Dec Jan

    This is something I tried to build with the following but without great success!
    SELECT CROSSJOIN({[R2016],([M09]:[M12])},{[R2017],([M01]:[M12])}) ON COLUMNS …

    • Hi Sebastien,

      Thanks for commenting…

      You can do that *without* a Crossjoin, assuming that you are willing to hard-code the three tuples:

      SELECT {([R2016], [Nov]), ([R2016], [Dec]), ([R2017], [Jan])} ON COLUMNS…

      But I suspect you want an automatic rolling periods thing, like @XRANGE does in BSO calcs?

Leave a Reply

Your email address will not be published. Required fields are marked *