Crossjoin is a function which, for obvious reasons, is dear to my heart. It’s been at least a year since I last found a feature in AS2005 MDX that I wasn’t aware of, but the other day I found out that the Crossjoin function can now take more than two sets as parameters, much in the same way that NonEmptyCrossjoin can. Here’s an example:
select
measures.[internet sales amount] on 0,
crossjoin(
[Date].[Day Name].[Day Name].members,
[Date].[Day of Week].[Day of Week].members,
[Product].[Color].[Color].members
)
on 1
from [Adventure Works]
measures.[internet sales amount] on 0,
crossjoin(
[Date].[Day Name].[Day Name].members,
[Date].[Day of Week].[Day of Week].members,
[Product].[Color].[Color].members
)
on 1
from [Adventure Works]
Not exactly revolutionary (and anyway I use the * operator instead nowadays) but fun to note.
Funny you post this, just as I was finishing my new blog about Crossjoin. Well, let\’s test if you really know this function and whether you discovered all the features of AS – here is a little quiz question: Do you know that there are 4 different ways in AS to compose a Crossjoin (you are obviously familiar with 2 ways, but what about other 2). I will hold posting my blog until you give an answer. 🙂
Oooh, I love a challenge! Here\’s #3, putting the sets inside a tuple:
selectmeasures.[internet sales amount] on 0,([Date].[Day Name].[Day Name].members,[Date].[Day of Week].[Day of Week].members,[Product].[Color].[Color].members)on 1from [Adventure Works]
…although I hate it, I\’ve seen too many people confused by doing this. As for #4, well I could have sworn there was an alternative function which was in the OLEDB for OLAP spec which did exactly the same thing as Crossjoin; I tried Cross() but that doesn\’t work, but I\’m sure it\’s something like that. Go on, put me out of my misery…
I could have come out with other answers like using NonEmptyCrossjoin or using Generate() but that seemed a bit like cheating.
You #3 is a good answer, but I don\’t know why you called it a "tuple". It is a set, not a tuple.
You cannot simulate CrossJoin using NonEmptyCrossJoin – so it doesn\’t count. Using Generate would require to put something as generator, and you are back to the original question – how can you alias CrossJoin function. As soon as I have more time to finish my blog you will know about #4.