I am quite sure that many of you MDX nerds have been reading articles and tips on Moshas old blog (http://sqlblog.com/blogs/mosha/). Mosha was one of the inventors of MDX.

If you are struggling with MDX performance take a look on Moshas blog.

One of my favourite trick on this blog is the alternative way of counting records in MDX. Off course you have the regular way using the COUNT function.

WITH MEMBER [Number Of Products] AS
  Count ( Filter ( [Product].[Product Name].[Product Name], [Measures].[Internet Total Sales] ) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]

The problem with this is that it does not evaluate as bulk mode. This will make the query run really slow.

One way of making this query faster is to use the SUM function with an IIF test.

WITH MEMBER [Number Of Products] AS
  ( Sum (
    [Product].[Product Name].[Product Name].Members,
    IIf ( [Measures].[Internet Total Sales] > 0, 1, NULL )
  ) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]

This reduced the query time for one of my queries from about a minute to 4 seconds.