I have two queries that appear to return the same result. Can someone explain how combining two dimensions with the comma operator differs from combining the same two dimensions using the crossjoin operator?
For example, two dimensions, account and sales. When I run this query with the cross join operator
select { [Measures].[Sales Count] } on columns,
{ ([Account].[Name].Allmembers *
[Date].[Month Val].[5] : [Date].[Month Val].
) } on rows
from warehouse
it returns the same results as this query when using the comma operator to create the set:
select { [Measures].[Sales Count] } on columns,
{ ([Account].[Name].Allmembers ,
[Date].[Month Val].[5] : [Date].[Month Val].
) } on rows
As crossjoin states, in example 1 above, every account is combined with every month value. When using the comma operator, I see the exact same results. Are these functionally equivalent? Any reason to use one method over the other?
Thanks....
AS2005 can be very forgiving - perhaps too forgiving - with MDX syntax...
First of all, despite appearances, there is no comma operator: the comma is used as a delimiter within a set and a tuple definition. Crossjoin you're ok with. What you're doing in your queries is producing a set of tuples containing every month crossjoined with every account and for the sake of clarity this is best written as an explicit crossjoin, either using the asterisk operator or the crossjoin function, something like this:
select { [Measures].[Sales Count] } on columns,
[Account].[Name].Allmembers * {[Date].[Month Val].[5] : [Date].[Month Val].
on rows
from warehouse
In your first query you're putting the above crossjoin operation inside a set of parentheses (), which denote a tuple - and which AS is ignoring because you're producing a set of tuples anyway. In the second query because you've put a set definition rather than a member as the second item in your tuple definition AS must be assuming that you want to do a crossjoin and is again returning the set of tuples you're after, but this behaviour is new to me and from a maintenance point of view should probably be avoided.
Chris
|||Hi Chris,
I'm wondering whether the comma syntax in the 2nd example (which I also recall being mentioned in Teo Lachev's As 2005 book) is technically a subcube expression, as defined for the SCOPE script statement or a Cell Calculation:
http://msdn2.microsoft.com/en-us/library/ms145989.aspx
>>
Subcube_Expression ::= (Auxiliary_Subcube [, Auxiliary_Subcube,...n])
Auxiliary_Subcube ::=
Limited_Set
| Root([dimension_name])
| Leaves([dimension_name])
>>
Mosha had mentioned in his blog that a subcube could get converted to a set, under certain circumstances (though unsupported):
http://sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx
>>
How to work with Dimension Leaves
While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.
>>
|||Hmmm, possibly... In which case, if Mosha says it's unsupported, it's definitely to be avoided.
Chris
|||Agreed...crossjoin works fine. Thanks for the feedback.