CODEWITHSUNDEEP
ssasmdxquery-performanceadventureworks
elvainch
elvainch

Reputation: 1385

NONEMPTY and CROSSJOIN performance and order in MDX

I was wondering which of the following two queries is more performant?

Query 1:

SELECT NONEMPTY(CROSSJOIN({[Product].[Category].children}, 
                           {[Scenario].[Scenario].members} 
                          )
                 ) ON COLUMNS
FROM [Analysis Services Tutorial]

Query 2:

SELECT CROSSJOIN(NONEMPTY({[Product].[Category].children}), 
                  NONEMPTY({[Scenario].[Scenario].members})
                 ) ON COLUMNS 
FROM [Analysis Services Tutorial]

I would say query 2 is more performant/optimized because first you take out all the unnecessary members and then crossjoin them. The first query you crossjoin everything and then take out the nulls. That would be my guess but I want somebody who can clear me up.

Edit 1 In response of comments of an answer

Lets say I add a measure as a second parameter, so it does not go to the "default measure". How could second query return values with null? I am specifying to crossjoin between nonempty members. And I just really dont see how the can return different results no matter the dimensions involved. To me they seemed pretty equivalent. What am I not seeing?

Query 1:

SELECT NONEMPTY(CROSSJOIN({[Product].[Category].children}, 
                           {[Scenario].[Scenario].members} 
                          ), [Total Internet Sales]
                 ) ON COLUMNS
FROM [Analysis Services Tutorial]

Query 2:

SELECT CROSSJOIN(NONEMPTY({[Product].[Category].children},[Total Internet Sales]), 
                  NONEMPTY({[Scenario].[Scenario].members},[Total Internet Sales])
                 ) ON COLUMNS 
FROM [Analysis Services Tutorial]

Edit 2

As the answer said the queries are not the same. I realized when @GregGalloway presented other scenario. I did an excel with sample data so maybe someone can find it useful. enter image description here

Upvotes: 3

Views: 2193

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

They aren't equivalent since both queries we will return different results. For example, against the real Adventure Works (not some tutorial version) these two queries return different results. Notice that the Clothing/Kentucky column shows null on the second query:

SELECT NONEMPTY(CROSSJOIN({[Product].[Category].children}, 
                           {[Customer].[State-Province].[State-Province].Members} 
                          ), [Measures].[Internet Sales Amount]
                 ) ON COLUMNS
FROM [Adventure Works] 
where [Measures].[Internet Sales Amount]



SELECT CROSSJOIN(NONEMPTY({[Product].[Category].children},[Measures].[Internet Sales Amount]), 
                  NONEMPTY({[Customer].[State-Province].[State-Province].Members},[Measures].[Internet Sales Amount])
                 ) ON COLUMNS 
FROM [Adventure Works] 
where [Measures].[Internet Sales Amount]

Note that the Scenario dimension doesn't relate to the Internet Sales measure group, I don't think. So that may not be a good example. I chose the Product dimension and the Customer dimension for my example.

As discussed (and as you updated in your question) NonEmpty() should always have a second parameter so it is clear what measure you are doing NonEmpty against. Your query should also mention a measure on one axis or the WHERE clause so that you're not returning some vague "default measure". I've included a WHERE clause with a measure in my examples.

Anyway, to answer your question... assuming the measure is a physical measure or a well optimized calculated measure that runs in block mode I wouldn't be surprised if Query 1 is faster. But it depends on the measure and the size of dimensions and the sparsity of the cube. This question is very theoretical and the two queries don't return equivalent results.

Upvotes: 3

Related Questions