Hi all

I am having a bit of a performance issue with the MDX Avg and
NonEmptyCrossJoin functions. Heres the requirement:

I have two time dimensions (calendar and fiscal), a site dimension (i.e. a
location) and a Petrol qty measure. The requirement is to have a monthly site
average for calendar or fiscal depending on which time dimension has been
used.

I am using AS2000 and OWC as my front end display.

My mdx is as follows:
Iif ([Time Month].[Calendar].Currentmember is [Time
Month].[Calendar].Defaultmember,Avg(NonEmptyCrossJoin(Descendants([Time
Month].[Financial].CurrentMember, [Time
Month].[Calendar].[Month]),Descendants([Site].CurrentMember,
[Site].[Site])),sum({([Volume
Hierarchy].[Petrols])},iif([Measures].[Purchases
(Qty)]=null,0,[Measures].[Purchases
(Qty)]))),Avg(NonEmptyCrossJoin(Descendants([Time
Month].[Calendar].CurrentMember, [Time
Month].[Calendar].[Month]),Descendants([Site].CurrentMember,
[Site].[Site])),sum({([Volume
Hierarchy].[Petrols])},iif([Measures].[Purchases
(Qty)]=null,0,[Measures].[Purchases (Qty)]))))

Performance is not good and I was hoping that some clever person out there
would give me some pointers. Performance was not so bad when I was just
trying to average over month (cal or fisc), but when I brought in the site as
part of the average together with the nonemptycrossjoin function, performance
got worse.

Thanks
Trevor

Hi Trevor,

Maybe by computing the average (sum/count) explicitly, rather than using
Avg(), you can leverage the pre-aggregated sum of the Petrol qty
measure:

[Measures].[Purchases (Qty)]) /
Iif ([Time Month].[Calendar].Currentmember is
[Time Month].[Calendar].Defaultmember,
Count(NonEmptyCrossJoin(Descendants(
[Time Month].[Financial].CurrentMember,
[Time Month].[Financial].[Month]),
Descendants([Site].CurrentMember, [Site].[Site]))),
Count(NonEmptyCrossJoin(Descendants(
[Time Month].[Calendar].CurrentMember,
[Time Month].[Calendar].[Month]),
Descendants([Site].CurrentMember, [Site].[Site]))))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak

Thanks for your reply. I tried your new mdx expression against the original
and found that it was slower than the original. The original takes 13 seconds
from when you drop the field onto the OWC page and the new one takes 22
seconds. Also the two mdx strings yield different results.

Any other suggestions?

"Deepak Puri" wrote:

if you have 1 row by month / site, why you don't use a simple count measure?

month 1 / site 1 / count=1
month 1 / site 2 / count=1
month 2 / site 1 / count=1
month 2 / site 3 / count=1

this is exactly the same as your dcount formula (count of nonemptycrossjoin)
but this measure is aggregated.

I presume that the fiscal month is the same as your month (I mean january =
january; but not the quarters and years)


"Trevor Howe" <TrevorHowe (AT) discussions (DOT) microsoft.com> wrote

Unfortunately, my grain is not at the site level, so I cannot do a normal
count and distinct count has its own related issues (most frustrating of all
being the inability to multi select dimensions - cant wait for SQL 2005 to
make its appearance in our organisation).

The fiscal year is an alternate calendar representing the organisations
fiscal period being March to Febrary and yes, at the month level both fiscal
and calendar month are the same, but not at quarter and year level.

"Jéjé" wrote:

Hi Trevor,


It's puzzling that the alternate MDX expression would take longer,
unless there are no usable aggregates at the higher levels of the Site
and Calendar/Financial dimensions. Are these defined as parent-child or
changing dimensions?

As to why the results returned also differ, perhaps there is something I
missed in the definition of the average. The expression is taking the
value of ([Volume Hierarchy].[Petrols], [Measures].[Purchases (Qty)]) at
the currently selected [Site] and [Time] co-ordinates, then dividing by
the count of non-empty site/month combinations. This should be
equivalent to taking the average across these tuples - is there a simple
sample of the discrepancy?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

ok...

try to create some calculated measures.
validate that your cube is aggregated at the site level. (maybe use 2 cubes,
1 for your DCount measures, the second for the sums measures)

in the nonemptycrossjoin, make sure you specify a measure instead-of the
default measure.
Count(NonEmptyCrossJoin(Descendants(
[Time Month].[Financial].CurrentMember,
[Time Month].[Financial].[Month]),
Descendants([Site].CurrentMember, [Site].[Site]),
measures.AnyCountMeasure,2), excludeempty)


"Trevor Howe" <TrevorHowe (AT) discussions (DOT) microsoft.com> wrote