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, or 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.