January MDX Puzzle Solution Revealed - 24 Jan 2001


How can you rewrite the following query so it executes faster? (The query should answer the question "Which male customers with at least a college education bought the most different types of products?")

WITH member \[Measures\].\[Prod Count\] AS 'Count( Filter( \[Product\].\[Product
	Name\].Members, NOT IsEmpty(\[Unit Sales\]) ) )'
SELECT \{\[Unit Sales\]\} ON COLUMNS,
   TopCount(\[Customers\].\[Name\].Members, 10,( \[Prod Count\], \[Bachelors Degree\],
	    \[M\] ) + ( \[Prod Count\], \[Graduate Degree\], \[M\] )) ON ROWS
FROM \[Sales\]

On my 600MHz laptop, this query takes 9 minutes 20 seconds to execute. The problem with this query is that the Prod Count measure is very expensive to calculate: The query requires a full pass through the Product dimension for each different customer. To make this query execute much faster, you reduce the number of customers for which the query evaluates Prod Count. You can do this by having the query eliminate any customers who are not male college graduates before determining which customers purchased the largest number of different items (Prod Count). By rewriting the query as Listing A shows, I shortened the query's processing time to 32 seconds.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.