sql server data warehouse: Has anyone any knowledge of optimizing MDX?

I have several MDX queries I have inherited, which take (some) in excess of
a minute to return.
I have been through Spofford's book and others. None seem to address a best
practices approach.

Any suggestions.

Thank you

What do you have?

there are some practices what to avoid...



[quoted text, click to view]

Here is an example.
The date dimension I know is baddly designed without any true unique members
at any level. This needs to change.
When I remove the filter for the sum > 0 the query is quite quick.
Otherwise it is sluggish.....
any suggestions regarding the mdx structure. I am looking for a short term
improvement until I can re-design the cube.

Thanks in advance....


WITH
SET [DateRangeSpan] AS
'{ [DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[February].[26]
:[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[March].[25] }'

SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]} '

SELECT
{[OrgUnit].&[1-990], [OrgUnit].&[1-990].CHILDREN} DIMENSION
PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,

Subset (
FILTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [Activity].[Activity].Members,
[DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores
Complete],[Measures].[% Complete]})
, Sum({Axis(0)}, [Measures].[Raw Stores
Included]) > 0
)
, 0, 150
)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID],
[Activity].[Activity].[Process ID], [Activity].[Activity].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity].[Process
Name],
[Activity].[Activity].[Activity Due Date],
[Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]


[quoted text, click to view]