CODEWITHSUNDEEP
mdx
Yugandhar
Yugandhar

Reputation: 87

MDX: What is the difference of these both queries

MDX: What is the difference of these queries, I'm using adventure works database.

SELECT 
{[Measures].[Internet Sales Amount]} ON COLUMNS,
NON EMPTY ([Product].[Category].[Category] ,
          [Date].[Calendar Year].[Calendar Year])ON ROWS
FROM [Adventure Works]

GO

SELECT 
{[Measures].[Internet Sales Amount]} ON COLUMNS,
NON EMPTY ([Product].[Category].[Category] *
          [Date].[Calendar Year].[Calendar Year])ON ROWS
FROM [Adventure Works]

Upvotes: 0

Views: 31

Answers (1)

whytheq
whytheq

Reputation: 35557

Those two scripts are the same.

This is effectively putting a set of tuples on the rows - you've definied the tuple explicitly using the braces (...)

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,NON EMPTY 
    (  //<<start of tuple
      [Product].[Category].[Category]
     ,[Date].[Calendar Year].[Calendar Year]
    ) ON ROWS
FROM [Adventure Works];

The second script is a cross join via the star operator *:

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,NON EMPTY 
    [Product].[Category].[Category] * [Date].[Calendar Year].[Calendar Year] ON ROWS
FROM [Adventure Works];

When you action a crossjoin it returns a set of tuples - so you end up with the same cell set.

You could write crossjoin instead of using the star - although the star is the most used notation in modern mdx scripts:

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,NON EMPTY 
    CrossJoin
    (
      [Product].[Category].[Category]
     ,[Date].[Calendar Year].[Calendar Year]
    ) ON ROWS
FROM [Adventure Works];

Upvotes: 1

Related Questions