I have the following MDX in which I am trying to return
the Producers in a particular state for the years 1999 to
2002. Even though I am using the NonEmptyCrossJoin
function, my result set contains a lot of empty rows. Out
of a total of 39000 rows returned a majority of them
contain no data. How can I just return only the rows that
have data.

I would appreciate any help I can get on this.
Thanks

The MDX I am using is:

WITH
MEMBER [Risk State].[Risk States] As 'SUM({[Risk State].
[19]})'
SET [Accident Years] As 'FILTER([Accident
Date].Year.Members, [Accident
Date].CurrentMember.Name>= "1999" And
[Accident Date].CurrentMember.Name<= "2002")'
SELECT {Measures.Members} ON AXIS(0) ,
NonEmptyCrossJoin([Accident Years], Except
({[Producer Name].Members}, {[Producer Name].[All Producer
Name]})) ON Axis(1)
FROM [Vehicle Pricing]
WHERE [Risk State].[Risk States]

You can use the 'CoalesceEmpty' command in front of the
MDX.

KRS

The problem may be that the NonEmptyCrossJoin does not implicitly filter
on the Where Slicer, namely the State. If so, try this version of the
query with explicit State:

MEMBER [Risk State].[Risk States] As 'SUM({[Risk State].
[19]})'
SET [Accident Years] As 'FILTER([Accident
Date].Year.Members, [Accident
Date].CurrentMember.Name>= "1999" And
[Accident Date].CurrentMember.Name<= "2002")'
SELECT {Measures.Members} ON AXIS(0) ,
NonEmptyCrossJoin([Accident Years], Except
({[Producer Name].Members}, {[Producer Name].[All Producer
Name]}), {Risk State].[19]}, 2 ) ON Axis(1)
FROM [Vehicle Pricing]
WHERE [Risk State].[Risk States]
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This is really interesting. What's going on is that the nonemptycrossjoin
uses the calculated member from the slicer axis to evaluate the
nonemptycrossjoin. But BOL says the calculated members are ignored. This
means its replaced with the defaultmember (which is most likely the all
member).

So you are getting the necj using the all member. But you're seeing the data
for the member in the slicer axis.

Deepak's suggestion is right, but it can be simplified a bit so you don't
have to repeat the set twice. Try this in warehouse cube in the Foodmart
sample database:

with member store.x as 'sum({[Store].[All Stores].[Canada]})'
select measures.members on 0, nonemptycrossjoin([Product].[Brand
Name].members, [Time].[Month].members) on 1 from warehouse where (store.x)

returns the rows that have data at the all stores. But the data shown is for
Canada which has no data! Cool!

But this:

with set myset as '{[Store].[All Stores].[Canada]}'
member store.x as 'sum(myset)'
select measures.members on 0, nonemptycrossjoin([Product].[Brand
Name].members, [Time].[Month].members, myset, 2) on 1 from warehouse where
(store.x)

Returns the results you would expect. That is, nothing. (And faster, too).

Hope this helps,
Richard


--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Richard,

One thing that is not clarified about NECJ() in BOL is the difference in
behavior of a calculated member in the 'Where'
Slicer vs. in the argument list itself. In the latter case,
the default (All) member is apparently not substituted. For example,
this version of the Foodmart query will always return no rows, even if
'myset' includes USA:

member store.x as 'sum(myset)'
select measures.members on 0, nonemptycrossjoin([Product].[Brand
Name].members, [Time].[Month].members, {store.x}, 2) on 1
from warehouse where
(store.x)


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Deepak,

Yeah - the behavior is interesting. Check this out:

This query returns data:

with member measures.x as '1'
select measures.members on 0, nonemptycrossjoin([Product].[Brand
Name].members, [Time].[Month].members, {measures.x}, 2) on 1
from warehouse

This one does not:

with member store.x as '1'
select measures.members on 0, nonemptycrossjoin([Product].[Brand
Name].members, [Time].[Month].members, {store.x}, 2) on 1
from warehouse

The non empty behavior of calculated members in the measures dimension looks
to be different than in non-measure dimensions. This all points to being
very careful using this function. It is intended for performance purposes
(not having to evaluate a calculation to determine if a tuple is empty).

Cheers,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Richard,

Thanks for pointing out the difference in the NECJ behavior of the
Measures dimension - I recall running across this one time, but was too
perplexed to make sense of it.

Because of the potential pitfalls in using NECJ for novice users, is
there any chance that future editions of BOL will expound on these NECJ
nuances in greater detail?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Things are being changed in the next release. NECJ should join the
mainstream and not be as narrowly focused as it is today.

Regards,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Richard, Deepak

I love these kinds of discussions ;-)

I guess the reason that NECJ behaves differently when you have calculated
measures and calculated members on non-measures dimensions is because NECJ
seems to automatically ignore any member on the measures dimension. And I
suppose this is because in a regular cube for any given combination of
non-measure members, you can't have one measure returning a null value and
another measure returning a non-null value - in that case, the null would be
converted to a zero, so there's no point evaluating it. However this doesn't
hold for virtual cubes, which leads to another interesting phenomenon. Take
the following Foodmart query:

SELECT
MEASURES.MEMBERS ON 0,
DESCENDANTS([Product].[All Products].[Food].[Canned Foods].[Canned Oysters],
[Product].[Product Name])
ON 1
FROM [WAREHOUSE AND SALES]

You'll see that for Better Fancy Canned Oysters there is no data available
for the measures from the Warehouse cube, but there is data for the measures
from the Sales cube. When you try to filter our the Products with no data
for Store Invoice, for example by saying

SELECT
MEASURES.MEMBERS ON 0,
NONEMPTYCROSSJOIN(
DESCENDANTS([Product].[All Products].[Food].[Canned Foods].[Canned Oysters],
[Product].[Product Name])
, {[Measures].[Store Invoice]},1)
ON 1
FROM [WAREHOUSE AND SALES]

You see that it has no effect! But of course if you ran the same query on
the Warehouse cube, you would lose Better Fancy Canned Oysters and the other
empties:

SELECT
MEASURES.MEMBERS ON 0,
NONEMPTYCROSSJOIN(
DESCENDANTS([Product].[All Products].[Food].[Canned Foods].[Canned Oysters],
[Product].[Product Name])
, {[Measures].[Store Invoice]},1)
ON 1
FROM [WAREHOUSE]

Though since measures are being ignored, you might as well just write:

SELECT
MEASURES.MEMBERS ON 0,
NONEMPTYCROSSJOIN(
DESCENDANTS([Product].[All Products].[Food].[Canned Foods].[Canned Oysters],
[Product].[Product Name])
)
ON 1
FROM [WAREHOUSE]

to get the same effect.

Fun and games...

Regards,

Chris



--
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and
confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote