Home > MDX Challenges > MDX #1 – Why should we always provide set_expression2 in NonEmpty() function

MDX #1 – Why should we always provide set_expression2 in NonEmpty() function

NonEmpty() is a very powerful MDX function for improving your MDX query performance. It is primarily used to remove unwanted empty sets in a very fast manner.

According to MDX Function Reference, it returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. The syntax is simple enough:

NONEMPTY(set_expression1 [,set_expression2])

Look closely, you will see that the usage of the set_expression2 is optional. I’ve been using NonEmpty() without the set_expression2 for many of my SSRS reports. Going from “out-of-memory” on the report server to only a few seconds to render my reports, I was so happy that I’ve never thought about testing record counts or some measures, until I finally had time.

What I saw in my test results shocked me. After some research and reading, I realized that the problem is in the omission of the set_expression2. By including the appropriate set_expression2, I got the perfect results.

In this blog, I’ll try to share with you this little lesson I’ve learned.

Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves

This is an important note from the above MDX Function Reference from Microsoft. Now that I am reading this note, I start to visualize:

1) set_expression1 will be typically the cross product of members of some hierarchies, for example,  

DEDESCENDANTS([Queue].[Queue].[Level 03].[Online Email], [Queue].[Queue].[Level 05], self) *

[Time].[Day].[Day].MEMBERS

2) Since the non-empty is really a characteristic of the cells, and we want to remove sets that have no certain measures, does that that mean we really need to specify the measures we are referring to? Now it makes sense to me that we do need to specify the measures we are referring to.

If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the measures in the cube

This is another important note from the above MDX Function Reference from Microsoft. So if the second set is not provided, we are blindly relying on the Analysis Services to find these “current coordinates”. “Current” really means at the moment of evaluation. Unfortunately, these “current coordinates” can be pretty random at the moment of evaluation, depending on how we setup our default members, and especially if we are defining calculated measures in our query. The default initial value of any calculated measure is null. This can lead to a very messy, confusing, and unexpected query results.

set_expression2 is not provided – wrong result

This is part of my initial query, where the second set expression is not provided.

image

Although the NonEmpty() function is used, rows with all nulls are plainly showing. Further testing showed that the record counts and measures are incorrect.

image

set_expression2 is provided – All looks good!

This is part of my revised query, where the second set expression is now provided with a measure.

image

There is no longer any rows that are all null. Further testing showed that all numbers are correct. 

image

Conclusion – Always include a measure in the second set in NonEmpty() function

I will put this lesson in my MDX best practice list. Hope you will do the same.

About these ads
Categories: MDX Challenges
  1. December 18, 2012 at 3:16 am

    Good Info, I will definitely add this to my best practice. I have unfortunately got some issue with calculated measure (Year to date). Below is my MDX:

    SELECT {[Measures].[Is Leaf Level] , [Measures].[Actual], [Measures].[Budget],
    [Measures].[BudgetVariance],[Measures].[LastYear],[Measures].[Growth],
    [Measures].[YTDActual],[Measures].[YTDBudget],[Measures].[YTDBudgetVariance],
    [Measures].[YTDLastYear],[Measures].[YTDGrowth]} ON COLUMNS,
    NONEMPTY({([Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS *
    [Date].[Fiscal Period].[Fiscal Period].ALLMEMBERS *
    [GLAccount].[Entity Code].[Entity Code].ALLMEMBERS *
    [GLAccount].[Entity Code Friendly Name].[Entity Code Friendly Name].ALLMEMBERS *
    [GLAccount].[Department Code].[Department Code].ALLMEMBERS *
    [GLAccount].[Department Code Friendly Name].[Department Code Friendly Name].ALLMEMBERS *
    [GLAccount].[Service Code].[Service Code].ALLMEMBERS *
    [GLAccount].[Service Code Friendly Name].[Service Code Friendly Name].ALLMEMBERS *
    [GLAccount].[GL Account Code].[GL Account Code].ALLMEMBERS *
    [GLAccount].[Account Code].[Account Code].ALLMEMBERS *
    [Category].[Sort Order].[Sort Order].ALLMEMBERS *
    [Category].[Unary Operator].[Unary Operator].ALLMEMBERS *
    DESCENDANTS([Category].[Category Parent Key].[Level 05].ALLMEMBERS))} , [Measures].[Actual]) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS
    FROM ( SELECT ( [NonCapitalFundingGroup] ) ON COLUMNS
    FROM ( SELECT ( [FacilityPnL Accounts] ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[GLAccount].[Service Code].&[20]“, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[GLAccount].[Department Code].&[1272]“, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[GLAccount].[Entity Code].&[1272]“, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[GLAccount].[Company].&[T1071]“, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[Date].[Fiscal Year].&[2013]“, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(“[Date].[Fiscal Period].&[2013]&[2]“, CONSTRAINED) ) ON COLUMNS
    FROM [TricareBI]))))))))
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    I used NonEmpty() function with second parameter of [Measures].[Actual]. It seem ok at first glance but I noticed that year to date measure on some GLAccounts missing. This happened when I got no value in [Measures].[Actual].

    Is it possible to specify more that one measures for the second parameter? or How could I get this issue resolved?

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: