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:

- Though a Crossjoin
*can*be used to construct a tuple, we only*need*it to construct a set. - 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.

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.

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

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?