NONEMPTYCROSSJOIN : how does it work with linked measure groups ?

Hi,

I am experiencing a strange behaviour with both NONEMPTYCROSSJOIN() and NON(CROSSJOIN()) Mdx statements when working with linked measure groups.

Bascially, I have created a very simple "regular" MOLAP cube on SSAS2005 with one fact table and 3 dimensions. I have one single Measure Group for this cube.

Now, if I create an empty cube (no data source view) and add the measure group of my first cube as a Linked Measure Group, it seems like the Mdx function NonEmptyCrossJoin() never returns anything whereas it does on the underlying cube.

Same problem occurs when using NonEmpty(CrossJoin()).

Is there anything I need to parameter to have the NonEmpty baheviour work correclty on the linked measure group ? I've read there's a "NonEmpty behaviour" parameter for calculated members, but I'm not even using calculated measures.

Is such a behaviour a known issue ?

Do you need a more detailed example? if so, on which database ?

To give some backgroung, I have first noticed this problem after migrating a virtual cube from SSAS 2000.

Many thanks in advance

Rgds,

Francois

I eventually found an answer to this.

There was obviously some type of confusion in the migration of the virtual cube (which is based in SSAS2000 on 3 physical cubes) because not all of the sub cubes' dimensions where used.

I have recreated by linked measure groups manually, removed the unused dimension and the nonemptycrossjoin works fine now.

Hope that helps anyone having the same type of problem.

- F.

NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset.

If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example? That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset.

Yes, NON_EMPTY_BEHAVIOR is used even if there are no NON EMPTY queries. Some more information about it here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx|||

Mosha, thanks for the reply.

The takeaway from your blog regarding NEBs is that the multiplication operator (as in the * operator used for math, not for crossjoin) uses the NEB. Are there any others?

What I was hoping for was sort of a list of places where NEB's are used unless that's an enormous list.

|||

Multiplication was one of the examples. Many other (but not all) MDX functions also take it into account. The exact list is not easy to build, because there are some other additional factors affecting whether or not NEB will be used.

The most important thing though, is to ensure that NEB is used correctly, before thinking about perf. Even in this forum, we routinely see people using NEB incorrectly, which can and does cause wrong results.

non_empty_behavior advice

Hello!

I'm new in the SSAS2005 world and was wondering if the following MDX script was good, especialy the non_empty_behavior part:

SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
Descendants([ENTITIES].CurrentMember, , LEAVES) * Descendants([PRODUCTS].CurrentMember, , LEAVES)
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

As you can see, I want to display Averages for a special member of my ACCOUNTS dim and the same member is used in the non_empty_behavior property (if there is no data at an agregated level, there is not data on the leaves levels and so no need to calculate averages, at least I think so . Is it right ?

I also tried to code this with the new EXISTING function but it doesn't seem to work
SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
EXISTING Leaves([ENTITIES]) * EXISTING Leaves([PRODUCTS])
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

Should I use EXISTING that way ?

Thx!

Unless you plan to support scenarios where there is multiselect on Entities or Products - leave EXISTING out of it - the Descendants function will work just fine. If you do need to support multiselect, than you can use EXISTING, but please don't use Leaves() function in the right-hand side of assignment. You can replace it with the key level of appropriate dimension. It would also help if Entities and Products were not parent-child dimensions.

Use of Non_Empty_Behavior here is probably OK, but only if you don't have some other calculations (or even unary operators) which can interfere with how PU_MOI is computed.

|||

Thanks for your answer Mosha!

In fact, there may be a multi-select on Entities but the Average value wouldn't be of interest in that way so.. I also forgot to mention but Entities and Products are parent-child dims, so if I use the EXISTING formula, I have to use Leaves too, or it there another way ?

By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ? Maybe a Filter([Entities].Members) against the currently selected Entity ? Or is there a 'IsCalculatedMember' like function I could use ?

|||

The fact that Entities and Products are parent-child will cause issues, but you still don't need to use Leaves function. Descendants with LEAVES flag is good enough here. For more differences between the two, check out this blog: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

> By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ?

Sorry, but I didn't understand the question.

NON EMPTY issue

Apologies if this is a bit simple but it's doing my head in a bit

Why would the following MDX return 4 correct records

Code Snippet

SELECT { [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

non empty{ DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time Calcs].[YTD],[Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Unit of Measure].&[2] )

however adding another measure returns 50 additional spurious measures, all with blank rows

Code Snippet

SELECT NON EMPTY { [Measures].[Signed Leases], [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Time Calcs].[YTD], [Unit of Measure].&[2] )

The "signed leases" measure was the field added

Presumably you mean that adding the Signed Leases measure returns another 50 spurious rows, not measures? Is Signed Leases a calculated measure, and if so what is its definition? I'm guessing [Time Calcs].[YTD] is another calculated measure (and is probably the culprit) - what is its definition? Have you been using the Non_Empty_Behavior property anywhere?

Chris

|||

Chris,

Yes, you were correct in your assumption. TimeCalcs was OK, it was actually the fact that signed leases was calculated. Adding an iif statement to create a null sorted it

NON EMPTY

Hello
I have an MDX statement that looks like this:
SELECT
{ Measures.members } on Columns ,
{ CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July],[Business].[User].[Business
Entity Id].[212].Children ) } on Rows
FROM UsageStats
And it returns this via a matrix report:
2004
|--|--|--|--|
| May | June | July |Total |
--|--|--|--|--|
Alan Smith | 2 | 3 | 2 | 7 |
Amy Marcus | | 3 | 3 | 6 |
Bob Fields | | | | |
Carry Grant | 1 | | | 1 |
--|--|--|--|--|
TOTAL | 3 | 6 | 5 | 14 |
--|--|--|--|--|
The report above represents hits to a website on a per-
Monthly basis for each user.
I would like to supress the empty rows so that Bob Fields,
above, won't show up at all. I tried using
NonEmptyCrossJoin like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
and just NON EMPTY like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
Both methods above supress the empty rows, but they also
supress anyone who hasn't had any hits to the site in the
first month, even if they do have hits in the 2nd or 3rd
months (so both Bob Fields, and Amy Marcus are both left
out of the report above).
Does anyone know how to possibly get around this? Any help
at all will be greatly appreciated!!! :D
Best regards
JanoYou might be able to use FILTER in conjunction with the CoalesceEmpty to
meet your need:
Example of CoalesceEmpty against Foodmart 2000 Sales cube to set
Measures.[Store Sales] to 0 for any Quarter that is empty:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} )
ON ROWS
FROM Sales
=====================================================================
Now we add FILTER statement to remove any stores that did not have sales in
all Quarters:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
FILTER(
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} ),
([Store].CurrentMember,
[Product].CurrentMember, [Measures].[NonEmpty Sales]) > 0 )
ON ROWS
FROM Sales
=====================================================================--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
jhmiller@.online.microsoft.com
"Jano" <JanoBermudes@.microsoft.com> wrote in message
news:31d401c47eca$c1c7b200$a301280a@.phx.gbl...
> Hello
> I have an MDX statement that looks like this:
> SELECT
> { Measures.members } on Columns ,
> { CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
> [Calendar].[2004].[July],[Business].[User].[Business
> Entity Id].[212].Children ) } on Rows
> FROM UsageStats
>
> And it returns this via a matrix report:
> 2004
> |--|--|--|--|
> | May | June | July |Total |
> --|--|--|--|--|
> Alan Smith | 2 | 3 | 2 | 7 |
> Amy Marcus | | 3 | 3 | 6 |
> Bob Fields | | | | |
> Carry Grant | 1 | | | 1 |
> --|--|--|--|--|
> TOTAL | 3 | 6 | 5 | 14 |
> --|--|--|--|--|
> The report above represents hits to a website on a per-
> Monthly basis for each user.
>
> I would like to supress the empty rows so that Bob Fields,
> above, won't show up at all. I tried using
> NonEmptyCrossJoin like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> and just NON EMPTY like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> Both methods above supress the empty rows, but they also
> supress anyone who hasn't had any hits to the site in the
> first month, even if they do have hits in the 2nd or 3rd
> months (so both Bob Fields, and Amy Marcus are both left
> out of the report above).
> Does anyone know how to possibly get around this? Any help
> at all will be greatly appreciated!!! :D
> Best regards
> Jano
>