Skip navigation

March 2003 MDX Puzzle Solution


In any type of analysis, it's important to separate the relevant data from the irrelevant. Many approaches to this type of filtering are available, but a common approach is to find the top n items or the bottom n items based on some criteria. In a cube such as FoodMart Sales, filtering products isn't always as simple as finding the top n products across the whole product dimension—analysts typically need information about one department or category.

For this puzzle, use the FoodMart Sales cube and write an MDX query that finds the top five product names in each product department. Use Store Sales to rank the products, and include the name of the product department dimension member before each group of product names. The products should be on the rows, and the quarters of 1997 should be on the columns. All values displayed should be store sales.

The query that Listing A shows is similar to Listing 1's query in the main article. I used the GENERATE() function to step through the product departments. For each product department, I used the TOPCOUNT() function to find the top five product names based on store sales. I grouped the TOPCOUNT() function with \[Product\].CURRENTMEMBER in a set (denoted with \{\}) so that the query displays the product department before each group of top five product names.

TAGS: SQL Server
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.