How Exactly Is NONEMPTY Working Here?

Question

I have a cube with two measure groups, one in MOLAP format and the other in ROLAP format. I've also created a calculated member ([C]) from a single column in each of the member groups ([A] MOLAP and [B] ROLAP). All I want is for [C] to return me the value of [A] if it is not empty, otherwise return [B]. This is achieved pretty easily with a CASE statement:

WITH MEMBER [C] AS
    CASE
        WHEN ISEMPTY([A]) THEN [B]
        ELSE [A]
    END

This works great, and running the following query displays the results exactly like I would expect them to be displayed, where [C] is [B] only when [A] is empty.

SELECT
    { [A]
    , [B]
    , [C]
    } ON COLUMNS
    , [Time].CHILDREN ON ROWS
FROM    
    [Cube]

However, there are some cases where both [A] and [B] are empty, and I would like to filter them out of the result set. Normally, I would be able to just wrap the [Time] dimension in NONEMPTY to achieve this:

SELECT
    { [A]
    , [B]
    , [C]
    } ON COLUMNS
    , NONEMPTY([Time].CHILDREN) ON ROWS
FROM    
    [Cube]

But when I try this with the above query, the results filter out all of the rows where [A] is empty, regardless of whether or not [B] is empty. Rows where [B] is empty are not filtered out.

I would expect that NONEMPTY would either:

1. Filter out only rows where [C] is empty ([A] and [B] are empty) OR
2. Filter out all rows where [A] OR [B] is empty

Why is NONEMPTY only taking [A] in to account here?

Answer 1

There is the NonEmpty function and the NON EMPTY key word. They do not have the same behavior.

If you write:

NON EMPTY [Time].CHILDREN ON ROWS

It will remove the empty rows.

The NonEmpty function does not take into account the other axes. Let's call h1 the hierarchy of [A], [B] and [C]. In your case the NonEmtpy is computed on the default member of h1.

For your query

NONEMPTY([Time].CHILDREN) ON ROWS

is equivalent to

NONEMPTY([Time].CHILDREN, {[h1].DefaultMember}) ON ROWS

The following query should help you to understand what happens:

SELECT
    { [A]
    , [B]
    , [C]
    , [A].Hierarchy.DefaultMember
    } ON COLUMNS
    , [Time].CHILDREN ON ROWS
FROM    
    [Cube]