I wondered if anyone might be able to help me to speed up a calculated
member by improving on the MDX.
I want to count the leaf level members of our Policy.NewCOB dimension
only where another calculated measure, Test1, exists (this member is
null for some policies).

I have created the count member, Test2, thus:

Count(Filter(Descendants([Policy].[NewCOB].CurrentMember, [PolId]),
(NOT IsEmpty([Measures].[Test1]))))

but because there are 170 000 or so policies, this is very slow.

Any ideas gratefully received!

How about next expression?

Count(
CrossJoin(Descendants([Policy].[NewCOB].CurrentMember, [PolId]),
{[Measures].[Test1]}), ExcludeEmpty
)

If you use AS 2005, you can consider use NonEmpty or exists functions. For
example,

Count(NonEmpty(Descendants([Policy].[NewCOB].CurrentMember, [PolId]),
[Measures].[Test1]))

Because NonEmpty function is optimized function to remove empty tuple.

Ohjoo



"rachel" <rachel.jones (AT) talbotuw (DOT) com> wrote

Thank you very much Ohjoo, that does also work but unfortunately is as
slow as the other method. We are going to upgrade to AS2005 so maybe
that will improve the speed, but non empty doesn't really help as all
our measures are populated (ie there are no nulls in them, they all
evaluate to zero if empty).
Rachel
Ohjoo Kwon wrote:

you have to optimize the cube correctly to improve the performance.
also try to change the connectionstring to add Cache Policy=7

in SSAS 2005 use a real distinct count measure to insure the performance.
If you want to continue to use a calculated measure double test the nonempty
& exists functions... their behavior are different and I recommand to use
the 2 functions at the same time if your Policy dimension will provide
attributes accessible outside a use hierarchy
exists isolate members regarding other attributes, nonempty isolate members
regarding existing data in the cube; without using the 2 at the same time
the dynamic DCount doesn't work correctly.


"rachel" <rachel.jones (AT) talbotuw (DOT) com> wrote