I am familiar with standard practices to assess performance query or stored procedure using SQL server 2000/2005/2008. How is the best way to assess MDX performance? I assume its possible to write structurely correct but poor performance MDX queries as easily as you can with standard T-SQL? Any suggestions?
FYI: I may be tasked with overhauling current data mining project that uses MS SQL analysis services and Proclarity client.
There are several facilities to assist tuning in Analysis Services (as of 2005).
You can get query plans for MDX in much the same way as SQL Server supports them, although the actual primitives in the engine are of a somewhat different nature.
Running usage based aggregation will pick up what slices actually get looked at in practice.
SSAS also supports the profiler, so you can get trace files to see what the server is actually getting up to.
The debugger in BIDS lets you trace the cube scripts.
However, this stuff is considerably less well documented than it is on SQL Server, so you may need to involve MS tech support, a third party consultant or Stackoverflow ;-}
Try running MDX Studio. It analyzes MDX queries and gives general performance hints for common problems.
I've found it very useful and by following the tips I've increased performance of some queries by a factor of 8.
It's free too!