September 2001 MDX Puzzle Solution Revealed

22437.zip

Imagine that you're in a retail business that sells products with a variety of options. For example, you're in the golf-club business, and your customers can order clubs that have different shafts (e.g., steel, graphite, titanium) and different grips. Which options are the most popular? This question is tricky because the product options are independent, and you'd usually structure the options in an OLAP cube by using different dimensions (e.g., grip dimension, shaft dimension). How would you write an MDX query that returns an ordered list of the most popular options?

SQL Server doesn't have a golf-club cube, but you can use the FoodMart Sales cube. Just pretend the Education Level and Marital Status dimensions are the grip and shaft dimensions. Then, use Unit Sales to measure popularity.

Listing A shows the solution to this problem. This situation is one of the few in which you need to put multiple dimensions on a single axis (e.g., in rows) without cross-joining them. You don't cross-join the dimensions because you don't want all the combinations of the optionsâ€”you just want each option listed as an individual row in the result. Because the options span two dimensions, I matched the options from one dimension with the All member of the other dimension. Then, I could sort all the options together. Note that I used the break descending option (BDESC) in the Order() function because I wanted all the options to sort independently of their hierarchical grouping (e.g., you don't have to group the Marital Status values together). Figure A shows the result of this query.