June 2003 MDX Puzzle Solution

Using the FoodMart 2000 Sales cube, write an MDX query that displays only the product categories on rows that contain products that are in the top five based on store sales. On the columns, display only store sales.

You have to think upside down to solve this problem. First you have to identify the top five product names at the leaf level of the product dimension. Then, you can move up the hierarchy to find the product categories that contain the top five products. As Listing A shows, I solved this puzzle by creating a set that contains the top five products, then creating another set that uses the GENERATE() function to step through the top five products and determine the ancestor of each of those products at the product category level. The function then puts these categories on the rows and puts Store Sales on the columns.