Hi,

I want to validate with you what is the best way to calculate a distinct
count measure.
and specially how to setup correctly the connection string!!!

because I think I have a good formula, but when I change the connection
string (execution location...) the performance drops!!!
my formula:
Nb Customers=
count(crossjoin(nonemptycrossjoin(customers.<lowes t level>,
{measures.mycount},1), {measures.mycount}), excludeempty)

In front of the measure I have another measure:
iif(measures.SumOfSales >0 , measures.[Nb Customers], null)
(if the total sales is 0 then I know there is no customers, so I return null
to the end user without evaluating the dcount)

first, I allways count all the customers, the users can't drill down by this
dimension.
second, I'm using the office pivottable to access my cube, so I can't write
any MDX when I access my cube, the user is free.
third, I have 2 cubes, 1 with the sums and the second with a count and
aggregated at the <lowest level> only.

When I setup the connection string to execute on the server (execution
location=3) the excludeempty cause the server to generates a lot of queries
(query num perf. counter)
the same formula without any execution location (so on the client side)
takes far less time, but other formula (standard aggregated value (sum)) are
slower.
And the server has a better CPU!!!!
My dimension contains 100 000 members (with an intermediate level which is
the first letter of the name due to the 64K limit)

so, how to configure correctly my connection string?
which value for the non empty threshold parameter?
does the default isolation level values changes anything?

there is a lot of combination and I don't know which one is better.

thanks for your help.

Jerome.

PS: thanks AS2005, the DCount is far better, but I can't use it now.

See if the technique discussed in this recent thread helps (it looks
like you have an intermediate alphabetic level as well). Milind reported
reduction in query time from 36 to 4 secs:

http://groups.google.com/group/micro...olap/msg/17525
2463615cf46
From: Deepak Puri
Date: Fri, 13 Jan 2006 14:09:05 -0800

Subject: Re: MDX Query Performance

Milind,


Assuming that there are aggregations at the "Alphabetic" level, this
earlier thread technique below may help:

with
member [Measures].[AsCount] as
'Count(Generate(NonEmptyCrossJoin([Person].Children),
NonEmptyCrossJoin([Person].Children)))'

Select {[Measures].[AsCount]} on columns
from DM_Answer
where ([QuestionSet].&[DiabetesAs],
[Question].&[DMPreventSocial],
[Client].&[52])

(the above assumes that the [Person] default member is at the [All]
level, so ().Children is called twice)
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

my current formula answers in few seconds too.
but when I execute on the server side, its the oposite effect, the answer is
slow.

does I have to allways execute on the client? or does I have to change the
formula to allow the server to evaluate the result quickly?

and first question and important:
why the processing time is different when I run on the server side???
I have searched on the web about this without success.


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