I have a parameterised query, generated by 3rd party tool:

SELECT NON EMPTY { { { DESCENDANTS( [Product].[All Product],
[Product].[1 level from top] ) } * { [Measures].[mymeasure] } } } ON
COLUMNS ,

NON EMPTY { DESCENDANTS( [customers].[All Customers], [customers].[leaf
level] ) } ON ROWS

FROM [my physical cube]

WHERE ( [date].[1 jan 2005] )


where mymeasure is a base measure - not a calculated member and the
cube is a physical cube.

This performs fine. This also works fine when i change the date to 2
jan 2005.

Now if i need to get the results where the filter is for both days at
the same time (ie. not using a slicer), my understanding (i'm not very
literate in MDX) is that i have to create a calculated member to
aggregate both days and use that member on the filter, so my MDX, as
generated by 3rd party tool is:

WITH MEMBER
[date].[Aggregation] AS '
AGGREGATE(
GENERATE( { [date].[1 jan 2005], [date].[2 jan 2005] },
HEAD(
INTERSECT( { [date].[1 jan 2005], [date].[2 jan 2005] },
ASCENDANTS( [date].CURRENTMEMBER ))))) ', SOLVE_ORDER = 0
SELECT
NON EMPTY { { { DESCENDANTS( [Product].[All Product], [Product].[1
level from top] ) } * { [Measures].[mymeasure] } } } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [customers].[All customers],
[customers].[leaf level] ) } ON ROWS

FROM
[fxdistribution]

WHERE
( [date].[Aggregation] )

This performs extremely poorly (~16 minutes compared to <5 seconds for
the day-at-a-time examples above), and a specific aggregation does not
appear to help. I have also tried non-empty-crossjoin function which
reduced the time down to 2 minutes, but this is still a long time.

Users need the date range to be any date range.

Firstly, is there a better way to write this query?

Is anyone else getting this sort of business/performance problem?

other than server execution location, and connection string changes,
what can i do to speed this up?

the following simplified MDX of my problem example in the previous
post, is still a problem...

WITH MEMBER [date].[Aggregation] AS
' AGGREGATE( { [date].[1 jan 2005], [date].[2 jan 2005]})', SOLVE_ORDER
= 0

SELECT
NON EMPTY { { { DESCENDANTS( [Product].[All Product], [Product].[1
level from top] ) } * { [Measures].[mymeasure] } } } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [customer].[All customer], [customer].[leaf
level] ) } ON ROWS

FROM [my physical cube]

WHERE
( [date].[Aggregation] )

Does this version show any improvement in performance:

Set [DateRange] as
'{[date].[1 jan 2005], [date].[2 jan 2005]}'
MEMBER [date].[Aggregation] AS
'AGGREGATE([DateRange])', SOLVE_ORDER
= 0

SELECT
NONEMPTYCROSSJOIN(
DESCENDANTS([Product].[All Product],
[Product].[1 level from top]),
{[Measures].[mymeasure]},
[DateRange], 2) ON COLUMNS ,

NONEMPTYCROSSJOIN(
DESCENDANTS([customer].[All customer],
[customer].[leaf level]),
{[Measures].[mymeasure]},
[DateRange], 1) ON ROWS

FROM [my physical cube]

WHERE
([date].[Aggregation])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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