Hi Every One,
When I am runing query using Non Empty to filter null rows, the resultset
the server returns, returns all the rows including the null ones and then the
client computer does the filltering on the null rows and not the server. The
result is that a query that should return only 100 rows with no null return
2,000,000 with null and it takes the client a long time to filter the nulls
and give me the final result. It happens by using Ado with vb.net or by using
the MDX sample application. It also happens by openning connection string
with execution location=3 (server side).
Have any idea how to solve it?
Thanks,
Ruby

Hi Ruby,

Can you describe the dimensions and MDX query that generate the 2
million rows - is it multiple cross-joins? Also, does it involve
calculated measures, in which case this MS KB article may help:

http://support.microsoft.com/default...b;en-us;304137
Keyword

Article ID : 304137
Last Review : July 15, 2004
Revision : 1.1
This article was previously published under Q304137

SUMMARY
In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

HI Deepak Puri,
thanks for answering (and not for the first time).
the query has a crossjoin between 2 dimensions with about 200,000 members
each.it also has calculated measures as you thought correctly.

"Deepak Puri" wrote:

Hi Again,
i read the article but it says what to do on the members if they are
already created by the time i run the query. what should i do when i build
the member inside the mdq query?
this is my query:
WITH
MEMBER
[Company].Selection
As
' sum ( {[Company].[All Company].[Microsoft],[Company].[All
Company].[Apple] } ) '
MEMBER
[Date].DateRange
As
' sum ( {[Date].[All Date].[2005].[1].[1].[1]:[Date].[All
Date].[2005].[4].[12].[1] } ) '
MEMBER
[Sector].SectorOp
As
' sum ( { [Sector].[All Sector].[English],[Sector].[All Sector].[Spanish] }
) '
SELECT
Non Empty {[Measures].allmembers } ON COLUMNS,
Non Empty {CrossJoin
({[program].[operatortype].Members},{[Product].[product].Members})} on rows
FROM
[ADV_SMALL]
WHERE
( [Date].DateRange,[Sector].SectorOp, [Company].Selection )
thanks

"Deepak Puri" wrote:

Unfortunately, since the calculated members are not measures, the Non
Empty Behavior property won't help here. There may be other options with
AS 2005, but below is an approach for AS 2000 (assuming that all
Measures are empty for cells with no underlying fact data):

Set [CompanySet] as
'{[Company].[All Company].[Microsoft],
[Company].[All Company].[Apple]}'
MEMBER [Company].Selection as
'Sum([CompanySet])'
Set [DateSet] as
'{[Date].[All Date].[2005].[1].[1].[1]:
[Date].[All Date].[2005].[4].[12].[1]}'
MEMBER [Date].DateRange as
'Sum([DateSet])'
Set [SectorSet] as
'{[Sector].[All Sector].[English],
[Sector].[All Sector].[Spanish]}'
MEMBER [Sector].SectorOp as
'Sum([SectorSet])'
SELECT
Non Empty [Measures].allmembers ON COLUMNS,
NonEmptyCrossJoin(
[program].[operatortype].Members,
[Product].[product].Members,
[CompanySet], [DateSet],
[SectorSet], 2) on rows
FROM [ADV_SMALL]
WHERE ([Date].DateRange, [Sector].SectorOp, [Company].Selection)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

THANKS, I will try it.

"Deepak Puri" wrote:

hI Deepak Puri'
It works fine. Thanks again. can you explain me the difference between the
to queries: yours and mine, and must i use the set or can i use the mwmbwrs
as i used?
Ruby

"Deepak Puri" wrote: