We have a cube that contains financial transactions for medical
patients. Each transaction has a key to the patient that the
transaction belongs to.

Our customers want a measure that gives them a patient count, as they
build a pivot table using various dimensions and filters.

A COUNT measure won't work, because that's basically just a count of
all transactions.

A DISTINCT COUNT measure won't work due to the various limitations of
DISTINCT COUNT. In particular, as we start adding filters, the
DISTINCT COUNT returns ERR.

Our previous OLAP developer created this MDX formula. Unfortunately,
it is so slow that it usually either times out or simply never returns
the results. I'm having problems understanding how the MDX formula is
trying to accomplish the task at hand. I'm pretty sure that it is
recursively calling itself, which I suspect is one reason that it is so
slow.

I would be grateful if anyone can give guidance on this MDX, or offer
an alternative solution.


iif([Patient Name].CurrentMember.Level.Ordinal=[Patient Name].[Patient
Name].Ordinal,
iif([Measures].[Tran Cnt]=0,NULL, 1),

iif(SUM([Patient Name].CurrentMember.CHILDREN, [Measures].[Patient
Cnt (C)])<>0,

SUM([Patient
Name].CurrentMember.CHILDREN, [Measures].[Patient Cnt (C)]),

NULL)
)


--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

Looking at the MDX, multiple member selections are presumably not made
in filters on the [Patient Name] dimension. You can try something
simpler like this (assuming there is just 1 AS 2000 physical cube):

[Measures].[Patient Cnt (C)]:
[Patient Name].CurrentMember,
[Patient Name].[PatientName]))
- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak Puri wrote:

Thanks for the feedback. This measure only applies to 1 AS 2000
physical cube. I assume this is what you mean. (Our OLAP database
contains five physical cubes.)

However, the user may make multiple member selections in the filters.
We've had complaints that the Patient Cnt measure isn't always correct.
You may have found the cause of an entirely different problem as well.


Do you have a suggestion that would work when multiple member
selections are made in a filter?

Can you suggest any good MDX books (preferably specific to AS 2000)? I
just completed the Microsoft Course 2074A, Designing and Implementing
OLAP Solutions with MS SQL Server 2000. I also wanted to take 2093A,
Implementing Business Logic with MDX in MS SQL Server 2000, but
unfortunately it was not available. With the release of SQL Server
2005, I doubt it will ever be available again.

Thanks!

--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

A good book to read is MDX Solutions (WILEY - Author George Spofford)
"Jon Robertson" <JonRobertson (AT) community (DOT) nospam> wrote

Clint wrote:

Thanks. Found on Amazon and submitted purchase request.

--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

Would you consider upgrading to AS2005 - the limitation of Distinct Count
measure that you mention here is removed in that version, and you can use
multiple members in the filter and get correct results.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =

When having a distinct count measure it is good practice to seperate
that measure out to a seperate cube, with the same shared measures, and
then use a virtual cube to tie them together. This can help
performance and let you perform incremental updates on your cube
without the distinct count. Not sure if this will solve your
particular issue.

http://www.microsoft.com/technet/pro...n/sste286.mspx

Mosha Pasumansky [MS] wrote:

Sure, I would. But the 180 businesses that currently use our
application are not interested.

We're an ISV who creates a software product for others to use.
Luckily, we only had a couple dozen customers when SQL 2000 came out.
Nearly all of them have upgraded from SQL 7.

But there is absolutely no way our entire customer base will upgrade
from SQL 2000 to SQL 2005. They expect our product to work as we've
advertised. After all, they've already paid for it.

It's good to know that Microsoft fixed it in AS2005. However, the
recommendation leads me to believe that this issue cannot be fixed by
modifying our MDX in AS2000.

--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

jason.saldo (AT) gmail (DOT) com wrote:

Thanks for the link. There is a lot of good information there. But
it'll take me a while (and several reads) to absorb and understand it.

It would definitely help if I could see the structure of the cube they
are using as an example. Is this a known cube somewhere, such as
Foodmart?

Thanks

--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com