Mark Mrachek - Business Intelligence Blog

MDX NonEmpty() function vs NonEmptyCrossjoin() function in Analysis Services 2005

If you have ever used the NonEmptyCrossjoin function, you may have been surprised (and frustrated) with the results your queries returned. This function was added to Analysis Services 2000 to help with performance of queries, but it commonly returned incorrect results. Fortunately the NonEmptyCrossjoin function is being deprecated.

The NonEmpty() function is new in Analysis Services 2005 and should be used instead of the NonEmptyCrossjoin function. The NonEmpty() function is powerful because it can be applied to MDX expressions whereas the NON EMPTY clause was only available at the top level in SELECT statements.

This syntax for the NonEmpty() function is NonEmpty({set},[filter set])

I met with a developer and PM from the Analysis Services team last week in Redmond and they provided me with more information on the NonEmpty() function.

If you use the NonEmpty() function without a filterset, then the expression is evaluated using the default measure in your cube. So, if you do not define a filterset and you did not specify a default measure in your cube, then you may get unexpected results with this function. Because, even though you may not have explicity defined a default measure, all cubes have a default measure (Analysis Services will define a default measure even if you did not define one) and the NonEmpty() function will use this default measure as the context in which to evaluate the expression.

2/19/2006 6:21:11 PM (Pacific Standard Time, UTC-08:00)

Comments [0] MDX |

Conditional Statements in the MDX WHERE Clause

I’ve written thousands of lines of MDX code over the years to support web reporting applications and today I discovered something new. Although it probably isn’t new to MDX, it was new to me because I never needed it until today. I was targeting the ascendants of a member and based on the parent of the member, I needed to alter the slice of the cube.

Typically, when I use conditional statements, I create calculated member and handle the logic in the WITH statement. Today’s challenge was different because I was not able to control what calculated members are passed from Panorama to Analysis Services. Instead, I needed to work through the ‘WHERE’ clause to slice my data.

Anyways, I don’t know if I’ll ever need to use it in the future, but it’s nice to know that this functionality exists. Below is a sample MDX statement that uses a conditional statement in the ‘WHERE’ clause. This sample provides little value except to illustrate that you can indeed use conditional statements in ‘WHERE’ clauses.

SELECT [Measures].[Internet Total Product Cost] ON 0,
[Sales Reason].[Sales Reasons].Members ON 1
FROM [Adventure Works]
WHERE (IIF([Sales Territory].[Sales Territory].[Sales Territory Country].[Australia].Parent.Name = “Pacific”, [Sales Territory].[Sales Territory], [Sales Territory].[Sales Territory].[Sales Territory Country].[Australia]))

1/25/2006 10:46:50 PM (Pacific Standard Time, UTC-08:00)

Comments [0] Analysis Services | MDX |