MDX - Why Cross join between measures do not work?

Question

In MDX, we can CROSS JOIN two members, a measure and a member but not two measures. Why is this so? What does it imply?

SELECT
   [Measures].[xyz] * [DimTable1].[SomeHierarchy].[Level] on 0,
   [DimTable2].[SomeOtherHierarchy].&[Value] on 1 
FROM [MyCube] 
// WORKS

SELECT 
   [Measures].[xyz]  on 0,
   [DimTable2].[SomeOtherHierarchy].&[Value] * [DimTable1].[SomeHierarchy].[Level] on 1 
FROM [MyCube] 
// OF COURSE IT WORKS

SELECT
    [Measures].[xyz] * [Measures].[ABC] on 0,
    [DimTable1].[SomeHierarchy].&[Value] on 1 
FROM [MyCube] 
// DOES NOT WORK!!

Why do you want to crossjoin two measures? What end result would you expect? For dimensions, you get the possible combinations of the dimension attributes, but why would you want to do this with measures? — mmarie, Oct 13 '14 at 15:20
I am trying to understand what is happening in the background when two measures are cross-joined. I very well understand that crossjoin implies various possible conditions. But my question is what happens when measures are cross-joined and why is it erroring out. Its not something I want to do, but something I want to understand. — SouravA, Oct 13 '14 at 15:50
@Sourav_Agasti a cross-join just does a cartesian product of all the members of one set with all the members from another set...suppose you end up with a massive set of 2 member tuples. Most of these tuples are in the empty space of the cube i.e. there are not results for them. The autexists property of a cross-join means that these empty tuples are discarded. — whytheq, Oct 13 '14 at 18:46
@Sourav_Agasti your third query has not values in the body of the table that it is returning ?! It has numbers down column A , then numbers down column B, then column c is [DimTable1].[SomeHierarchy].&[Value] but what values do you want to display in column C ?! — whytheq, Oct 14 '14 at 07:52

score 1 · Accepted Answer · answered Oct 13 '14 at 23:22

1

I believe you forgot:

SELECT [dd].[hh].[mm1] * [dd].[hh].[mm2] on 0, [DimTable1].[SomeHierarchy].&[Value] on 1 FROM [MyCube]

did not work neither. [Measures] is not different than [dd] in my example. In MDX you cannot define a tuple with _ several members _ of the _ same hierarchy _. Have a look to this gentle introduction explaining the main concepts.

answered Oct 13 '14 at 23:22

Marc Polizzi

9,275
3
36
61

Thanks, I understand the concept of tuple. The error message which appears when 2 members of the same hierarchy are cross-joined(**The xyz hierarchy is used more than once in the Crossjoin function.**) is pretty different from that while putting them in the same tuple(**The 'xyz' hierarchy appears more than once in the tuple.**). But amazingly, when I cross join two measures, the error message is way different. **The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used.** – SouravA Oct 14 '14 at 03:48
Sorry do not know the details of SSAS; possibly an implementation detail. – Marc Polizzi Oct 14 '14 at 07:57

whytheq · Answer 2 · 2014-10-14T07:56:45.603

0

EDIT
Your third query, that does not work, looks like this:

enter image description here

The yellow area is empty so it is understandable that it is not happy.

EDIT
Following is an analogy using Excel pivot tables which use OLAP technology

If you put a crossjoin of measures A and B on rows you get something like this:

enter image description here

Then if we add a very small level (with 4 members) onto columns we get the following:

enter image description here

So what will go into the main body of this table?
A count is possible and probably is, in MDX, if you create a custom measure (don't have a server to test this statement on). Excel will default to a count but the result is pretty pointless?

enter image description here

edited Oct 14 '14 at 07:56

answered Oct 13 '14 at 18:43

whytheq

34,466
65
172
267

Your analogy is not correct I feel. When we don't explicitly complete any tuple(holding all the hierarchies), it picks the default member out of those hierarchy. Had we simply avoided the **CROSS JOIN**, it would have taken the [default member](http://www.bp-msbi.com/2011/01/default-measure-in-ssas-cubes/) (generally **ALL**) from all the hierarchies, which would return a maximum possible value of the measure. Here in this case, I would have expected something similar, but it errors out. – SouravA Oct 14 '14 at 03:59

MDX - Why Cross join between measures do not work?

2 Answers2