August 2002 MDX Puzzle Solution

Puzzle: A common type of retail sales analysis is consolidation analysis. One example of consolidation analysis is customer consolidation analysis: If fewer customers are buying more products, your customers are consolidating. If more customers are buying fewer products, your customers aren't consolidating. In the FoodMart 2000 Sales cube, use the Store Sales measure to determine the top 10 customers. Then, write an MDX query to determine whether the top 10 FoodMart customers are consolidating throughout the four quarters of 1997.

Solution: To solve this puzzle, you need to create an MDX query that includes the four quarters of 1997 on the columns of the query's result. Then, create two rows. The first row should display the total number of store sales that the top 10 customers purchased. The second row should display the percentage of total store sales that the top 10 customers purchased. Listing A shows the code that produces this result.

I made this query a little easier to read by first creating a set with the top 10 customers based on Store Sales, then using this set in the other two calculated measure definitions. The first calculated measure sums the store sales for the top 10 customers to determine the store sales that the top customers are responsible for. Next, the Top 10 Percent measure determines what percentage of the total store sales comes from the top 10 customers. The query then displays both the Top 10 Amount and the Top 10 Percent for each quarter of 1997.

The query's result shows that the top 10 customers are consolidating slightly. During first quarter 1997, the top 10 customers were responsible for 1.41 percent of all store sales; during fourth quarter 1997, that group accounted for 1.77 percent of store sales.