Sunday, April 21, 2013

MDX Tricks: Part 1


Non Empty v/s NonEmpty:

Non Empty:

This will return all the columns which are not null for all the tuples of rows axis:


SELECT  
  NON EMPTY  
    { 
      
    } ON COLUMNS 
, { 
    
  } ON ROWS 
FROM [Cube];

This will return all the rows which are not null for all the tuples of column axis:

SELECT     
    { 
      
    } ON COLUMNS 
NON EMPTY 
    
  } ON ROWS 
FROM [Cube];

Non Empty:


returns the set of tuples that are not empty from a specified set,

SELECT  
  { 
  } ON COLUMNS 
,{ 
    NonEmpty 
    ( 
      [Geography].[Country].Children 
     ,[Measures].[Subscribers] 
    ) 
  } ON ROWS 
FROM [Cube];

As you can see, the NonEmpty operator takes all the rows having a not NULL value for Subscribers in the rows 

and then displays all the measures defined in the column axis. Basically what happens internally is that 

NonEmpty is evaluated when the sets defining the axis are evaluated. So at this point of time, there is no 

context of the other axes.

SELECT  
  {[Date].[Month].[March]} ON COLUMNS 
,{ 
    NonEmpty([Geography].[Country].Children
  } ON ROWS 
FROM [Blog Statistics] 
WHERE  
  [Measures].[Hits];

NonEmpty is evaluated when the set defining the axis is evaluated (here, Country) and at that point of time, 

NonEmpty is evaluated for each member of the country against the default member of the Date dimension 

(which would be ALL generally). 


Use NonEMPTY before filtering the rows to improve the performance as shown below:


SELECT  
  {[Measures].[Hits]} ON COLUMNS 
,{ 
    Filter 
    ( 
      NonEmpty 
      ( 
          [Geography].[Country].Children 
        *  
          [Date].[Month].Children 
       ,[Measures].[Hits] 
      ) 
     , 
      [Measures].[Hits] > 30 
    ) 
  } ON ROWS 
FROM [Blog Statistics];




No comments:

Post a Comment