January 2004 MDX Puzzle Solution

Problem: A simple and valuable way to measure a store's performance over a given year is to create a line chart containing the store's unit sales for that year. If the chart includes other lines representing the best- and worst-performing store for each quarter in that year, it can tell you whether the store in question was consistently near the bottom or top of the pack for each quarter in the year.

Use the FoodMart 2000 Sales cube to create an MDX query that compares the unit sales of the Beverly Hills store against the best- and worst-performing stores for each quarter in 1997. The worst store's performance must be greater than zero to filter out stores that had no sales in that quarter.

Solution: The first step in solving this puzzle is to create calculated measures for the best- and worst-performing stores. I call these calculated measures Best Store and Worst Store. As Listing A shows, the Best Store calculation is a TOPCOUNT that returns the top store in the Store City level for the current time member. This value can change every quarter, so the Time value is dynamic. The Worst Store calculation is similar but has the added twist that the value can't be zero. I first filtered from the Store City level all the members that had a Unit Sales value less than or equal to zero. I then sorted the list in ascending order and took the first member from that set, which gave me the lowest non-zero value for each time period.

After I defined the calculated measures, the rest of the query was fairly easy to construct. Best Store, Beverly Hills, and Worst Store are on the rows, and the four quarters of 1997 are on the columns of the MDX query. Unit Sales is the selected, although that selection is redundant because Unit Sales is the default measure for the FoodMart 2000 Sales cube.